3

I have set the name P1A_FO_DESIGN on a cell A1 that countains the value 5.12 I have set another cell C10 with this value: =("="&A10&"_"&B10&"_"&C8)

A10 contains P1A

B10 contains FO

C8 contains DESIGN

the value of C10 is =P1A_FO_DESIGN but I would like it to display 5.12 How do I acheive it please? Regards

JNevill
  • 46,980
  • 4
  • 38
  • 63
yakitori
  • 37
  • 4
  • 2
    Instead of doing named ranges which require an `INDRECT` formula (which is volatile) to look up their values, I would highly recommend creating a table that has the built variables names and their associated values so can you just do a `VLOOKUP` instead. It will also be easier to maintain and update the table rather than continuing to make adjustments to named ranges. – tigeravatar Apr 14 '16 at 15:16
  • thank you for this tip but I'm not sure I understand what you are proposing – yakitori Apr 14 '16 at 16:18
  • ok I did the vlookup thing and it is easier to build but I found some constraints on this functions (data need to be ordered) – yakitori Apr 14 '16 at 22:39

1 Answers1

4

Use =INDIRECT() formula:

=INDIRECT(A10&"_"&B10&"_"&C8)

This will evaluate to =INDIRECT("P1A_FO_DESIGN") Which will further evaluate to point to the cell with that Name.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Not sure that you need the additional quotation marks - it seems `=INDIRECT(A10&"_"&B10&"_"&C8)` would suffice from a quick test. – asongtoruin Apr 14 '16 at 15:36
  • Right on. I tested and thought I needed them, but yea... because it's coming in from other cell values it will already be interpreted as a string. I will update the answer. Thanks! – JNevill Apr 14 '16 at 15:51