look up or Macro function to search through multiple sheets

[VSK]

Hi All,

I am trying to search through multiple sheets with one particular item which has a quantity above 0. Each sheet is named by employee, and each sheet contains the items they hold with cat number, description and quantity. Not necessary all will have the same items , but definitely there could be same items with different quantity level with each employee.

This is shared in cloud. Hence the feature which i am looking is if some one has run out of one item, how can they search the other's sheets using a look up or a macro function. Search should be based on a Cat number or description with the quantity above 0. As long as the search can return the answer which sheet name got it , it would be great . There could be multiple employees ( multiple sheets ) who have the same item.

I have previously posted it here, but the title was crappy , and i couldn't edit it. Attaching the sample file here too. Even though i have only put 3 columns, there is lots of other data in each sheet, and hence pooling them to one sheet is not easy, more over i wouldnt know which employee got it, as the sheets are individually named under each employee name.

There may be a easier fix, but i am not good with excel. Any help would be really appreciated.

 

[rrocker1405]

Hi Vsk,

From the explanation above, I don't think you need a macro to do this trick for you. All you need is power query to append all the source values (considering all the headers of the source are same like in the example). once done create a pivot to list values or you can go one step ahead like in the attached file by creating a unique list (description) and by creating a relationship using Power Pivot.

Once the above is established, you can then every time click on data > refresh all in the ribbon to retrieve new information from multiple sources (shared drive, sharepoint, database and API's etc) and the pivot will also update accordingly.

Hope this helps.

Kind regards,
A!

 

[VSK]

Hi Rocker,

I am completely a dummy when it comes to such tricks.

While i am still trying to imitate the work you have done for me, can you help me display the sheet name ( which is staff name ) too next to the quantity

Basically i dont want to display all the Box numbers or Cat numbers, reason being each staff would have about 100 over items . Actually the box number here is the Parts( sorry for the confusion) . attaching the updated file.

Purpose of this is to Query and find out which staff or staffs got one particular item with a quantity above zero, so that other staff members can borrow it from him.

I have edited the file to reflect the correct description. If the sheet names are hard to display, alternatively i can add one more column called "location" which can be treated as Box1 , Box 2 etc.

As i changed the Description column, when i refresh, i am getting the below error

"""We couldn’t refresh the connection “Query-tblBox”. Here is the error message we got:

The “Microsoft.Mashup.OleDb.1” provider is not registered on the local machine."""

Also when i open the excel , in the output tab it displays all the box on one side, but when i click box 2 or cat number it display only those, how can i display all those as it appears when i open the file. What i have been doing is to click back button to get that view.

I have a lot to learn, thank you so much for helping me here