Use cell contents to refer to a sheetname in a formula

C

cp

I have the following formula, which looks up the following:
The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the
month of February (Summary! E3):
=INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal!$A$1:$A$3)*(Summary!$F$2=Legal!$B$1:$B$3),0))

The formula works great (entered as an array). Now, on the Summary tab, I
would like to enter other sheet names for other departments (ie., HR,
Marketing), and have the formula read it from the cell. On the Summary tab,
Legal is listed in call A7. So instead of Legal!$D1:$D3, how do I make it
read A7!$D1:$D3, so I can copy it down and have the information pick up for
my other departments?

I apologize if this has been asked/answered before; I haven't been able to
find a spot-on matching problem, and nothing I've tried works.
 
P

Per Erik Midtrød

I have the following formula, which looks up the following:
The expense amount (Legal!D1:D3) in the year 2006 (Summary! E2) for the
month of February (Summary! E3):
=INDEX(Legal!$D$1:$D$3,MATCH(1,(Summary!$E$2=Legal!$A$1:$A$3)*(Summary!$F$2 =Legal!$B$1:$B$3),0))

The formula works great (entered as an array).  Now, on the Summary tab, I
would like to enter other sheet names for other departments (ie., HR,
Marketing), and have the formula read it from the cell.  On the Summarytab,
Legal is listed in call A7.  So instead of Legal!$D1:$D3, how do I makeit
read A7!$D1:$D3, so I can copy it down and have the information pick up for
my other departments?

I apologize if this has been asked/answered before; I haven't been able to
find a spot-on matching problem, and nothing I've tried works.

Your formula is a bit too complex for me, but you should have a look
at the indirect-function.

Per Erik
 
C

cp

The indirect function may indeed be the answer, but I have not been able to
get it to work for me.
 
P

Peo Sjoblom

This should work


=INDEX(INDIRECT("'"&A7&"'!$D$1:$D$3"),MATCH(1,(INDIRECT("'"&A7&"'!$A$1:$A$3")=Summary!$E$2)*(INDIRECT("'"&A7&"'!$B$1:$B$3")=Summary!$F$2),0))


also entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom
 
S

ShaneDevenshire

Hi,

Use a formula of the form:

=INDEX(INDIRECT(E4&"!$D$1:$D$3"),MATCH(1,(Summary!$E$2=INDIRECT(E4&"!$A$1:$A$3"))*(Summary!$F$2=INDIRECT(E4&"!$B$1:$B$3")),0))

Where E4 contains the sheet name.
 
C

cp

This works, since some of my sheet names DO have a space. I did try the
indirect function before, but obviously missed something (id addition to the
space). Thank you SO much for your time!

-- cp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top