Posts by alz1


Hi, hope someone can help.
This works:
=COUNTIFS(Table1[Role],3,Table1[Skill1],1)
Returning a count of instances of "1" where the Role column value in Table1 is 3
Can someone please help explain why this doesn't work if C4 contains Table1 and D4 contains Skill1
=COUNTIFS(Table1[Role],3,(INDIRECT(C4&"["&D4&"]",1)
My aim is to inject the table and column references from cell values in C4 and D4.
The INDIRECT syntax works with count but I cant get it to work with the condition of the other column (role) value.
Thanks.

Re: display latest date for specific value
Brilliant. Thanks.

List of docId in sheet 1, unique
List of reviews of docs corresponding to docid in sheet 2, can be multiple
Want to display latest review date agianst docID in sheet 1
Example attached
Appreciate any help 
Re: combine lookup and count no of occurences of a string that relate to the lookup v
Perfect! Thanks.

[ATTACH=CONFIG]64531[/ATTACH]
This is cut down example of what i'm trying to achieve.
table 1 has a value that i lookup in table 2 in a separate sheet. for that value found in table 2 there can be multiple rows with the same original value, i want to display a count of the ocurrences of a text string in an adjacent column in table 2  but display the count in table 1. Example attached.
I might be asking too much for one formula, any help appreciated.

Re: Using nested IF statements for date calculations
Quote from NBVC;725757Give this a try.
=IF([@[Due Date]]TODAY()>0, IF([@[Due Date]]TODAY()<=3,"Amber","Green"),"Red")
Worked a treat! Thanks NBVC and others who posted a response. Example using this formula attached for info.

I have a formatted table with a "Due Date" column.
I've currently got 3 nested IF statements to calculate if "Red", "Green" or "Amber" is displayed as result of the formula. I then use this text value to apply conditional formatting.
However, I'm having difficulty because I want one IF statement to override another.
There are the requirements for what I would like to achieve  any help appreciated.
Rule 1: IF [@[Due Date]] is today or less display text: "Red"
Rule 2: IF [@[Due Date]] is greater than or equal to today display text: "Green"
However...
Rule 3: IF [@[Due Date]] is greater than today but within 3 days, override rule 2 and display "Amber"
This is the working formula I have:
=IF([@[Due Date]]TODAY()<=0,"Red", IF([@[Due Date]]TODAY()>=0,"Green", IF([@[Due Date]]?TODAY()??,"Amber")))

Re: list common values across columns
Thanks Luke  great solution.

I have categories across the columns and codes that apply to the categories in rows.
Category1,Category2,Category3
Code1, Code2, Code3
Code2, Code9,Code5In this example code2 appears in Category1 and Categroy2
I'm looking for help with an easy way to report on common codes across categories. There are over 100 categories in my real data.
Ideally in a matrix with all codes along the top row and categories down the first column.

Re: Apply a formula to a cell based on a lookup value
Thanks Luke. Your second option worked best.

I've looked at some examples two formulas in one cell but not found what I need.
I have a list of identifiers in a formatted table in one sheet with a column that signifies if they have a two or three character prefix:
(pipe delimited here for illustration only)
AB001  Two
AB002  Two
CD001  Two
CD002  Two
EFG001  Three
GHI002  ThreeIn a separate sheet in the same workbook I want to show the character prefix only in its own column.
The user types "AB001" into column A, Excel puts prefix only in column B.
AB001  AB
AB002  AB
CD001  CD
CD002  CD
EFG001  EFG
GHI002  GHIThis can be done "manually" with these in column B:
=LEFT($A2,2)
=LEFT($A3,3)I want Excel to decide which formula to use ("=LEFT($A2,2)" or "=LEFT($A2,3)" by looking up the value "Two" or "Three" in the table in the other sheet.
I'm using Excel 2010.
Any help appreciated.

Re: apply different conditional formating to border based on value in cell above/belo
Example template.

Re: apply different conditional formating to border based on value in cell above/belo
Thanks NBVC and Soulfire21. Achieved exactly what I wanted! My example template attached based on your suggestions. Works well combined with row shading too.

Re: apply different conditional formating to border based on value in cell above/belo
Soulfire, really helpful  thanks! A step in the right direction. I've manually mocked up what I want to achieve in the attacted file.
Although I don't know how to write such a formula, could OFFSET be used pass the value of the cell above/below in to the formula (if cell above/below is odd/even  or if different from current cell) to then apply the formatting.
What I want to achieve is possible with shading but for this activity I need to avoid using shading and use border formatting, if possible.

I want to use conditional formatting to format rows if the value in the cell above is odd and use different formatting if the value in the cell below is even.
I can shade rows using" etc based on odd even values. but i want to apply a different border formatting depending on the odd or even value in the cell above/below.
To explain it as an example: I have 9 rows. Rows 13 have "1" in A1, A2, A3; 46 have "2" in A4, A5, A6; 79 have "3" in A7, A8, A9; I want to use conditional formatting to have an outline border round rows 13, 46, 79  based on the odd or even value. (instead of shading the rows using isodd/iseven).
Might not be possble but thought I'd ask if it was perhaps obvious to an expert at formulas  which I'm not! Any help appreciated.

Re: Inject formula into cell based on lookup value
Thanks NBVC! Very useful. I've used your example and a modified version of it:
=IF(B2="1",LEFT(A2,5)&" "&MID(A2,6,3)&" "&RIGHT(A2,3),IF(B2="2",LEFT(A2,4)&" "&MID(A2,5,3)&" "&RIGHT(A2,4),"Not Specified"))
The result is a much neater spreadsheet where I can copy down the formula easily. The help from this forum is always appreciated.

Re: Inject formula into cell based on lookup value
Attached example with notes. hope this makes sense.

I have a formula that puts spaces in a string:
"=LEFT(A5,H5) &" " &MID(A5,I5,J5) &" " &RIGHT(A5,K5)"
Value "00000000000" (in A5) becomes "00000 000 000" in another cell. You can see I'm using values in other cells in the row to populate the formula.
I've adjusted the formula values to also display the string as "0000 000 0000"
I want to determing the formula to use based on lookup  instead of copying different version of the formula down the column:
=LEFT(A4,4) &" " &MID(A4,5,3) &" " &RIGHT(A4,4) equates to Style 1
=LEFT(A5,5) &" " &MID(A5,6,6) &" " &RIGHT(A5,3) equates to Style 2Is it possibler to "inject" a formula into a cell based on a lookup value?
Any help appreciated.

Re: Use one conditional formatting rule to check for multiple values
Brilliant! Works perfectly  thanks cytop