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

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

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

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

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

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

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

Pete