Vlookup with dynamic capabilities

  • Good Day All


    I have a workbook with five sheets (each of them a rental store name) and a sixth one (return store).
    Each of the sheets is set up the same way: Column A - Date, Column B - Last Name, Column C- First Name, Column D - S shirt, Column E - M Shirt, Column F - L Shirt, Column G - Time of rental.
    The "Return Store" sheet is set-up the same but is fro returns.


    What would be the best way / formula to count the liability (number of shirts not returned) across the five stores if I set up a seventh sheet (Liability)? What I need is to see, if Mr. X has rented 2 shirts of each size in 2 stores but has returned only one of each, then how many shirts are still with Mr. X.


    Thank you in advance for your input.


    Take care.

  • Hi Carim,
    Understood. But the 3D formula would be static, wouldn't it? adding up let's say cell C1 across multiple sheets.
    What I am looking for is to tally how many shirts have been rented by one person across the five stores (five sheets) and then subtract the number of shirts returned by the same person. i.e Mr. Smith might have been in all five stores, might have been only in one. He might have rented one of the sizes or all of them. I was thinking to use vlookup but I am stuck... sorry.....any help is kindly appreciated.

  • Hi Robert,


    Excellent idea to attach a sample ...:smile:


    The only missing bit ...is your ' Expected Result ' ...


    Feel free to add it manually ... even with written explanations ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim.
    My goal is to be able to show in the “Liability” sheet who has rented shirts, by tallying all the various shirts rented by any person across the 5 sheets (Store 1 through 5) and subtracting the shirts returned in the “Returns” sheet.
    If Mr. Smith has rented various sizes in any or all the stores and he did not return any or all of them, I need his name and number of un-returned shirts to appear in the “Liability” sheet.
    I tried with Vlookup but I am running in circles…

  • Hi Robert,


    With your definition of Liability ... my understanding is :


    Liability = Sum( All Stores) - Returns


    Is that right ...?


    If it is the case, the 3D Sum mentioned earlier should do the job ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim


    The problem is that I can have up to 1,000 names on the store sheets. One person (let's say Mr Smith) can have multiple entries on any (& possibly all 5 stores). So I need to add up all Mr. Simth entries from all Stores and subtract all his returns to get the liability figure.
    This is why I thought to go with vlookup or Index & Match on the liability sheet to calculate the above but I am getting nowhere.
    Appreciate any advice

  • Hi Robert,


    With over 1'000 Customers, 5 Stores and more to come, 4 Sizes of shirts, x different Colors, y Sales assistants, etc ...


    and the objective to monitor Entries and Returns within certain time frames over the whole business ...


    You are in fact after building an integrated Management System ...


    For this development ...Would recommend to clearly state all your needs ... and post your request in the HIRE section ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Ah, OK


    Thank you Carim.


    Forgot to tell you that regarding your 1'000 + customers, you should make your life easier and create a Unique Identifier ...


    This will help you ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)