IF statement that returns a formula

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
What cell is your formula in? It should be in B1 (Or anything from E1 onwards)

Also, what are the values in A1, C1 and D1?
 

gnmthead

New Member
Joined
Sep 9, 2008
Messages
25
I just figured out what the problem was but this relates to a bigger question in general. Here is my example. I am working on a VLOOKUP formula and am wondering how I can have the return value of the VLOOKUP formula to be a formula. Is that possible? So instead of returning a value, I want the VLOOKUP to return "C$4 + D$4". I want to do this so I can copy this VLOOKUP formula throughout my spreadsheet. (I would use an if statement but there are 23 conditions)
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
There is probably a better way to do it but the way I would do it would be with a helper column, I would make E$4 = C$4 + D$4 and make your VLookup return E$4
 

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767
I just figured out what the problem was but this relates to a bigger question in general. Here is my example. I am working on a VLOOKUP formula and am wondering how I can have the return value of the VLOOKUP formula to be a formula. Is that possible? So instead of returning a value, I want the VLOOKUP to return "C$4 + D$4". I want to do this so I can copy this VLOOKUP formula throughout my spreadsheet. (I would use an if statement but there are 23 conditions)
You can have cell references as entries in cells and refer to them indirectly, if that is what you're after. But you can't refer to formulae indirectly as far as I know.

So if C$4 was written in A1, INDIRECT(A1) would return the value in C$4.

But if C$4+D$4 was written in A1, INDIRECT(A1) would give an error.
 

gnmthead

New Member
Joined
Sep 9, 2008
Messages
25
The C$4 + D$4 was a simpler formula than what I have. Here is what I have. Column A has Account numbers (23 of them) and Column B:Z has monthly figures. For each account, I want to calculate figures in column AA with a different formula for each account and this account sequence repeats in various order over 600 time underneath the first set of complete accounts. I hope this makes sense.

Account
4000
5000
6000
7000
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,170
The C$4 + D$4 was a simpler formula than what I have. Here is what I have. Column A has Account numbers (23 of them) and Column B:Z has monthly figures. For each account, I want to calculate figures in column AA with a different formula for each account and this account sequence repeats in various order over 600 time underneath the first set of complete accounts. I hope this makes sense.

Account
4000
5000
6000
7000
=EVAL(VLOOKUP(LookupValue,FormulaTable,2,0))

where FormulaTable houses in its 2nd column values like:

'=C$4+D$4

On the other hand, Excel is not an appropriate environment for this kind of processing set up as a programming language like Lisp would.

EVAL requires the free morefunc.xll add-in.
 

gnmthead

New Member
Joined
Sep 9, 2008
Messages
25
I have installed the morefunc.xll and the formula worked like a charm. I can finally get some sleep. I offer you the most sincere gratitude. Also, I find this site to be very helpful and have referred to it on many occassions. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,248
Messages
5,449,242
Members
405,560
Latest member
Jadax

This Week's Hot Topics

Top