Connecting worksheets.

[jda2000]

I have a data base from my company that lists our inventory. It starts with stock number, followed by publisher, price, shelf location, etc. I want to know if there is a way that I can set up a new worksheet that references this data base. Bottom line, in a perfect world, I would like to enter the stock number on a blank worksheet, hit return, have all the other associated info auto fill, then have the cursor automatically return to the next line in the stock number box. Is this possible? Also, it is possible to just retrieve some of the data (for example, I enter the stock number, it auto fills publisher, price, but NOT shelf location). Any info would be helpful. Thank you.

 

[richh]

Hi jda,

Welcome to the forum! I think you are looking for a VLOOKUP function.

B2: =IF($A2="", "", IFERROR(VLOOKUP(Sheet4!$A2,Sheet3!$A$2:$E$12,2,FALSE), "Not Found"))
C2: =IF($A2="", "", IFERROR(VLOOKUP(Sheet4!$A2,Sheet3!$A$2:$E$12,3,FALSE), "Not Found"))
D2: =IF($A2="", "", IFERROR(VLOOKUP(Sheet4!$A2,Sheet3!$A$2:$E$12,4,FALSE), "Not Found"))
E2: =IF($A2="", "", IFERROR(VLOOKUP(Sheet4!$A2,Sheet3!$A$2:$E$12,5,FALSE), "Not Found"))

 

[jda2000]

Thanks Richh, I watched some youtube tutorials, was able to sort things out after some fiddling (needed to insert a pesky little "trim" into things to make it work. So I have another question for you: I have things set up so that my sorted data comes up on a separate sheet in the workbook with the raw data. Is there a way that I can delete the raw data page so that I can just sent the one sheet back to my company? I tried just to delete the page, but of course once the data was gone, the sorted sheet lost all of its values. Is there a way to convert the sorted sheet to just raw data that will stand on its own with out having to reference the other? Thanks

 

[richh]

Hey again,

Yes, you can do that as well.

I would recommend making a copy of the report (whatever you need to send). In the copy, select all of your cells and click copy (Control + C) then right click and choose to Paste By Values (icon is a clipboard with 123). This removes your formulas but retains the data. This way, you keep your report generator sheet as desired, with the formulas still embedded, and can send the copy with those formulas stripped out.

 

[jda2000]

Amazing! thanks again, know very little about Excel, you have been a big help.

 

[jda2000]

You have been such a big help, just gotta bug you one more thing.....Can you tell me the best way to transfer one sheet of a workbook to another workbook? I have been spending a lot of time on youtube tutorials slogging through stuff I don't need in hopes of finding the one nugget that I do, your clear and concise answers have been much more productive. Thanks

 

[jda2000]

Hey Ricch, never mind about the last question, I figured it out. Thanks

 

[jda2000]

Dear Ricch, I have finished one project, but have one to go. In this project I want to set up an order sheet. I would enter a stock number in field 1, fields 2 and 3 would be auto filled via vlookup, then a current stock quantity would be entered in cell 4. In a perfect world, I would like to enter the stock number in cell 1, hit return, have 2 and 3 auto fill, then have the cursor automatically go to the 4th cell. After entering a quantity there, I would hit enter and have the cursor automatically go back to cell one in the next row down so that I can enter the next stock number. Is this possible to do?