IF statement that returns a formula

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,138
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.
 

Forum statistics

Threads
1,082,106
Messages
5,363,171
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top