use a cell/variable as a sheet reference?

I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.

For example... instead of LEMONS!a1 and LIMES!B2
I would like to use REF1!a1 and REF2!B2
where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'

How can I do this?

thanks

hi,

Welcome to the Forum

Read up on the "Indirect function" in the Excel Help files & let us know if you need help making it work.

hth
Rob

=indirect(z1&"!a1")
if sheet name has spaces say "lemon tree"
you need the extra '
='lemon tree'!A1
then yo have to use them in the indirect
=INDIRECT("'"&Z1&"'!a1") that's =INDIRECT("'"&Z1&"'!a1")

Hi Shytott,

Also, have a look at the ADDRESS function.

=INDIRECT(ADDRESS(,,,,))

Cheers,

hi,

Are you working in Excel 2003 (listed in your profile)?
Or in Excel 2007 which is when the "Countifs" function became available?

Can you please upload an example file?

Rob

hi,

Try this out:

Please Login or Register  to view this content.

This is very similar to Martin's earlier example where he has shown what is needed for sheets with spaces in the name & he has omitted the optional "A1" argument which defaults to "true" whereas my example states this.

Note, that Indirect is a volatile function & if used extensively it could slow your spreadsheet calculations down.
There's currently a discussion going on amongst some of the whizzes on the Forum discussing alternative approaches (eg using Index) but I'm taking the quicker (lazy!) approach for the moment because it looks like you will only be using the formula sparingly for flexible reporting.


hth
Rob

Thanks for the help! That worked. Another question about the original formula: =COUNTIFS(CHM!I3:I500,"=Bryan",CHM!M3:M500,"=M")

Instead of including "=Bryan" I'd like to use a cell reference that held "Bryan" or whatever text I needed. Is the Indirect function the best way to use a reference (instead of text) inside a formula? This seems like a somewhat generic issue re excel formula creation.

UPDATE: I got it: INDIRECT("='"&J8&"'") Guess I'll read up on Indirect. The punctuation gets a bit confusing.


thanks again.

Thanks for the feedback - I'm pleased we could help

I suggest reading through the examples in the 2007 Help files for the "Countifs" function as well. If I understand your last post correctly - no, indirect is not needed eg:

Please Login or Register  to view this content.

(or if cell K7 is changed from "Male" to "M")

Please Login or Register  to view this content.

Personally, I recommend avoiding Indirect where-ever possible, but it is (to me) the easiest way of making formulae flexible to refer to different sheets. An alternative setup of using a single "database style" layout on a single sheet (with the sheet name/grouping being added as a separate column) would allow you to use a pivot table for summarising your data.
(pivot tables can half multiple source ranges but are a bit more involved)

Anyway...
If you are happy with what you've got, can you please mark the post as solved?

hth
Rob