VB / Lookup search for "X" in one column and return a formul
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    England
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,820
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    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?



  4. #4
    Guest

    Default

      
    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?



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com