Pulling information from sheet to sheet

[steve3730]

Hi,Sorry I'm not too great with excel but I'm trying to do something I believe it is capable of. I have a sheet (1) set up for order entry and on another sheet (2) I have a list of items that have item numbers,descriptions,cost(Each in its own column but same row per item). I would like to know how can I set it up so that if you enter the item number in sheet 1( my order entry sheet) it will pull the rest of the items information of that item.

Thanks for the help and sorry for needing help with what might be pretty simple
-Steve

 

[Weazel]

without trying to overcomplicate it initially you could try just using vlookups to return the data column your interested in..

this assumes your order data is on a sheet named order entry. the vlookup matches the item number entered to the item number on the order entry sheet and returns the column of information you want, 2 for the description and 3 for the cost.

also assume that column E, F, and G are on a different sheet

hopefully that helps.

Excel 2013

	A	B	C	D	E	F	G
1	Item #	Description	Cost		Item #	Description	Cost
2	1	Description 1	11		6	Description 6	3
3	2	Description 2	16
4	3	Description 3	24
5	4	Description 4	24
6	5	Description 5	18
7	6	Description 6	3
8	7	Description 7	22
9	8	Description 8	10
10	9	Description 9	23
11	10	Description 10	14

<tbody>
</tbody>

order entry​

Worksheet Formulas Cell Formula F2 =VLOOKUP($E2,'order entry'!$A$2:$C$11,2,0) G2 =VLOOKUP($E2,'order entry'!$A$2:$C$11,3,0) <tbody> </tbody>

<tbody>
</tbody>

 

[steve3730]

that is exactly what I need! Now a few more questions if you don't mind. Do I have to enter the formula in each cell to make them pull the information? example you had was E3, would I have to enter the formula and change look_up value to E4 or can I do a range to make the entire E column an input?

Thanks!

 

[Weazel]

i'm not sure I follow, but the value in E3 would be the value you are wanting information on so you would manually enter it.

the Formula in F2 and G2 contain the formulas that retrieve the data.

bear in mind this is only to pull 1 record.

deciding on what types of formulas to use is usually based on the desired outcome as well as several other possible variables you may have particular to your data. i.e., are there duplicates, how many columns of data are you trying to return, is your data text or numeric ?

 

[steve3730]

yes so E3 would be my value I enter, If I want to enter a list of 10 items would I have to enter E4 as I did E3 and so on or is there a way to make E3-E13 all input fields for data that would retrieve data into column F and G in the corresponding row? Thanks for working with me on this

information I'm dealing with is pretty simple example would be
input:E3 Description(pulled from another worksheet)F3 Cost: (pulled from another sheet) G3
C903 3" copper 90 $50.00

 

[Weazel]

If you wanted to have the data for 10 items returned you would need to input each item the same as E3 then drag the corresponding formulas down for the 10 rows.
so the formula in F2 and G2 would be copied down to extract the information for the item number in E4, E5, etc.

There are probably several different ways to do this but we'd probably need to see some of your data to find the best method.

 

[steve3730]

perfect this is what I'm looking for. I think I may only have one last question. When there is no value entered is there any way to have the cell come up blank instead of #N/A

 

[Weazel]

theres a few ways to do it, the easiest way would be to use an iferror

=IFERROR(VLOOKUP($E2,'order entry'!$A$2:$C$11,2,0),"")

this would require you to have excel 2007 or later

 

[steve3730]

theres a few ways to do it, the easiest way would be to use an iferror

=IFERROR(VLOOKUP($E2,'order entry'!$A$2:$C$11,2,0),"")

this would require you to have excel 2007 or later

Worked perfectly! Thanks again

 

[steve3730]

Sorry I'm brainstorming about what else I may need on here and came up with another idea to add to this. if I had a dropdown menu with my 3 branches on here each would have its own seperate excel sheet to call data from. Is there an easy way to set up selecting a different branch would change the formula to pull from a different sheet?