=INDIRECT, Sheet reference with Incremental cell reference

1. =INDIRECT, Sheet reference with Incremental cell reference

Hi Everyone

I've used the INDIRECT function several times, but never fully understood it.

What I'm trying to do now is create 1 sheet with all the values from A2:A499 on 29 different sheets, Named Sheet1 - Sheet29.

On my new sheet i'm using to compile the date, the headings in Row 1 are Sheet1 - Sheet29.

Then in A2 I have =INDIRECT("'"&A\$1&"'!\$A2").

This means, as I drag that equation around. the indirect reference will automatically adjust it's column reference but will always look at the value in row 1. and no matter what sheet is is looking at, it will always pull the value from column A, but adjust the rows accordingly

The problem I have is that the cell reference value "\$A2" is not changing. as I copy the equation to different cells. It always stays as "\$A2". Even if I manially adjust to "\$A3" & "\$A4", Then select those three cells and drag the selection down to copy, all this does is repeat that same sequence of 3, it doesn't continue the count.

Any ideas what I can do?

2. Re: =INDIRECT, Sheet reference with Incremental cell reference

Oops, my mistake, ignore this.

3. Re: =INDIRECT, Sheet reference with Incremental cell reference

Hi there,
the cell reference in You example is static, since You are declaring it in between " sign.

If You are dragging down and want to receive values from changing rows, You have to create dynamic row number.
For example see attachement, with this formula:
Formula:
`Please Login or Register  to view this content.`

where in A1 is sheet name '1', so I get value from sheet '1' cell A1 (row A2=2 minus 1).
Book1.xlsx
If You want also dynamic columns, You have to combine it with another INDIRECT or OFFSET.

Hope it helps

4. Re: =INDIRECT, Sheet reference with Incremental cell reference

Thanks Miroslav, that works perfectly (with a slight adjustment adding another \$ to affix the cell ref column)

=INDIRECT("'"&A\$1&"'!A"&ROW(\$A3)-1)

I'll be honest though, I don't understand "why" this works, but a big part of that is me not understanding Indirect fully, or Offset at all is offset the bit,

Why did you need the "(A2)-1", Whay couldnt you just put "(A1)"? As I've tested and that works too.

5. Re: =INDIRECT, Sheet reference with Incremental cell reference

Hi Deap,
of course You can use A1 instear of A2-1. The result is the same...
It is just because when I create formulas with ROW or COLUMN functions, I like to be fixed to the cell I enter the formula in. So it is only 'my way' of doing things.

Happy that it hepls You!

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1