Returning a Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
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
 
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?
 
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
 
Back
Top