Indirect - Multiple Sheets/Cells

I have several sheets that I need to pull the information from cells K20,
K65, and K79 for each sheet.
On a "Summary" sheet, I have listed the sheet names in Column A.

So far I have tried using versions of an INDIRECT formula, but it does not
consistently work (in fact, the few times it has worked, I can't figure out
why)!

Here's the formula I was using:
=INDIRECT($A$2&"!"&"K20") Usually this formula comes back as #REF... What
am I doing wrong????

There is nothing wrong with your formula. Make sure that the tab name in
column A is EXACTLY the same as the tab name itself. No leading or trailing
blanks, etc.
--
Gary''s Student - gsnu200754


"Keep It Simple Stupid" wrote:

I have several sheets that I need to pull the information from cells K20,
K65, and K79 for each sheet.
On a "Summary" sheet, I have listed the sheet names in Column A.

So far I have tried using versions of an INDIRECT formula, but it does not
consistently work (in fact, the few times it has worked, I can't figure out
why)!

Here's the formula I was using:
=INDIRECT($A$2&"!"&"K20") Usually this formula comes back as #REF... What
am I doing wrong????

K.I.S.S.,

You may have spaces in the sheet tab name:

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

HTH,
Bernie
MS Excel MVP


"Keep It Simple Stupid" wrote in message
...
I have several sheets that I need to pull the information from cells K20,
K65, and K79 for each sheet.
On a "Summary" sheet, I have listed the sheet names in Column A.

So far I have tried using versions of an INDIRECT formula, but it does not
consistently work (in fact, the few times it has worked, I can't figure out
why)!

Here's the formula I was using:
=INDIRECT($A$2&"!"&"K20") Usually this formula comes back as #REF... What
am I doing wrong????

Try

=INDIRECT("'"&$A$2&"'!K20")



--


Regards,


Peo Sjoblom


"Keep It Simple Stupid" wrote
in message ...
I have several sheets that I need to pull the information from cells K20,
K65, and K79 for each sheet.
On a "Summary" sheet, I have listed the sheet names in Column A.

So far I have tried using versions of an INDIRECT formula, but it does not
consistently work (in fact, the few times it has worked, I can't figure
out
why)!

Here's the formula I was using:
=INDIRECT($A$2&"!"&"K20") Usually this formula comes back as #REF...
What
am I doing wrong????

UGGGH! Just realized there were SPACES in my sheet names! Now I feel silly.
Thanks for your help!

"Gary''s Student" wrote:

There is nothing wrong with your formula. Make sure that the tab name in
column A is EXACTLY the same as the tab name itself. No leading or trailing
blanks, etc.
--
Gary''s Student - gsnu200754


"Keep It Simple Stupid" wrote:

I have several sheets that I need to pull the information from cells K20,
K65, and K79 for each sheet.
On a "Summary" sheet, I have listed the sheet names in Column A.

So far I have tried using versions of an INDIRECT formula, but it does not
consistently work (in fact, the few times it has worked, I can't figure out
why)!

Here's the formula I was using:
=INDIRECT($A$2&"!"&"K20") Usually this formula comes back as #REF... What
am I doing wrong????

You may have spaces in the sheet names.

With list of sheet names in column A

In B1 enter =INDIRECT("'"&A2&"'!"&"K20")

Note that A2 is not absolute so can be copied down.


Gord Dibben MS Excel MVP

On Thu, 8 Nov 2007 07:04:04 -0800, Keep It Simple Stupid
wrote:

I have several sheets that I need to pull the information from cells K20,
K65, and K79 for each sheet.
On a "Summary" sheet, I have listed the sheet names in Column A.

So far I have tried using versions of an INDIRECT formula, but it does not
consistently work (in fact, the few times it has worked, I can't figure out
why)!

Here's the formula I was using:
=INDIRECT($A$2&"!"&"K20") Usually this formula comes back as #REF... What
am I doing wrong????