indirect formula help

[Guest]

i need to use a formula in which the variable part is the worksheet name, i'm
trying to use INDIRECT($A$1 & "!A2"), where the value of $A$1 is the
worksheet name, but when i copy my formula to the other cells, the A2 part
does not update

how do i make it automatically update to the current cell reference?

 

[Biff]

How do you want the A2 reference to update? By column or row or both?

Biff

 

[Biff]

How do you want the A2 reference to update? By column or row or both?

Biff

 

[Guest]

both please!

How do you want the A2 reference to update? By column or row or both?

Biff

 

[Guest]

both please!

How do you want the A2 reference to update? By column or row or both?

Biff

 

[Biff]

Try this:

=INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2))

Biff

 

[Biff]

Try this:

=INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROWS($1:2))

Biff

 

[Guest]

Thanks, I was stuck with how to get the column part to show up as a letter!

So I've used =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROW()

Thanks so much for your help!

 

[Guest]

Thanks, I was stuck with how to get the column part to show up as a letter!

So I've used =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROW()

Thanks so much for your help!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks, I was stuck with how to get the column part to show up as a
letter!

So I've used =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROW()

Thanks so much for your help!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks, I was stuck with how to get the column part to show up as a
letter!

So I've used =INDIRECT($A$1 & "!" & CHAR(COLUMN()+64) & ROW()

Thanks so much for your help!

 

[Roger Govier]

Hi Biff

Just a small point.
Whilst this may well work for the OP's desired range, the formula will
fail once you go past column Z.

An alternative might be
=OFFSET(INDIRECT($A$1 & "!$A$1"),ROW(1:1),COLUMN()-1)

 

[Roger Govier]

Hi Biff

Just a small point.
Whilst this may well work for the OP's desired range, the formula will
fail once you go past column Z.

An alternative might be
=OFFSET(INDIRECT($A$1 & "!$A$1"),ROW(1:1),COLUMN()-1)

 

[Biff]

the formula will fail once you go past column Z.

Yes, I know. I would have dealt with it if needed.

Biff

 

[Biff]

the formula will fail once you go past column Z.

Yes, I know. I would have dealt with it if needed.

Biff

 

[Guest]

Ok, so I have used this to import my data, however depending on the A$1 some
of my tables are smaller than others which is making my lookup functions not
return properly! How can I stop this?

 

[Guest]

Ok, so I have used this to import my data, however depending on the A$1 some
of my tables are smaller than others which is making my lookup functions not
return properly! How can I stop this?

 

[Biff]

How about providing an explanation of what you're trying to do. What lookup
functions? What lookup tables? Where are the tables? Where is the lookup
value?

Biff

 

[Biff]

How about providing an explanation of what you're trying to do. What lookup
functions? What lookup tables? Where are the tables? Where is the lookup
value?

Biff

 

[Guest]

Hi Biff,

Thanks for your help, I figured this one out already, i have to use offset
instead of index