Hello forum,
I am a long time stalker but i decided it was about time to register and post my own question since i was getting nowhere with what i was trying. I have attached a simulation of the excel file i am working at my job, if anyone can help me. Also i have provided some images just to clarify my point.
So i am trying to create an excel file with 2 sheets regarding employee's monthly traveling expenses. In the first sheet there will an overview on monthly expenses depending the month that the user is choosing from the drop down list (cell D2) (1st image). The data that was validated in order for the drop list to be created is in sheet2 where i am supposed to keep track of everyone's expenses during each month. As you can see i have filled 6 months just for the sake of the example (2nd image).
Thus, my aim is to make a functional and easy-to-read overview in Sheet1. That resolves around the drop-down list in D2. What i want is the column D to be filled with the relevant values from Sheet2 (3rd image). For example if i choose "February", i want the values from that month to be extracted in column D in sheet1. That would be an easy task with a Vlookup or Index/Match with D2 as the lookup value. However, since we are talking about hundreds of employees that their expenses change every month, i would like only the cells that contain a value >0 to be extracted (so no blanks and no zeros). That's the first step i would appreciate some help, since i can't find a way to combine such a formula that skips blanks with a drop-down list as the lookup value.
The second step that probably would give me headaches is to match the extracted values with the relevant IDs (4th image). Let's say that i have chosen in the drop-down list February as my month of choice and column D shows the values that are >0 from sheet2. I am searching for a formula that will pull the right ID that corresponds to the amount at D Column. To be honest, i haven't put much thought on that 2nd step, since i am stuck at the 1st one.
I do hope i was straightforward and made my point clear. Every piece of advice would be helpful. Thank you in advance.
Screenshot_4.pngScreenshot_5.pngScreenshot_6.pngScreenshot_7.png
Bookmarks