Using sheet name as part of a formula

[BrianL]

Hi, I would like to create a formula (in a sheet called main) that gets data from another worksheet e.g.
There is a sheet named 25 Sep
Cell A5 in sheet called main is "25 Sep"
I would like B5 formula (in main) to = G21 in sheet 25 Sept

similarly
Cell A6 (in main) is 26 Sep
B6 formula (in main) to = G21 in sheet 26 Sep

can anyone help please?

 

[Ramesh]

you can use the following formula in cell B5

=INDIRECT(A5&"!G21")

The same is applicable for the next one too

 

[BrianL]

you can use the following formula in cell B5

=INDIRECT(A5&"!G21")

The same is applicable for the next one too

you can use the following formula in cell B5

=INDIRECT(A5&"!G21")

The same is applicable for the next one too

Thanks Ramesh - awesome
Though, when I fill the formula right the column G remains constant rather changing.

 

[Ramesh]

True Brian..as the cell address has been placed in the double quotes, it would not follow the automatic cell reference update practices (absolute / relative / mixed) when you copy/paste the formula..tht's the only trouble you have to experience

 

[NARAYANK991]

Hi ,

I learnt this recently !

The solution is :

=INDIRECT(A5 & "!" & CELL("address" ,G21))

Now when you copy to the right , the reference G21 will change.

Narayan

 

[BrianL]

Hi ,

I learnt this recently !

The solution is :

=INDIRECT(A5 & "!" & CELL("address" ,G21))

Now when you copy to the right , the reference G21 will change.

Narayan

Thanks Narayan, works like a dream!