Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookups returning formulas - not values - in excel
I have a long list of customers. Each of them have a unique pricing formula
associated with their account. On my "Tab1," I have written formulas to return the right commission by customer for each transaction my company is party to. (I wrote the formulas to calculate based on a plug value in column "c". The pricing formulas are each "IF" or "LOOKUP" formulas.) I have a second tab ("Tab2") that is a long list of thousands of transactions. (The value of each transaction is also located in column "c"). I wrote a VLOOKUP to refer to the customer commission formulas on Tab1. The vlookup successfully returns the VALUE in "Tab1"... but I am hoping to write it in such a way that it returns the FORMULA. That way, I can actually calculate the company commission by transaction by customer. (Right now, with my vlookup returning the VALUE instead of returning the FORMULA, my data calculations are worthless and based on the plug values in column "c" of "Tab1".) Any thoughts? Is there a better way to achieve this task? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookups returning formulas - not values - in excel
Do you want it to return just the customer-specific portion of the
formula, or the entire IF formula as shown in Excel? Generally, Excel's built-in functions can't do this. You'll need a macro or UDF. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookups returning formulas - not values - in excel
What you require - processing a batch of data through one algorithm - can be
done using the DataTable command. It's not really straightforward and to give an example we'd need your data layout and probably some examples of your formulas. Look in HELP and post back in this same thread if you can't get it done, and supply more details. -- Kind regards, Niek Otten Microsoft MVP - Excel "Buckshot" wrote in message ... I have a long list of customers. Each of them have a unique pricing formula associated with their account. On my "Tab1," I have written formulas to return the right commission by customer for each transaction my company is party to. (I wrote the formulas to calculate based on a plug value in column "c". The pricing formulas are each "IF" or "LOOKUP" formulas.) I have a second tab ("Tab2") that is a long list of thousands of transactions. (The value of each transaction is also located in column "c"). I wrote a VLOOKUP to refer to the customer commission formulas on Tab1. The vlookup successfully returns the VALUE in "Tab1"... but I am hoping to write it in such a way that it returns the FORMULA. That way, I can actually calculate the company commission by transaction by customer. (Right now, with my vlookup returning the VALUE instead of returning the FORMULA, my data calculations are worthless and based on the plug values in column "c" of "Tab1".) Any thoughts? Is there a better way to achieve this task? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookups returning formulas - not values - in excel
I want the vlookup to return the customer specific formula - each of which
are on the different rows of tab 1. I can get my vlookup to look at the right cell, but when it returns an answer it returns the value that is in the cell at that time, not the formula that is in the cell (that is the result that i am trying to achieve). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookups returning formulas - not values - in excel
Buckshot wrote:
I have a long list of customers. Each of them have a unique pricing formula associated with their account. On my "Tab1," I have written formulas to return the right commission by customer for each transaction my company is party to. (I wrote the formulas to calculate based on a plug value in column "c". The pricing formulas are each "IF" or "LOOKUP" formulas.) I have a second tab ("Tab2") that is a long list of thousands of transactions. (The value of each transaction is also located in column "c"). I wrote a VLOOKUP to refer to the customer commission formulas on Tab1. The vlookup successfully returns the VALUE in "Tab1"... but I am hoping to write it in such a way that it returns the FORMULA. That way, I can actually calculate the company commission by transaction by customer. (Right now, with my vlookup returning the VALUE instead of returning the FORMULA, my data calculations are worthless and based on the plug values in column "c" of "Tab1".) Any thoughts? Is there a better way to achieve this task? Thanks in advance. not sure if I understand your requirement. Try using Excel built-in feature, Tool Option and check Formulas under Window Option This will show all the formulas in your sheet and you can uncheck it if you want value. HTH -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookups returning formulas - not values - in excel
On Dec 24, 10:53*am, Buckshot
wrote: I want the vlookup to return the customer specific formula - each of which are on the different rows of tab 1. *I can get my vlookup to look at the right cell, but when it returns an answer it returns the value that is in the cell at that time, not the formula that is in the cell (that is the result that i am trying to achieve). * Yes, that is how Excel is supposed to work. It doesn't do what you are asking with the built-in functions. You can just repeat the customer formula in other cells. A little more convoluted, but more what you are asking for: EVAL or EVALUATE is a common UDF (search the internet for it) that references an old, disabled Excel function that you aren't normally allowed to use, anymore. (blame authors of viruses for Microsoft removing this function) You have to reference it with a macro to make it work. You could take each customer formula and enter it as text in a column. Then you can reference the formula whenever you wish to use it, plus you can see it in text format easily if you ever need to. Then using INDIRECT or CONCATENATE formulas combined with EVAL will perform calculations based off the text version. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
Matching and returning values to a master sheet in Excel 2007 | Excel Worksheet Functions | |||
Why are my formulas and vlookups not updating? | Excel Worksheet Functions | |||
Weird vlookups and formulas on large sheets | Excel Discussion (Misc queries) | |||
vlookup formulas returning no values | Excel Worksheet Functions |