Formula
 

In a WorkSheet, I have Sheet 1= 365 rows with column a = date 1/1/06 thru
12/31/06 and in columns b thru av infomation about that date.

In Sheet 2 I would like to insert a date in cell 1 that would equal one of
the dates that are in sheet 1 and have excel copy to sheet 2 everything
related to that row from sheet 1.

Can someone tell me what formula should I create?
--
Museum Restaurant Group

Formula
 

In coloumn B, insert the following formula, then copy it across to col AV.
The ",2" must change for every column to the right, ie C=3, D=4 etc
=IF($A1="","",VLOOKUP($A1,Sheet1!A1:AV365,2,FALSE) )

"MRG Perez" wrote:

In a WorkSheet, I have Sheet 1= 365 rows with column a = date 1/1/06 thru
12/31/06 and in columns b thru av infomation about that date.

In Sheet 2 I would like to insert a date in cell 1 that would equal one of
the dates that are in sheet 1 and have excel copy to sheet 2 everything
related to that row from sheet 1.

Can someone tell me what formula should I create?
--
Museum Restaurant Group

Formula
 

Thanks, But in what sheet should I imput the formula that you gave me?

Remember, all I want is one day's information in Sheet 2.

Example: Sheet 1, Cell A25= 1/25/06, B25= $256.00, C25=18.00, D25=6.58,
etc...

And if I go to Sheet 2 and imput 1/25/06 in cell A1, I want to have in cell
B1= $256.00, C1= 18.00, D1= 6.58, etc...

If I imput another day in cell A1, Then I want that day's info on the other
cells in row 1. which replaces the information of the day before.

How can I do this with VLOOKUP?
--
Museum Restaurant Group


"kassie" wrote:

In coloumn B, insert the following formula, then copy it across to col AV.
The ",2" must change for every column to the right, ie C=3, D=4 etc
=IF($A1="","",VLOOKUP($A1,Sheet1!A1:AV365,2,FALSE) )

"MRG Perez" wrote:

In a WorkSheet, I have Sheet 1= 365 rows with column a = date 1/1/06 thru
12/31/06 and in columns b thru av infomation about that date.

In Sheet 2 I would like to insert a date in cell 1 that would equal one of
the dates that are in sheet 1 and have excel copy to sheet 2 everything
related to that row from sheet 1.

Can someone tell me what formula should I create?
--
Museum Restaurant Group

Formula
 

You insert these formulae in Sheet 2, where you want the results to show.
You start in B1, and then copy across to AV1. When you enter a date in A1,
you will see the info for that date in B1:AV1. Btw, I made a mistake with
the formula, it should actually read
=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$AV$365,2,FA LSE)). Also remember, once
you have copied across to AV1, you will have to adjust the offset numbers
(the 2 just before FALSE). It has to increment by 1 for each cell you go
right. Iow, in C1 it should read 3, in D1 4 etc

"MRG Perez" wrote:

Thanks, But in what sheet should I imput the formula that you gave me?

Remember, all I want is one day's information in Sheet 2.

Example: Sheet 1, Cell A25= 1/25/06, B25= $256.00, C25=18.00, D25=6.58,
etc...

And if I go to Sheet 2 and imput 1/25/06 in cell A1, I want to have in cell
B1= $256.00, C1= 18.00, D1= 6.58, etc...

If I imput another day in cell A1, Then I want that day's info on the other
cells in row 1. which replaces the information of the day before.

How can I do this with VLOOKUP?
--
Museum Restaurant Group


"kassie" wrote:

In coloumn B, insert the following formula, then copy it across to col AV.
The ",2" must change for every column to the right, ie C=3, D=4 etc
=IF($A1="","",VLOOKUP($A1,Sheet1!A1:AV365,2,FALSE) )

"MRG Perez" wrote:

In a WorkSheet, I have Sheet 1= 365 rows with column a = date 1/1/06 thru
12/31/06 and in columns b thru av infomation about that date.

In Sheet 2 I would like to insert a date in cell 1 that would equal one of
the dates that are in sheet 1 and have excel copy to sheet 2 everything
related to that row from sheet 1.

Can someone tell me what formula should I create?
--
Museum Restaurant Group

Formula
 

You can change the 2 that Kassie refers to to COLUMN() if the formula
is in column B to start with - that way you can copy across and it will
automatically adjust itself, i.e.:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$AV$365,COLU MN(),FALSE))

Hope this helps.

Pete

Formula
 

If you are going to use a function to increment the column number it is
better to use

=COLUMNS($A$1:B1)

than

=COLUMN()

why?

Put both formulas in column B and insert a new column in A and see what
happens to the

=COLUMN()

it will change to 3 and not return the same answer, to avoid that using
COLUMN you would need
something like

=COLUMN()-(COLUMN($B$2)-2)


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Pete_UK" wrote in message
ps.com...
You can change the 2 that Kassie refers to to COLUMN() if the formula
is in column B to start with - that way you can copy across and it will
automatically adjust itself, i.e.:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$AV$365,COLU MN(),FALSE))

Hope this helps.

Pete

Formula
 

Good point, Peo. I'll try to remember that.

Pete