+ Reply to Thread
Results 1 to 9 of 9

Adding a match formula to an existing formula

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Adding a match formula to an existing formula

    Hi all

    I was helped previously by DLL with this formula:
    Please Login or Register  to view this content.
    This formula would pull data from various sheets depending on the user selection in the dashboard (the branch selected). If all branches are selected then it would need to sum all the branches together – I will come onto this as I have a problem with it...

    I now need to add in an additional match function, can anyone help with this please?

    I need it to match cell B67 (on the “calcs” sheet) with Column B on the corresponding sheet (e.g payables/inventory/receivables).

    I have highlighted the cell where I need the formula in orange.

    The other problem I have is that when ALL branches are selected it doesn’t add up correctly despite each branch being correct – why would this be? e.g. if you select branch 1 on the dashboard the forumla returns the correct data however if all is selected it pulls a number in and I dont know where this is from.

    Can anyone look into this please?
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding a match formula to an existing formula

    Does this work?

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Adding a match formula to an existing formula

    Hi- I have been testing this and I have noticed a problem with my data layout as I have lookup references with the same name - I am amending this now..

  4. #4
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Adding a match formula to an existing formula

    Hi,

    The formula works for all the individual branches but not in total - does anyone know why this would be?

    I notice that when when all is selected it doesnt take into account this new addition (solution posted in post #2) - as it provides the following same data:
    Payables Trade payables -29026
    Payables Capital Creditors -29026
    Payables Other payables -29026

    I dont know what this -29026 is summing??

    Can anyone help with this part of the formula please?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding a match formula to an existing formula

    I am not following... where are you selecting "All" and where are you seeing the -29026 exactly?

  6. #6
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Adding a match formula to an existing formula

    "all" can be selected from the option box on the dashboard sheet. The user can select the different branches also from here. These option boxes are linked to the sheet "calcs".

    The totals -29026 are on the calcs sheet highlighted in orange under the section payables. This may be only on my latest model. Do you need me to upload it again?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding a match formula to an existing formula

    Try:

    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Adding a match formula to an existing formula

    Thanks NBVC - just testing it now

  9. #9
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Adding a match formula to an existing formula

    Thanks NBVC - this formula worked brilliantly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1