Results 1 to 7 of 7

Thread: updating formulas

  1. #1

    updating formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    From the attachment, I am trying to find a way of being able to automatically update the master sheet with customer information from the customer 1 and 2 tabs.

    I have tried to use pivot tables and the get pivot function but the problem is when a new date with new information is added to the customers tabs the master sheet is not automatically updated with the most current information. Am I using grt pivot data incorrectly or is there another way?

    Any help would be great fully appreciated.
    Attached Files Attached Files

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    I don't think that you have the best layout for your data. I think it should be transposed, as the number of Products will likely remain relatively "fixed", but the number of transactions will constantly increase.
    I the attached workbook, I have transposed the data on the the 2 customer sheets, and created tables using Insert > Table and I named them Customer_1 and Customer_2 respectively because table names cannot contain spaces.
    I then changed your headings in B2 and C2 on sheet Master to coincide.

    Then the simple formula of
    =SUM(INDIRECT(B$2&"["&$A3&"]"))
    copied across and down provided your result.

    As tables are automatically dynamic, they will grow as you add more data (rows) or if you add more products (columns)
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Hiya,

    Many thanks for your reply, almost there, however I didnt want to sum up the products, I wanted to be able to show on the "master" sheet the latest figure for the latest date on the customers tabs, Otherwise I wouldnt have the need for a master sheet. The only way I can think of doing it is to use a pivot table and then using the =getpivotdata formula but the problem is being able to update the pivot tables automatically.

    I dont know if there is another way or if you have any other suggestions?

    Thank you for your response

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    You can find the last item in a numeric range, by carrying out a Lookup, for a large value (one that will not be found within your data).
    Lookup then returns the last value it found whilst searching for your value.
    There are many different options for choosing a large number, I normally use 99 to the power of 99 ( 99^99)
    So just change the formula I gave you in my file to

    =LOOKUP(99^99,INDIRECT(B$2&"["&$A3&"]")) in cell B3, and copy across and down
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  5. #5
    Hi,

    Many thanks but I keep getting a #REF! error.

  6. #6
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi
    Then I can only suggest that you have not copied the formula correctly.

    =LOOKUP(99^99,INDIRECT(B$2&"["&$A3&"]"))

    Take a look at the attached file.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  7. #7
    Absolute magic, you are awesome!

Posting Permissions

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