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

Mel C Roberts

New Member
Joined
Feb 26, 2002
Messages
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top