Forum Discussion

  • Dublinguy1980's avatar
    Dublinguy1980
    Copper Contributor
    Jan 25, 2024
    Basically i have did this up as a test before i do the proper one

    i will have multiple tabs on the bottom for customers / suppliers etc
    on the left is week 1 to 52
    on the top are services we will offer and unit rates etc

    i want to be able to input weekly figures ( unit rate will stay same ) but quantities will vary , but i'd like it to add the figures to the master sheet ( pic 2 )

    so tabs will be like

    master / customer 1 / customer 2 / customer 3 / etc

    if they all use a certain service lets say packing , i want to be able to see overall how many units of packing was charged out

    but no idea of what formula ( excel novice ) could somebody do something for me id be very grateful
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 25, 2024

    Dublinguy1980 

    The version is very important when approaching this task. Based on the screen caps, it looks like you're using Excel 2007?

     

    In Excel 365, options might include PowerQuery and using dynamic arrays (VSTACK, FILTER, and the like).  With 2007, these tools are not available and you're left with INDIRECT (a slow and volatile function) or going the vba route.

     

    Another thing to consider,  forgoing the master sheet/individual sheets setup and storing all like-data (Data with similar column headers) in 1 sheet and analyze it with pivots, formulas, charts, etc.

Share

Resources