Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for datats in different Exel sheets
Hello together, I have a problem which I can not solve. I know how I can search and import datas from an other Excel Sheet with the help of the formula VLOOKUP but I don`t know how I can get the information I need if I`m search in different Exelsheets for this information. So my problem exactly is that I use for different Groups of Products, different Excel sheets, and I want now creat one sheet in wich I can insert in one Cell the number of the product and in the other one automatically appear the price of the product. So Excel have to search in all of my different sheets to find the number of the Product with the right price. I couldn`t until now not find any way to sove this problem, and it is for me, because of other reasons, not possible to copy all my products in one exel sheet. Thanks a lot Franz -- Franz Muster ------------------------------------------------------------------------ Franz Muster's Profile: http://www.excelforum.com/member.php...o&userid=31369 View this thread: http://www.excelforum.com/showthread...hreadid=510621 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for datats in different Exel sheets
How many sheets do you have?
You could look in each sheet until you find it. =IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,0))),VLOOK UP(A1,Sheet2!A:B,2,0), IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,0))),VLOOKU P(A1,Sheet3!A:B,2,0), IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKU P(A1,Sheet4!A:B,2,0), "missing from all 3"))) (I stopped after looking at 3 sheets. You could continue until you try to add the 8th lookup--excel can nest 7 functions.) If the part numbers have some unique identifier (a special prefix that corresponds to the product), you could use that to determine which sheet to look through. Franz Muster wrote: Hello together, I have a problem which I can not solve. I know how I can search and import datas from an other Excel Sheet with the help of the formula VLOOKUP but I don`t know how I can get the information I need if I`m search in different Exelsheets for this information. So my problem exactly is that I use for different Groups of Products, different Excel sheets, and I want now creat one sheet in wich I can insert in one Cell the number of the product and in the other one automatically appear the price of the product. So Excel have to search in all of my different sheets to find the number of the Product with the right price. I couldn`t until now not find any way to sove this problem, and it is for me, because of other reasons, not possible to copy all my products in one exel sheet. Thanks a lot Franz -- Franz Muster ------------------------------------------------------------------------ Franz Muster's Profile: http://www.excelforum.com/member.php...o&userid=31369 View this thread: http://www.excelforum.com/showthread...hreadid=510621 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for datats in different Exel sheets
Franz,
Domenic has suggested a quite powerful formula for VLOOKUP() over multiple sheets. Look in microsoft.public.excel.worksheet.functions and search with keywords Vlookup over multiple sheets domenic and I think you will find exactly what you are looking for. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheets (Yes, I've already done a search on previous posts) | New Users to Excel | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
exel spread sheets | Excel Worksheet Functions | |||
FAQ Spreadsheet with search function | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions |