How to reference a cell using a number in another cell

[Pumperkin]

Hi,

I have some formula which will render the data from blank cells (concatenated).

I want to tidy up my worksheet so that only the data that I want to show (the ones next to blank cells) is shown. I am currently on line 469 and don't want to have to scroll down all the time.

I have managed to get the row number of first blank showing in cell G1 on Sheet 2.

I want to reference cell Sheet1!L(Sheet2!G1), where G1 is currently 469 but will forever go up.

Is there a way of inputting this?

 

[steve the fish]

You could get the address like such:

=ADDRESS(Sheet2!$G$1,12,1,1,"Sheet1")

Is that what you want?

 

[Pumperkin]

Hi, Thanks for the response.

Not sure how I'd input that :/

My formula is:

=IF(Shipped!L469="", Shipped!A469&" "&Shipped!B469&" "&Shipped!C469&" "&Shipped!J469&""&Shipped!D469, "")

Basically every time it says 469, I want that to instead be the figure in G1, which is dynamic depending on where the first blank falls.

So something like Shipped!L(G1) but probably written more elegantly...

 

[steve the fish]

So you want the value in the cell not its reference? Then try indirect:

INDIRECT("Shipped!L"&$G$1)

as an example.

 

[Pumperkin]

Legend

'Tis a thing of beauty when a formula works as intended

=IF(INDIRECT("Shipped!L"&$G1)="", (INDIRECT("Shipped!A"&$G1))&" "&(INDIRECT("Shipped!B"&$G1))&" "&(INDIRECT("Shipped!C"&G1))&" "&(INDIRECT("Shipped!J"&G1))&""&(INDIRECT("Shipped!D"&G1)), "")