What cell does that cell refer to?

[yky]

Hello! I have the following formula in a cell:

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()+5))<>"", INDIRECT(ADDRESS(ROW(),COLUMN()+5)), IF(INDIRECT(ADDRESS(ROW(),COLUMN()+9))<>"", INDIRECT(ADDRESS(ROW(),COLUMN()+9))......(very long)

The formula looks for a non-empty cell down the row. Now, how do I tell the address of the non-empty cell this formula found?

 

[AlphaFrog]

Remove the INDIRECT function to return just the address...

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()+5))<>"", ADDRESS(ROW(),COLUMN()+5), IF(INDIRECT(ADDRESS(ROW(),COLUMN()+9))<>"", ADDRESS(ROW(),COLUMN()+9)......(very long)

There may be better ways to do this if you explain what you untimely want to do.

 

[yky]

Remove the INDIRECT function to return just the address...

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()+5))<>"", ADDRESS(ROW(),COLUMN()+5), IF(INDIRECT(ADDRESS(ROW(),COLUMN()+9))<>"", ADDRESS(ROW(),COLUMN()+9)......(very long)

There may be better ways to do this if you explain what you untimely want to do.

Thanks for the reply.

I have a purchasing list with many items on it. The list is arranged in a reverse chronological way. Not every item is purchased on every trip. Thus, some cells are blank. The formula is to find the latest purchasing price. Next to the price are purchasing amount and purchasing unit. I can modify the above formula to find the other two pieces of info. But I think if I can find out the cell that the formula finds, I can refer directly to the two cells down the row for the info I want. The formula might be much shorter.

 

[AlphaFrog]

This will find the latest date In cells C2:Z2

=MAX(C2:Z2)

This will return the value from row 4 that has the latest date in C2:Z2

=INDEX(C4:Z4,1,MATCH(MAX(C2:Z2), C2:Z2, 0))

INDEX - MATCH

 

[yky]

This will find the latest date In cells C2:Z2

=MAX(C2:Z2)

This will return the value from row 4 that has the latest date in C2:Z2

=INDEX(C4:Z4,1,MATCH(MAX(C2:Z2), C2:Z2, 0))

INDEX - MATCH

Thank you. But I cannot look for date because many items could be purchased on the same day and it would be a waste of memory to store the purchasing date along with the purchasing amount, unit, and price for every item purchased on the same day. With the way data is stored, I can only go down the row and find the nonblank cell as the latest purchase.

 

[AlphaFrog]

This will find the first non-blank cell in C2:Z2 and return the value from row 4 of the same column...

=INDEX(C4:Z4,1,MATCH(TRUE,LEN(C2:Z2)<>0,0))

This is an array formula. When you enter in this formula, use Ctrl+Shift+Enter. Excel will automatically surround the formula with {braces}.