Formula to change row number to value of different cell.

[EJ42955]

If that isn't clear.

I want this formula to change the Row reference $G$533, to the value in cell AK1.
When I change the value in AK1, to 534, I want the formula to change the row 533 to 534. Automatically, on change, a macro button, whatever.

=COUNTIF(DATA!$C$524:$G$533,AE2)

Ideas? Thanks.

 

[jorismoerings]

Hi,

Try this: =COUNTIF(INDIRECT("data!$C$524:$G$"&$AK$1),AE2)

 

[EJ42955]

Perfect, jorismoerings.

I gotta remember that.

Thanks, much.

 

[EJ42955]

Hey,jorismoerings, As long as I have your ear.
How can I get cell $AK$1, from above, to return the ROW number of the first blank cell in DATA!C:C? So, I don't have to make the change manually.

Thanks.

 

[jorismoerings]

Hi,

Try this =MATCH(TRUE,INDEX(ISBLANK(DATA!C:C);0);0). ISBLANK test the cell for a true blank cell. If you cleared the cell using "" in some kind of formula the better option is to try this: =MATCH(TRUE,INDEX(DATA!C:C="",0),0).

 

[EJ42955]

Thanks, again, ,jorismoerings. Second formula worked best.
Appreciate, all your help.

EJ