Relative reference within INDIRECT function

  • Thread starter Thread starter paulkaye
  • Start date Start date
P

paulkaye

Hi,

Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
me to use the INDIRECT function in order to use the contents of a cell
as part of a reference to another cell:

=INDIRECT("'" & A2 & "'!C7")

where A2 contains the name of another worksheet and C7 is the cell on
that worksheet that I would like to reference. The problem is that the
main reason for needing this was to create a summary worksheet simply
by copying and pasting the formula. However, although the A2 will
change correctly to B2 when copied to the next column, C7 does not.
How can I make C7 a relative reference?

Many thanks,

Paul
 
Hi Paul,

=INDIRECT("'" & A2 & "'!C"&ROW(C7))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Hi,
|
| Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
| me to use the INDIRECT function in order to use the contents of a cell
| as part of a reference to another cell:
|
| =INDIRECT("'" & A2 & "'!C7")
|
| where A2 contains the name of another worksheet and C7 is the cell on
| that worksheet that I would like to reference. The problem is that the
| main reason for needing this was to create a summary worksheet simply
| by copying and pasting the formula. However, although the A2 will
| change correctly to B2 when copied to the next column, C7 does not.
| How can I make C7 a relative reference?
|
| Many thanks,
|
| Paul
 
Sorry, I messed up rows and column!

I'll try again

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Paul,
|
| =INDIRECT("'" & A2 & "'!C"&ROW(C7))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
|| Hi,
||
|| Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
|| me to use the INDIRECT function in order to use the contents of a cell
|| as part of a reference to another cell:
||
|| =INDIRECT("'" & A2 & "'!C7")
||
|| where A2 contains the name of another worksheet and C7 is the cell on
|| that worksheet that I would like to reference. The problem is that the
|| main reason for needing this was to create a summary worksheet simply
|| by copying and pasting the formula. However, although the A2 will
|| change correctly to B2 when copied to the next column, C7 does not.
|| How can I make C7 a relative reference?
||
|| Many thanks,
||
|| Paul
|
|
 
Hi Paul,

=INDIRECT("'" & A2 & "'!"ADDRESS(ROW(C7),COLUMN(C7),4))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Sorry, I messed up rows and column!
|
| I'll try again
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| Hi Paul,
||
|| =INDIRECT("'" & A2 & "'!C"&ROW(C7))
||
|| --
|| Kind regards,
||
|| Niek Otten
|| Microsoft MVP - Excel
||
||
||
||| Hi,
|||
||| Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
||| me to use the INDIRECT function in order to use the contents of a cell
||| as part of a reference to another cell:
|||
||| =INDIRECT("'" & A2 & "'!C7")
|||
||| where A2 contains the name of another worksheet and C7 is the cell on
||| that worksheet that I would like to reference. The problem is that the
||| main reason for needing this was to create a summary worksheet simply
||| by copying and pasting the formula. However, although the A2 will
||| change correctly to B2 when copied to the next column, C7 does not.
||| How can I make C7 a relative reference?
|||
||| Many thanks,
|||
||| Paul
||
||
|
|
 
Hi Niek,

Thanks for replying so quickly! I'm getting the standard formula error
message. When I click OK, it's highlighting ADDRESS as the problem (I
think!).

Any ideas?

Paul
 
Sorry Paul,

Keep messing up things. Missed an &

=INDIRECT("'" & A2 & "'!"&ADDRESS(ROW(C7),COLUMN(C7),4))

Please let us know if it's OK now

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Niek,
|
| Thanks for replying so quickly! I'm getting the standard formula error
| message. When I click OK, it's highlighting ADDRESS as the problem (I
| think!).
|
| Any ideas?
|
| Paul
|
| > Hi Paul,
| >
| > =INDIRECT("'" & A2 & "'!"ADDRESS(ROW(C7),COLUMN(C7),4))
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Sorry, I messed up rows and column!
| > |
| > | I'll try again
| > |
| > | --
| > | Kind regards,
| > |
| > | Niek Otten
| > | Microsoft MVP - Excel
| > |
| > || Hi Paul,
| > ||
| > || =INDIRECT("'" & A2 & "'!C"&ROW(C7))
| > ||
| > || --
| > || Kind regards,
| > ||
| > || Niek Otten
| > || Microsoft MVP - Excel
| > ||
| > ||
message| >
| > ||| Hi,
| > |||
| > ||| Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
| > ||| me to use the INDIRECT function in order to use the contents of a cell
| > ||| as part of a reference to another cell:
| > |||
| > ||| =INDIRECT("'" & A2 & "'!C7")
| > |||
| > ||| where A2 contains the name of another worksheet and C7 is the cell on
| > ||| that worksheet that I would like to reference. The problem is that the
| > ||| main reason for needing this was to create a summary worksheet simply
| > ||| by copying and pasting the formula. However, although the A2 will
| > ||| change correctly to B2 when copied to the next column, C7 does not.
| > ||| How can I make C7 a relative reference?
| > |||
| > ||| Many thanks,
| > |||
| > ||| Paul
| > ||
| > ||
| > |
| > |
|
 
To get relative cell addresses, rather than absolute

Glad we made it, finally!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Perfect! Thank you! Just one question - what's the 4 at the end
| for?!?!?
|
| > Sorry Paul,
| >
| > Keep messing up things. Missed an &
| >
| > =INDIRECT("'" & A2 & "'!"&ADDRESS(ROW(C7),COLUMN(C7),4))
| >
| > Please let us know if it's OK now
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Hi Niek,
| > |
| > | Thanks for replying so quickly! I'm getting the standard formula error
| > | message. When I click OK, it's highlighting ADDRESS as the problem (I
| > | think!).
| > |
| > | Any ideas?
| > |
| > | Paul
| > |
| > | > Hi Paul,
| > | >
| > | > =INDIRECT("'" & A2 & "'!"ADDRESS(ROW(C7),COLUMN(C7),4))
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | >
| > | > | Sorry, I messed up rows and column!
| > | > |
| > | > | I'll try again
| > | > |
| > | > | --
| > | > | Kind regards,
| > | > |
| > | > | Niek Otten
| > | > | Microsoft MVP - Excel
| > | > |
| > | > || Hi Paul,
| > | > ||
| > | > || =INDIRECT("'" & A2 & "'!C"&ROW(C7))
| > | > ||
| > | > || --
| > | > || Kind regards,
| > | > ||
| > | > || Niek Otten
| > | > || Microsoft MVP - Excel
| > | > ||
| > | > ||
| > message| > | >
| > | > ||| Hi,
| > | > |||
| > | > ||| Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
| > | > ||| me to use the INDIRECT function in order to use the contents of a cell
| > | > ||| as part of a reference to another cell:
| > | > |||
| > | > ||| =INDIRECT("'" & A2 & "'!C7")
| > | > |||
| > | > ||| where A2 contains the name of another worksheet and C7 is the cell on
| > | > ||| that worksheet that I would like to reference. The problem is that the
| > | > ||| main reason for needing this was to create a summary worksheet simply
| > | > ||| by copying and pasting the formula. However, although the A2 will
| > | > ||| change correctly to B2 when copied to the next column, C7 does not.
| > | > ||| How can I make C7 a relative reference?
| > | > |||
| > | > ||| Many thanks,
| > | > |||
| > | > ||| Paul
| > | > ||
| > | > ||
| > | > |
| > | > |
| > |
|
 
<To get relative cell addresses, rather than absolute>

But in this case I think it could be left out, because the C7 will change already. I really was making things too complicated.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| To get relative cell addresses, rather than absolute
|
| Glad we made it, finally!
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| Perfect! Thank you! Just one question - what's the 4 at the end
|| for?!?!?
||
|| > Sorry Paul,
|| >
|| > Keep messing up things. Missed an &
|| >
|| > =INDIRECT("'" & A2 & "'!"&ADDRESS(ROW(C7),COLUMN(C7),4))
|| >
|| > Please let us know if it's OK now
|| >
|| > --
|| > Kind regards,
|| >
|| > Niek Otten
|| > Microsoft MVP - Excel
|| >
|| >
|| > | Hi Niek,
|| > |
|| > | Thanks for replying so quickly! I'm getting the standard formula error
|| > | message. When I click OK, it's highlighting ADDRESS as the problem (I
|| > | think!).
|| > |
|| > | Any ideas?
|| > |
|| > | Paul
|| > |
|| > | > Hi Paul,
|| > | >
|| > | > =INDIRECT("'" & A2 & "'!"ADDRESS(ROW(C7),COLUMN(C7),4))
|| > | >
|| > | > --
|| > | > Kind regards,
|| > | >
|| > | > Niek Otten
|| > | > Microsoft MVP - Excel
|| > | >
|| > | >
|| > | > | Sorry, I messed up rows and column!
|| > | > |
|| > | > | I'll try again
|| > | > |
|| > | > | --
|| > | > | Kind regards,
|| > | > |
|| > | > | Niek Otten
|| > | > | Microsoft MVP - Excel
|| > | > |
|| > | > || Hi Paul,
|| > | > ||
|| > | > || =INDIRECT("'" & A2 & "'!C"&ROW(C7))
|| > | > ||
|| > | > || --
|| > | > || Kind regards,
|| > | > ||
|| > | > || Niek Otten
|| > | > || Microsoft MVP - Excel
|| > | > ||
|| > | > ||
|| > message|| > | >
|| > | > ||| Hi,
|| > | > |||
|| > | > ||| Gaurav Raveshia, Jim Thomlinson and Dave Peterson (Thank you!) helped
|| > | > ||| me to use the INDIRECT function in order to use the contents of a cell
|| > | > ||| as part of a reference to another cell:
|| > | > |||
|| > | > ||| =INDIRECT("'" & A2 & "'!C7")
|| > | > |||
|| > | > ||| where A2 contains the name of another worksheet and C7 is the cell on
|| > | > ||| that worksheet that I would like to reference. The problem is that the
|| > | > ||| main reason for needing this was to create a summary worksheet simply
|| > | > ||| by copying and pasting the formula. However, although the A2 will
|| > | > ||| change correctly to B2 when copied to the next column, C7 does not.
|| > | > ||| How can I make C7 a relative reference?
|| > | > |||
|| > | > ||| Many thanks,
|| > | > |||
|| > | > ||| Paul
|| > | > ||
|| > | > ||
|| > | > |
|| > | > |
|| > |
||
|
|
 
Back
Top