Posts by alz1

Rory, much appreciated - couldn't see the obvious error! Thanks.

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;725757

Give 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,Code5

In 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 | Three

In 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 | GHI

This 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

"=ISEVEN($A1)

" 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 1-3 have "1" in A1, A2, A3; 4-6 have "2" in A4, A5, A6; 7-9 have "3" in A7, A8, A9; I want to use conditional formatting to have an outline border round rows 1-3, 4-6, 7-9 - 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 2

Is 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