Forum Discussion

Kevin_Hills's avatar
Kevin_Hills
Copper Contributor
Jan 04, 2023

How to create a search formula that will search an entire work book and summarise quantities

Hi all, i have a spreadsheet that is a basic map of my warehouse. Each column represents a line of pallets of a certain product sku. In each column the product abbreviation and the quantity is entered. For example (Cell A1: MMPN21 64C) 64C is the carton quantity. (Cell A2: MMPN21 36C and so on down the column) then column B might have a different product such as: (Cell B1: PSR19 88C - Cell B2: PSR19 85C) 

There are 6 sheets in total in the workbook. Each sheet represents a room that the product is stored and named as that room (example room names: parks / althaus / aldercorso). 

 I want to create on a seperate sheet a search function so i can type into a cell, the product sku, and in the adjacent cells it will list the room that the product is located and the quantitiy that is in that particular room. Then if the product is in multiple rooms it will list the other rooms underneath in the same way. So in simple terms, i want to be able to type a product sku and excel will tell me the quantity i have in each room.

I hope someone is able to help me with this as i have struggled.

 

Thanks 👍 

excel

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 04, 2023

    Kevin_Hills 

    Since you have not given any precise information about your Excel version, operating system, storage medium, etc., it is difficult to understand what the digital environment is and which solutions are best suited or which are possible.

    Nevertheless, I am sending you some solutions that might help you :).

     

    Without VBA

    Search should be performed across entire worksheet.

    You can search a value or text not only in a specific worksheet but in the whole workbook,

    please do as follows.

    1) Activate the relevant workbook.

    2) Start the search dialog by pressing the key combination Shift + F5 or Ctrl + F.

    3) Click the Options button here. Additional search parameters are added to the dialog box.

    4) Now select Workbook from the Search list box. Then click the Find All button.

     

    With VBA

    Added example file.

    It's an old file that I got from the internet, unfortunately I don't remember where exactly. Believe it meets your specifications, or it's along those lines.

     

    You could also try Power Query in Excel

     

    …many roads lead to Rome and even more to an Excel solution :).

     

     

    Hope I was able to help you with this info.

     

    NikolinoDE

    I know I don't know anything (Socrates)

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources