Indirect Help

[theta]

Hi, using INDIRECT but cannot get it to work in this particular instance

I have a static named range (just contains a single number) called KBMB.

Cell G7 contains "KB" and cell H7 contains "MB".

INDIRECT(G7&H7) does not provide me with any result, just get a #REF error

Formula evaluates to INDIRECT("KBMB")

?

 

[GlennUK]

When you list names in the Name Manager, what attributes show for name KBMB, under Refers To, and Scope? I assume that this is all within the same workbook?

 

[theta]

Yes refers to workbook...

Thought it would interpret as a reference, not at "KBMB" text

 

[GlennUK]

Yes refers to workbook...

Thought it would interpret as a reference, not at "KBMB" text

... what do you mean? What does that name refer to?

 

[theta]

The name contains a value, 0.96456

When I type =KBMB ... the cell correctly displays 0.96456

When I type =INDIRECT(G7&H7) and analyse the formula, it interprets it as =INDIRECT("KBMB")

Thoughts?

 

[GlennUK]

So, it doesn't refer to a range at all, but to a value stored in a defined name. Have you thought of using VBA to fetch the name contents, in a function?

 

[theta]

No because I have alot of different rows. The workbook doesn't allow VBA (security)

My data looks like this :

FROM...TO....CONV
KB.....MB....0.0009765625
MB.....KB....1024
MIN....SEC...60
SEC....MIN...0.1666666667

The conversion factors are saved as named ranges e.g. KBMB, MINSEC, SECMIN

So need a dynamic formula to pull the correct factor from the named range

 

[GlennUK]

It's not a named range. BUT, if you put your values in cells in a sheet, and name them accordingly, then all will work.

 

[theta]

Hmmm it works. Why does it do that?

Have the value in a cell, it can be references ok. But stored in a defined name and dosn't work?

 

[GlennUK]

I guess it has to do with the way Excel resolves references. You were trying to do something that didn't involve references at all. Then Excel hit a brick wall.