Excel & INDIRECT 1

[suzreid]

I can't seem to find the answer to this anywhere but I'm sure it must be possible.

I have an INDIRECT lookup function that concatenates various cells together to give a 'real' cell reference on another sheet.

The formula is =INDIRECT( $N$9&$N$12&$J7) where $N$9 holds the sheet name, $N$12 holds the column reference and $J7 the row reference. In some cases I get either a #ref or #n/a error. I suspect there are either extra or not enough quotations marks to format the full reference correctly.

Is there any way of showing the value of the 'translated' cell reference before it shows the error? Can't figure out where the error is and it can be a long case of trial and error.

Thanks in advance and i'm sorry if it has been asked before.

Susan

 

[xlbo]

=$N$9&$N$12&$J7

will give you the textual representation of the formula.

Pretty sure that being as the error is sporadic, it is to do with ' ' and those sheet names that have spaces in them.

A sheet name, referenced in a formula, can be entered as

=Sheet1!A1

HOWEVER, if the sheet name has spaces in it, you must wrap it in ' ' e.g.

='This is a test'!A1

The ' ' will not cause an error even if they are not necessary so you may as well include them in the formula:

=INDIRECT("'" & $N$9 & "'" & $N$12 & $J7)

should do the trick

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Is there any way of showing the value of the 'translated' cell reference before it shows the error?

Yes - Edit the cell and highlight the string inside the parentheses ..

[tt] =INDIRECT([/tt][highlight black][yellow][tt]$N$9[/tt][/yellow][white][tt]&[/tt][/white][tt]$N$12[/tt][white][tt]&[/tt][/white][tt]$J7[/tt][/highlight][tt])[/tt]

.. and press F9

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[KenWright]

Tools / Formula Auditing / Evaluate Formula will also show you what is in each step of your formula.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------