Answers: Excel Worksheet Function: How to refer to a dynamic column?

user232736

Note: Trying without VBA

Let's say I have in C1 the word "Hello"

From another cell, lets say A1, I want to return C:C, because in the first row 1:1, contains the word "Hello"

In A1, I could put:

=MATCH("Hello",1:1,0)

And this would return 3, as it is in the third column. However, I need it in the format C:C

ADDRESS function can return a specific cell using the column number 3, but not the entire column.

Ultimately, I want to use a COUNTIF(dynamicWholeColumn,criteriaCell)

Any ideas anyone? Cheers.

benshepherd

You could manipulate the output of ADDRESS using string functions, to turn $C$1 into $C:$C. But if you're putting it into COUNTIF, you'd be better using OFFSET and the output from MATCH.

=COUNTIF(OFFSET($A:$A,0,MATCH("Hello",1:1,0)-1),"Hello")

Excel INDIRECT function and conditional formatting - highlighting a row

microsoft-excel microsoft-excel-2007 microsoft-excel-2010 worksheet-function conditional-formatting

Ehryk

I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't working for me.

Basically, I want to highlight rows where B is not blank and F is blank. INDIRECT will work for ONE of the conditions, but

= AND(INDIRECT("B"&ROW()) > 0, INDIRECT("F"&ROW()) = "")

does not work at all.

The answer in the question points to replacing the references with relative ones, so I'm thinking this should work:

= AND ($B2 > 0, $F2 = "")

But it does not, nor does ISBLANK($F@) or ISEMPTY($F2) (the cell contains a formula that sometimes will return "", I want the row highlighted in these cases but only when something is in column B).

Am I missing something about relative references? Why doesn't INDIRECT work with AND/OR?

Home

Excel Worksheet Function: How to refer to a dynamic column?