Parsing variable names from cell content

Question

I learned from this question that I can declare variables in Excel by naming cells. Now I'm wondering whether it'd be possible to parse text in a cell to call this variable. Say I name cell A1 "var1", and assign it the value 0.5, and cell A2 "var2" with the value 0.75. Then I want to do the following:

     B    C    D
1    var1 10   =B1*C1
2    var2 10   =B2*C2
3    var2 10   =B3*C3
4    var2 10   =parse(B4)*C4
5    var1 10   =CELL(B5)*C5
6    var1 10   =VALUE(B6)*C6

that is, multiply the values in column D by either var1 or var2, which is read from the same row in column C. Rows 4, 5, and 6 show some unsuccessful attempts of what I have in mind.

score 2 · Accepted Answer · answered Oct 22 '15 at 15:32

2

Use the INDIRECT function. In cell D6 the formula you want is =INDIRECT(B6)*C6.

Note that in Excel 2010 or later, you can't use var1 as a cell name, because it looks like a cell reference. You would have to use something like _var1 or variable1 instead.

answered Oct 22 '15 at 15:32

Simon White

136
7

Great, thank you! And thanks for the hint about `var1` as well, while I was only using this as an example here and not in my actual application, it's still good to know. – Nils Gudat Oct 22 '15 at 15:36

score 1 · Answer 2 · answered Oct 22 '15 at 15:30

1

You can use the INDIRECT() command to query both a range and a named range. Try =INDIRECT("myVar1")*C6

answered Oct 22 '15 at 15:30

Robin Gertenbach

10,316
3
25
37

Parsing variable names from cell content

2 Answers2