worksheet cell references

[Guest]

The below is an example of a formula that I was shown to get values from
other worksheets into my current worksheet, using a cell to reference the
appropriate worksheet needed. Is there a way to write this so that the
additional string cell reference is not absolute.

I would like to copy and paste, but the only reference that changes is the
A1. I know that if I put a $ in front of the 1, the A$1 will always
reference the same cell, when copied and pasted, for the worksheet name I
need to use, but I need to get different information from sequential cells in
the worksheets.
=INDIRECT(A1&"!B2")
=INDIRECT(A1&"!B3")
=INDIRECT(A1&"!B4")
=INDIRECT(A1&"!B5")
=INDIRECT(A1&"!B6")

Thanks for yur help.

 

[WayneF]

Hi JT,

I was sent this bit of advice ...

Enter this into row 2 to get B2, then drag down for 1000 rows.

=INDIRECT($A$1&"!" & ADDRESS(ROW(),2))

HTH,
Bernie
MS Excel MVP

Indeed this worked for me. Another TIP is that the 2 in this formula
represents the column B
So if you wanted to get data from Column A, this formula would have to
read ...

=INDIRECT($A$1&"!" & ADDRESS(ROW(),1))

Maybe striaght forward for you MVP's but us newbies like to know

Cheers,

Wayne

 

[Guest]

If you want B to increase when copied down you can use

=INDIRECT(A2&"!"&CELL("address",B3))

or

=INDIRECT(A1&"!B"&ROWS($B$1:B2))


or

=INDIRECT(A1&"!B"&ROW(2:2))


Regards,

Peo Sjoblom

 

[Guest]

I am a little confused by the references in your response. The first one you
listed is perfectly understandable and, in fact, worked beautifully.

The second one seems to be indicating more than one cell limited to the same
column. Can this one be used, for instance, in addition formulas?

The third one seems to be limiting to one column, one cell.

In any case, your response helped solve my problem. Thank you for your help.

Take care

JT

 

[Guest]

The first one will change copied down/across, the latter 2 are limited to
column B
Thanks for the feedback


Regards,

Peo Sjoblom

Re