A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Returning a Value



 
 
Thread Tools Display Modes
  #1  
Old June 11th, 2004, 04:32 PM
external usenet poster
 
Posts: n/a
Default 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  
Old June 11th, 2004, 04:38 PM
JulieD
external usenet poster
 
Posts: n/a
Default 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  
Old June 11th, 2004, 04:58 PM
external usenet poster
 
Posts: n/a
Default 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  
Old June 11th, 2004, 05:01 PM
JulieD
external usenet poster
 
Posts: n/a
Default 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  
Old June 11th, 2004, 06:57 PM
external usenet poster
 
Posts: n/a
Default 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  
Old June 12th, 2004, 02:00 PM
JulieD
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.