indirect formula help
 

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?

indirect formula help
 

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

Biff

"ivory_kitten" wrote in message
...
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?

indirect formula help
 

both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in message
...
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?

indirect formula help
 

Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in message
...
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?

indirect formula help
 

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" wrote:

Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in message
...
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?

indirect formula help
 

You're welcome. Thanks for the feedback!

Biff

"ivory_kitten" wrote in message
...
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" wrote:

Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in
message
...
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?

indirect formula help
 

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)

--
Regards

Roger Govier


"Biff" wrote in message
...
Try this:

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

Biff

"ivory_kitten" wrote in
message ...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in
message
...
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?

indirect formula help
 

the formula will fail once you go past column Z.

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

Biff

"Roger Govier" wrote in message
...
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)

--
Regards

Roger Govier


"Biff" wrote in message
...
Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in message
...
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?

indirect formula help
 

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" wrote:

Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in message
...
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?

indirect formula help
 

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

"ivory_kitten" wrote in message
...
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" wrote:

Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in
message
...
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?

indirect formula help
 

Hi Biff,

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

"Biff" wrote:

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

"ivory_kitten" wrote in message
...
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" wrote:

Try this:

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

Biff

"ivory_kitten" wrote in message
...
both please!

"Biff" wrote:

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

Biff

"ivory_kitten" wrote in
message
...
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?