If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Returning a Value
I had two spreadsheets. The first contains two columns of
data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? |
#2
|
|||
|
|||
Returning a Value
Hi
use VLOOKUP. e.g. =VLOOKUP(A1,'sheet1'!A1:B100,2,0) -- Regards Frank Kabel Frankfurt, Germany wrote: I had two spreadsheets. The first contains two columns of data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? |
#3
|
|||
|
|||
Returning a Value
Hi
use VLOOKUP say your data is in the range A1:B10 on sheet1 and in cell A1 on sheet2 you are typing in the value in A column in Sheet 1 (you can use data validation to provide a drop down list of these values btw) and in Sheet2 - B1 is where you want the related information to appear the formula in this cell would be =VLOOKUP(A1,Sheet1!$A$1:$B$10,2,false) hope this helps Cheers JulieD wrote in message ... I had two spreadsheets. The first contains two columns of data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? |
#4
|
|||
|
|||
Returning a Value
I appreciate the reply, but it's not working.
I should have noted that the data in Column2 of Sheet2 is simply 270 MORE than the data in Column1 (it's a date manufactured vs. shelf life spreadsheet, so 270 days after manufacturing it's past its shelf life). And the value to be entered in A1 of Sheet2 is a specially customized value. So the data in Sheet1 is a number, but the value to be entered in Sheet2 isn't. I keep getting the #N/A error. Thill, thanks for the prompt reply. -----Original Message----- Hi use VLOOKUP say your data is in the range A1:B10 on sheet1 and in cell A1 on sheet2 you are typing in the value in A column in Sheet 1 (you can use data validation to provide a drop down list of these values btw) and in Sheet2 - B1 is where you want the related information to appear the formula in this cell would be =VLOOKUP(A1,Sheet1!$A$1:$B$10,2,false) hope this helps Cheers JulieD wrote in message ... I had two spreadsheets. The first contains two columns of data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? . |
#5
|
|||
|
|||
Returning a Value
Hi
okay now i'm getting confused, can you type out in your reply post a sample of one line of sheet 1 - column A & B and one line of sheet 2 column A & B showing what you've got and what you want. Cheers JulieD wrote in message ... I appreciate the reply, but it's not working. I should have noted that the data in Column2 of Sheet2 is simply 270 MORE than the data in Column1 (it's a date manufactured vs. shelf life spreadsheet, so 270 days after manufacturing it's past its shelf life). And the value to be entered in A1 of Sheet2 is a specially customized value. So the data in Sheet1 is a number, but the value to be entered in Sheet2 isn't. I keep getting the #N/A error. Thill, thanks for the prompt reply. -----Original Message----- Hi use VLOOKUP say your data is in the range A1:B10 on sheet1 and in cell A1 on sheet2 you are typing in the value in A column in Sheet 1 (you can use data validation to provide a drop down list of these values btw) and in Sheet2 - B1 is where you want the related information to appear the formula in this cell would be =VLOOKUP(A1,Sheet1!$A$1:$B$10,2,false) hope this helps Cheers JulieD wrote in message ... I had two spreadsheets. The first contains two columns of data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? . |
#6
|
|||
|
|||
Returning a Value
Haha, sorry.
Sheet 1 (database) Manufacturer's Date(MD) Code (formatted as date) (MD + 270, special format) 12-May-03 Feb06 04 PH 13-May-03 Feb07 04 PH 14-May-03 Feb08 04 PH and so on and so on Sheet 2 (form) Shelf Life Manufacturer's Date (to be entered) (to be calculated) In Sheet 1, Column 2 is simply a special format of the sum of Column 1 and 270. So you'd think the equation I'd need for Sheet 2 would be "Munfacturer's Date = Shelf Life - 270, and Sheet 1 wouldn't even be necessary. The problem is that while the Code is a special format of MD plus 270, if I try to type the code freely into Shelf Life of Sheet 2, Excel doesn't recognize it as a number anymore, so it won't subtract 270 to give me the Manufacturer's Date. Second, I can't do VLOOKUP. There could be two reasons for this: the first is that, again, the code if typed won't be a number, and the format for Sheet 1 IS a number. Or, even if I format everything to be a number, Code is still MD + 270, so Excel gets confused. Did this help at all? -----Original Message----- Hi okay now i'm getting confused, can you type out in your reply post a sample of one line of sheet 1 - column A & B and one line of sheet 2 column A & B showing what you've got and what you want. Cheers JulieD wrote in message ... I appreciate the reply, but it's not working. I should have noted that the data in Column2 of Sheet2 is simply 270 MORE than the data in Column1 (it's a date manufactured vs. shelf life spreadsheet, so 270 days after manufacturing it's past its shelf life). And the value to be entered in A1 of Sheet2 is a specially customized value. So the data in Sheet1 is a number, but the value to be entered in Sheet2 isn't. I keep getting the #N/A error. Thill, thanks for the prompt reply. -----Original Message----- Hi use VLOOKUP say your data is in the range A1:B10 on sheet1 and in cell A1 on sheet2 you are typing in the value in A column in Sheet 1 (you can use data validation to provide a drop down list of these values btw) and in Sheet2 - B1 is where you want the related information to appear the formula in this cell would be =VLOOKUP(A1,Sheet1!$A$1:$B$10,2,false) hope this helps Cheers JulieD wrote in message ... I had two spreadsheets. The first contains two columns of data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? . . |
#7
|
|||
|
|||
Returning a Value
the simple subtraction of 270 from sheet 1 column 2 works for me
vlookup works for me (except you have to have a hidden column to the right of Code which has the formula = A1 etc in it as the lookup value must be the leftmost thing in the table) - i also used data validation to provide a pick list for sheet 2 column1 how did you format your sheet 1 column 2 - i used a custom format of mmmdd yy "PH" if you can't get it to work, email me direct and i'll email you a sample spreadsheet. Cheers JulieD how about wrote in message ... Haha, sorry. Sheet 1 (database) Manufacturer's Date(MD) Code (formatted as date) (MD + 270, special format) 12-May-03 Feb06 04 PH 13-May-03 Feb07 04 PH 14-May-03 Feb08 04 PH and so on and so on Sheet 2 (form) Shelf Life Manufacturer's Date (to be entered) (to be calculated) In Sheet 1, Column 2 is simply a special format of the sum of Column 1 and 270. So you'd think the equation I'd need for Sheet 2 would be "Munfacturer's Date = Shelf Life - 270, and Sheet 1 wouldn't even be necessary. The problem is that while the Code is a special format of MD plus 270, if I try to type the code freely into Shelf Life of Sheet 2, Excel doesn't recognize it as a number anymore, so it won't subtract 270 to give me the Manufacturer's Date. Second, I can't do VLOOKUP. There could be two reasons for this: the first is that, again, the code if typed won't be a number, and the format for Sheet 1 IS a number. Or, even if I format everything to be a number, Code is still MD + 270, so Excel gets confused. Did this help at all? -----Original Message----- Hi okay now i'm getting confused, can you type out in your reply post a sample of one line of sheet 1 - column A & B and one line of sheet 2 column A & B showing what you've got and what you want. Cheers JulieD wrote in message ... I appreciate the reply, but it's not working. I should have noted that the data in Column2 of Sheet2 is simply 270 MORE than the data in Column1 (it's a date manufactured vs. shelf life spreadsheet, so 270 days after manufacturing it's past its shelf life). And the value to be entered in A1 of Sheet2 is a specially customized value. So the data in Sheet1 is a number, but the value to be entered in Sheet2 isn't. I keep getting the #N/A error. Thill, thanks for the prompt reply. -----Original Message----- Hi use VLOOKUP say your data is in the range A1:B10 on sheet1 and in cell A1 on sheet2 you are typing in the value in A column in Sheet 1 (you can use data validation to provide a drop down list of these values btw) and in Sheet2 - B1 is where you want the related information to appear the formula in this cell would be =VLOOKUP(A1,Sheet1!$A$1:$B$10,2,false) hope this helps Cheers JulieD wrote in message ... I had two spreadsheets. The first contains two columns of data, and on the second I wish to set up an equation (no macros or sorts) so that when I type in a value from the first column of the first spreadsheet, it will automatically return the corresponding value from the second column. What do I do? . . |
Thread Tools | |
Display Modes | |
|
|