VB / Lookup search for "X" in one column and return a formul

Mel C Roberts · Feb 28th, 2002, 05:45 AM

Hi,
Can anyone help.
I have a table that copies from a pivot table and hence the data location can change dependent upon the pivot table criteria.
What i need is a formula that will search one column to find the statement "Total" (say it is in B7) and then return a formula in the same row but in a different column ie E7.
I have done it this way to enable me to change the sum function which is determined by the majority of my data. The formula i need to return in the said cell is C7/D7 for the example above.
Obviously the next time i refresh the chart the "total" cell could have moved hence the need for some VB or a Lookup?
Thanks heaps.
Mel x

Aladin Akyurek · Feb 28th, 2002, 05:55 AM

Quote:

On 2002-02-28 04:45, Mel C Roberts wrote:
Hi,
Can anyone help.
I have a table that copies from a pivot table and hence the data location can change dependent upon the pivot table criteria.
What i need is a formula that will search one column to find the statement "Total" (say it is in B7) and then return a formula in the same row but in a different column ie E7.
I have done it this way to enable me to change the sum function which is determined by the majority of my data. The formula i need to return in the said cell is C7/D7 for the example above.
Obviously the next time i refresh the chart the "total" cell could have moved hence the need for some VB or a Lookup?
Thanks heaps.
Mel x

Mel,

Worksheet functions do not return a formula, but a computed result. Maybe you want to compute an address. Care to elaborate with an example along with what is expected to be returned?

Aladin

Mar 12th, 2002, 08:13 AM

Aladin.

OK an example of my query.
Pivot table calculates three areas North, SOuth and West. The information is summarised monthly and also has sub totals and a grand total eg.

North
JAN 10
FEB 8
Total 18

South
JAN 5
FEB 6
Total 11

West
JAN 2
FEB 10
Total 12
Grand Total 41

In one column against this i have a calulated item to work out average days spent on each item. This is summed. Hence the total and grand total column sum as well, when i need them to return the average for the area. To do this i have written VB to copy the pivot table into another sheet and hence enable me to change the formulas. This is really inconvenient as every time I refresh the information the formulas have to be reinputted.
Therefore i need to be able to search the first column to find the " North total" cell location (which will change position). And i need to return the average of the two columns in the same row but different column, thus overrighting the sum calculation the pivot table has calculated.
Does this help?

Mar 12th, 2002, 08:13 AM

Aladin.

OK an example of my query.
Pivot table calculates three areas North, SOuth and West. The information is summarised monthly and also has sub totals and a grand total eg.

North
JAN 10
FEB 8
Total 18

South
JAN 5
FEB 6
Total 11

West
JAN 2
FEB 10
Total 12
Grand Total 41

In one column against this i have a calulated item to work out average days spent on each item. This is summed. Hence the total and grand total column sum as well, when i need them to return the average for the area. To do this i have written VB to copy the pivot table into another sheet and hence enable me to change the formulas. This is really inconvenient as every time I refresh the information the formulas have to be reinputted.
Therefore i need to be able to search the first column to find the " North total" cell location (which will change position). And i need to return the average of the two columns in the same row but different column, thus overrighting the sum calculation the pivot table has calculated.
Does this help?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 05:45 AM	#1
Mel C Roberts New Member Join Date: Feb 2002 Location: England Posts: 40	Hi, Can anyone help. I have a table that copies from a pivot table and hence the data location can change dependent upon the pivot table criteria. What i need is a formula that will search one column to find the statement "Total" (say it is in B7) and then return a formula in the same row but in a different column ie E7. I have done it this way to enable me to change the sum function which is determined by the majority of my data. The formula i need to return in the said cell is C7/D7 for the example above. Obviously the next time i refresh the chart the "total" cell could have moved hence the need for some VB or a Lookup? Thanks heaps. Mel x

Mar 12th, 2002, 08:13 AM	#3
Guest Posts: n/a	Aladin. OK an example of my query. Pivot table calculates three areas North, SOuth and West. The information is summarised monthly and also has sub totals and a grand total eg. North JAN 10 FEB 8 Total 18 South JAN 5 FEB 6 Total 11 West JAN 2 FEB 10 Total 12 Grand Total 41 In one column against this i have a calulated item to work out average days spent on each item. This is summed. Hence the total and grand total column sum as well, when i need them to return the average for the area. To do this i have written VB to copy the pivot table into another sheet and hence enable me to change the formulas. This is really inconvenient as every time I refresh the information the formulas have to be reinputted. Therefore i need to be able to search the first column to find the " North total" cell location (which will change position). And i need to return the average of the two columns in the same row but different column, thus overrighting the sum calculation the pivot table has calculated. Does this help?