INDIRECT formula not working

[Yunus]

Hi,

What am I doing wrong here...

This formuals does not work:
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")
where A & B are cell references used in the naming of many worksheets

I am attempting to obtain the value in Cell Q1 of worksheets:

YUNUS-A3-B3 GD0
YUNUS-A4-B4 GD0
YUNUS-A5-B5 GD0
YUNUS-A6-B6 GD0
YUNUS-A7-B7 GD0
YUNUS-A8-B8 GD0

 

[Ron Rosenfeld]

Hi,

What am I doing wrong here...

This formuals does not work:
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")
where A & B are cell references used in the naming of many worksheets

I am attempting to obtain the value in Cell Q1 of worksheets:

YUNUS-A3-B3 GD0
YUNUS-A4-B4 GD0
YUNUS-A5-B5 GD0
YUNUS-A6-B6 GD0
YUNUS-A7-B7 GD0
YUNUS-A8-B8 GD0

"... does not work"

That is not much to go on. Probably there's something wrong with the contents of A3 and B3

 

[joeu2004]

What am I doing wrong here... This formuals does not work:
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")
where A & B are cell references used in the naming of many
worksheets
I am attempting to obtain the value in Cell Q1 of worksheets:
YUNUS-A3-B3 GD0
YUNUS-A4-B4 GD0
YUNUS-A5-B5 GD0
YUNUS-A6-B6 GD0
YUNUS-A7-B7 GD0
YUNUS-A8-B8 GD0

Depends on what is in column A and B.

But if the INDIRECT formulas are in consecutive rows, you might not need the
contents of column A and B after all.

The formula in the first row can be:

=INDIRECT("'YUNUS-A"&ROW(A3)&"-B"&ROW(A3)&" GD0'!Q1")

ROW(A3) returns 3. When you copy the formula down the column into
consecutive rows, it becomes ROW(A4), ROW(A5) etc, returns 4, 5 etc.

Note other simplifications that might apply to any solution.

 

[Yunus]

"... does not work"

That is not much to go on. Probably there's something wrong with the contents of A3 and B3

Sorry i may not have been clear...

A3, A4, A5, A6, A7,A6, B3, B4, B5, B6, B7,B8 ARE ALL CELL REFERNECES.
THEY HAVE VALUES IN THEM which are used in naming worksheets.

e,g Column A & B in sheet 1 are listed below
London 1
London 2
london 3
Lverpool 1
Liverpool 2
manchester 1
Manchester 2
birmingham 1
etc

The Worksheets are Named
yunus-london-1 GD0
yunus-london-2 GD0
etc

Now in Sheet 1 Column C using the indirect command I want to obtaing
the value in in cell Q1 in each of the worksheets.
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")

 

[Alex Plantema]

Yunus schreef in

A3, A4, A5, A6, A7,A6, B3, B4, B5, B6, B7,B8 ARE ALL CELL REFERNECES.
THEY HAVE VALUES IN THEM which are used in naming worksheets.

e,g Column A & B in sheet 1 are listed below
London 1
London 2
london 3
etc

The Worksheets are Named
yunus-london-1 GD0
yunus-london-2 GD0
etc

Now in Sheet 1 Column C using the indirect command I want to obtaing
the value in in cell Q1 in each of the worksheets.
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")

The formula works as expected, at least in OpenOffice.
Check text entries for unintended blanks.
What does ="'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1" give?

 

[Ron Rosenfeld]

Sorry i may not have been clear...

A3, A4, A5, A6, A7,A6, B3, B4, B5, B6, B7,B8 ARE ALL CELL REFERNECES.
THEY HAVE VALUES IN THEM which are used in naming worksheets.

e,g Column A & B in sheet 1 are listed below
London 1
London 2
london 3
Lverpool 1
Liverpool 2
manchester 1
Manchester 2
birmingham 1
etc

The Worksheets are Named
yunus-london-1 GD0
yunus-london-2 GD0
etc

Now in Sheet 1 Column C using the indirect command I want to obtaing
the value in in cell Q1 in each of the worksheets.
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")

"... does not work" ???

You still have not described what you mean by that statement.

There are many ways that something, in a computer, might "not work". Program crashes, BSOD, unexpected results, error messages, etc.

If you are getting an error message, it would be good to know the nature of the message, and might save everyone some time. For example, had you written that you get an #REF error, then I would tell you, as I did before, that there is a problem with the contents of your column A and column B references. The problem is that they do not match the name of your worksheet. But it would be a waste of time to go into the nitty gritty of trying to debug that sort of problem, when we don't even know if that is what you mean by "... does not work" !!!

 

[joeu2004]

e,g Column A & B in sheet 1 are listed below
London 1
London 2 [....]
The Worksheets are Named
yunus-london-1 GD0
yunus-london-2 GD0

Oh, that is completely different from your original (mis)representation of
the facts. GIGO!

Now in Sheet 1 Column C using the indirect command
I want to obtaing the value in in cell Q1 in each of
the worksheets.
=INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1")

There is nothing syntactically wrong with your formula, AFAIK. It can be
written more simply, and it behoove you to do so in order to minimize typing
mistakes. Namely:

=INDIRECT("'YUNUS-" & A3 & "-" & B3 & " GD0'!Q1")

You have not explained in what way the formula "does not work". And Excel
error like #REF or #VALUE? Or simply an unexpected result?

One possibility is that either A3 and/or B3 contains leading or trailing
spaces, or the worksheet names contain interstitial spaces that you have not
shown.

Another possibility: the formula is not exactly as you have written above.
(Or as I have rewritten it.) Be sure to copy the formula from the Formula
Bar and paste it into a follow-up posting, if you did not do so already.

And a third possibility: you have manual calculation mode set. Does the
formula work when you press ctrl+alt+F9?

If none of the suggestions here and by others solves your problem, I suggest
that you upload an example Excel file to a file-sharing website and post the
URL (http://...) in a follow-up posting. The following is a list of some
free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

 

[Yunus]

Sorry i may not have been clear...

A3, A4, A5, A6, A7,A6, B3, B4, B5, B6, B7,B8 ARE ALL CELL REFERNECES.
THEY HAVE VALUES IN THEM which are used in naming worksheets.

e,g Column A & B in sheet 1 are listed below
London 1
London 2
london 3
Lverpool 1
Liverpool 2
manchester 1
Manchester 2
birmingham 1
etc

The Worksheets are Named
yunus-london-1 GD0
yunus-london-2 GD0
etc

Hi all work it out. I typed '"" rather than "'". i could not see it. i
guess I need glasses.
Thanks for our help.

 

[joeu2004]

Hi all work it out. I typed '"" rather than "'". i could not see it.

And neither could we, of course. For future note, copy-and-paste formulas
from the Formula Bar.