Pulling information from sheet to sheet


New Member
Nov 30, 2016
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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
1Item #DescriptionCostItem #DescriptionCost
21Description 1116Description 63
32Description 216
43Description 324
54Description 424
65Description 518
76Description 63
87Description 722
98Description 810
109Description 923
1110Description 1014

order entry

Worksheet Formulas
F2=VLOOKUP($E2,'order entry'!$A$2:$C$11,2,0)
G2=VLOOKUP($E2,'order entry'!$A$2:$C$11,3,0)


Upvote 0
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?

Upvote 0
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 ?
Upvote 0
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

Upvote 0
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.
Upvote 0
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
Upvote 0
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
Upvote 0
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?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back