Dynamically referencing Tab names?

[johanna0507]

Hi all! Say I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...

How would I go about dynamically picking up the sheet name. and referring to it?

For example if I have a column with all the different sheet names in it:

Col AA:
31-10-13
01-11-13
04-11-13

etc!

Hope the question makes sense and that somebody knows!

[Pete_UK]

In cell AB1, for example, you can have this function:

=INDIRECT("'"&AA1&"'!M6")

which will get the data from '31-10-13'!M6 (change the cell reference to suit).

When you copy this down you will retrieve data from the same cell in those other sheets.

Hope this helps.

Pete

[johanna0507]

Thanks for this response. It makes perfect sense, only I cannot get it to work.

Field AA6 contains the Date 27-10-13 (I've tried it as a Text field, Date field and originally it was populated by a formula - no joy either way).

In AB6 I am trying to do my formula.
I added:

Please Login or Register  to view this content.

...because the field I am trying to reference is in the tab called 27-10-13
...and the field reference is D15


Unfortunately I keep getting a #REF! error.

I want to apply the formula to multiple fields, by dragging vertically, but trying to get the formula working first...

No doubt I am doing something wrong, but I don't know what!

[Pete_UK]

Try it this way:

=INDIRECT("'"&TEXT(AA6,"dd-mm-yy")&"'!D15")

with AA6 containing a proper date. Ensure that you do not have any spaces within or at the ends of the name as it appears on the tab.

Hope this helps.

Pete

[newdoverman]

This works for my try. Enter the date in AA6 as text so that the format is exactly the same as the Tab Name. I tried just entering the AA6 as a date but the date entered replaced the - with / and of course that wouldn't work. Once entered as text this formula returned the value of D15 on sheet 27-10-13

Please Login or Register  to view this content.

This also works with a real date.

Please Login or Register  to view this content.

Pete's formula in Post # 4 also works.

[Pete_UK]

Johanna,

you have posted this formula:

=INDIRECT("'"&AA6&"'!'&D15")

you do not need the '& after the !, but see my revised formula in post #4 (I copied it directly from yours, and didn't spot the '&).

Pete

[johanna0507]

Thanks both! Still not working, with either suggestion. I am just getting #REF

The value in the field I I am trying to reference, is "6" , no formula, just the value.

Field AA6 is a Date field, with the style "dd-mm-yy". I have tried it as a text field too.

Edit
Not even changing the tab name to "A" fixes the problem (did that, just to avoid any issues with my tab name which was a date...

[johanna0507]

Johanna,

you have posted this formula:

=INDIRECT("'"&AA6&"'!'&D15")
you do not need the '& after the !, but see my revised formula in post #4 (I copied it directly from yours, and didn't spot the '&).

Pete

Thanks! But I am having no luck at all with this.
Could some bad settings somewhere be affecting it?

[Pete_UK]

Johanna,

I think we have some posts made at the same time. Please try this in AB6:

=INDIRECT("'"&TEXT(AA6,"dd-mm-yy")&"'!D15")

with AA6 containing a proper date. If that produces #REF then that date in AA6 does not have a matching sheet - you can avoid the error like this:

=IFERROR(INDIRECT("'"&TEXT(AA6,"dd-mm-yy")&"'!D15"),"")

then copy down.

Hope this helps.

Pete

EDIT: if you are still having difficulty, then attach the workbook.

[johanna0507]

Johanna,

I think we have some posts made at the same time. Please try this in AB6:

=INDIRECT("'"&TEXT(AA6,"dd-mm-yy")&"'!D15")

with AA6 containing a proper date. If that produces #REF then that date in AA6 does not have a matching sheet - you can avoid the error like this:

=IFERROR(INDIRECT("'"&TEXT(AA6,"dd-mm-yy")&"'!D15"),"")

then copy down.

Hope this helps.

Pete

EDIT: if you are still having difficulty, then attach the workbook.

Hurrah! Now it works, with your explanation!
Now I am just going to have to figure out how to adapt it to quite a few different fields, and how to drag the formula down, and keep getting the right dates.

[Pete_UK]

Well, the date in AA6 is converted into the same format as used by your sheet names by means of the TEXT function, and then that is joined onto !D15 (which will not change when the formula is dragged/copied down) to give a string-value reference to a cell, which the INDIRECT function converts into a form that Excel can interpret as a proper cell reference. When the formula is copied down, the AA6 tern will become AA7, and then AA8, then AA9 and so on in successive rows, so that you will get data from cell D15 on the tabs given by the date in column AA.

Hope this helps, and glad you got it working in the end.

Pete

[johanna0507]

Well this is an enormously helpful thing to know.

Perhaps I was adding unecessary complications by using a date. If I had just used a plain text based name it might have worked faster.

I'm marking it as solved in any case. THANKS AGAING