access data from another sheet in excel

November 1, 2010 at 09:01:44
Specs: Windows 7

i have a spreadsheet with 2 sheets. the first sheet is used to print a receipt, the second has the products.

I would like to type in the code on the 1st sheet and it to fill in description and price taking the data from the 2nd sheet. there are 2 columns on the 2nd sheet, code, description and price.

can anyone help with a formula or process to do this?


See More: access data from another sheet in excel

Report •


#1
November 1, 2010 at 09:08:56

Sounds like VLOOKUP should work for you.

Look it up in the Excel Help files and see if you can get it to work.

If not, come on back with some more details and we'll see what we can do to help.


Report •

#2
November 1, 2010 at 09:10:19

thanks, i tried VLOOKUP but i cant quite figure out what conditions to put in.

Report •

#3
November 1, 2010 at 09:22:15

You'll note that in my response I said:

"If not, come on back with some more details and we'll see what we can do to help."

Keep in mind that we can't see your spreadsheet from where we're sitting, so if you can't figure out conditions to put in, we certainly can't.

You'll need to give us some idea how how the worksheets are laid out. Some examples of the data in Sheet 2, what columns it's in etc. might help

What(where?)do you plan to enter data in Sheet 1 and what(where?)do you expect as results?



Report •

Related Solutions

#4
November 1, 2010 at 09:32:00

ok sorry about that.

sheet 1 starting at A7-A9 for code, des, price.

sheet 2 for lookup is A1-A786 for code, B1-B786 for des and C1-C786 for price.

i want to type the code into A7 and it autofill A8 with des and A9 with price. hope this helps. if not let me know what else you need to know and ill provide it.

thanks.


Report •

#5
November 1, 2010 at 13:46:55

As per the Help files, the syntax of VLOOKUP is:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Your lookup_value is A7
Your table_array is Sheet2!$A$!:$C$786
Your col_index_num is 2 for Column B and 3 for Column C
Your range_lookup is FALSE or 0, since you are looking for an exact match


Therefore A8 should contain:

=VLOOKUP(A7, Sheet2!$A$1:$C$786, 2, 0)

This will "look up" the value you put in A7 in the list of codes in the first column of the table_array (Sheet2!A1:C786) and return the value from the 2nd column of that table, which would be Sheet2!B1:B786.

To pull values from Column C, use =VLOOKUP(A7, Sheet2!$A$1:$C$786, 3, 0)

Note: The col_index_num is 2 for Column B and 3 for Column C only because they are the 2nd and 3rd columns of your table_array, not because they are the 2nd and 3rd column of the spreadsheet.

For example, if your table_array was G5:J47, a col_index_num of 2 would refer to Column H, the 2nd column of the table_array. Column J would have a col_index_num of 4.


Report •

#6
November 2, 2010 at 07:19:16

thanks for your help. that worked perfectly, i see where i was going wrong now.

is there a way to make the cell blank rather than displaying an #N/A if there is no value for it lookup?


Report •

#7
November 2, 2010 at 07:44:45

dont worry, i used put the vlookup in an IF(ISBLANK) to void the blank value.

Report •

#8
November 2, 2010 at 09:00:37

Another (more common) option is to use ISNA().

ISNA handles all situations where VLOOKUP returns #N/A, not just when the lookup_value cell is blank.


Report •

#9
November 2, 2010 at 09:21:12

thanks for that, i will experiment with that and see how it works out.

thanks alot for all your help with this.


Report •


Ask Question