Need to reorganize data on a separate sheet possibly using vlookup

[Asa_johannesen]

Hi all you clever Excel people

I always seem to have problems setting up anything more complicated than a
simple calculation, so here I go again.

I have one sheet where chick weights are entered according to date (Column A
has dates starting with the date of the first hatch and the following columns
have weights from chick 1 to, say, 100). The chicks hatched on different
dates, so on the first row I have the hatch date, and data are entered from
the first weighing (i.e. lots of columns do not have data in the first few
rows).

Now what I want is a separate sheet, where the data show up according to
chick age. So I want the first column to be age (ranging from day 0, that is
day of hatching, to day 100) and the rest to be weights.

I thought about setting up an IF formula with the dates being used to
calculate the age of the chick and finding the right value (as dictated by
column A in the second sheet) possibly combining with VLOOKUP, but I really
don't know what I'm doing, so it's not really working at all.

Anyone have an idea how I can do this? The idea is that it will be easy to
enter the data in the sheet with a separate date for each row, and easy to
analyse with a separate age of chick for each row on the other sheet.

Thank you, and sorry if I'm asking something really easy that I should be
able to figure out myself. Excel makes me feel stupid sometimes.

 

[Spiky]

Hi all you clever Excel people

I always seem to have problems setting up anything more complicated than a
simple calculation, so here I go again.

I have one sheet where chick weights are entered according to date (Column A
has dates starting with the date of the first hatch and the following columns
have weights from chick 1 to, say, 100). The chicks hatched on different
dates, so on the first row I have the hatch date, and data are entered from
the first weighing (i.e. lots of columns do not have data in the first few
rows).

Now what I want is a separate sheet, where the data show up according to
chick age. So I want the first column to be age (ranging from day 0, that is
day of hatching, to day 100) and the rest to be weights.

I thought about setting up an IF formula with the dates being used to
calculate the age of the chick and finding the right value (as dictated by
column A in the second sheet) possibly combining with VLOOKUP, but I really
don't know what I'm doing, so it's not really working at all.

Anyone have an idea how I can do this? The idea is that it will be easy to
enter the data in the sheet with a separate date for each row, and easy to
analyse with a separate age of chick for each row on the other sheet.

Thank you, and sorry if I'm asking something really easy that I should be
able to figure out myself. Excel makes me feel stupid sometimes.

I can't quite understand.
Do you mean you want the columns and rows to look exactly the same
except showing "12 days old" instead of "May 4, 2008" in the first
column?

 

[Asa_johannesen]

"Spiky" skrev:

I can't quite understand.
Do you mean you want the columns and rows to look exactly the same
except showing "12 days old" instead of "May 4, 2008" in the first
column?

Well, no. Not quite. It will be exactly the same data, but instead of me
manually moving the data in all the columns to coincide age wise rather than
date wise, the sheet does it for me. This will be something that we'll do
every year with increasing numbers of chicks, and it will get very laborious,
if we have to first check what age the chick was at first weighing (this is
not the same for every chick) and then move the data accordingly in order to
fit into the age sheet.

Say Chick 1, 2 and 3 hatched on the 5, 7 and 10th of May. We started
weighing on the 6th, so that will be the first row on the date sheet.
However, chick 2 and 3 won't have any data for the first two to five rows. I
stop weighing on the 20th of June and I want to compare how much the chicks
weighed at 30 days of age. Imagine me doing this with a hundred chicks
hatched on a wide range of dates, not all weighed for the first time on day
1. I could manually move the data in the columns so that the age was the same
on each row for all chicks, but I'd prefer to have some sort of formula do it
for me. Mainly because, as I said, it's laborious and will be repeated every
year.

Does that make sense at all? Am I trying to do this in a really silly round
about way?

Thanks for the reply

 

[Spiky]

"Spiky" skrev:





Well, no. Not quite. It will be exactly the same data, but instead of me
manually moving the data in all the columns to coincide age wise rather than
date wise, the sheet does it for me. This will be something that we'll do
every year with increasing numbers of chicks, and it will get very laborious,
if we have to first check what age the chick was at first weighing (this is
not the same for every chick) and then move the data accordingly in order to
fit into the age sheet.

Say Chick 1, 2 and 3 hatched on the 5, 7 and 10th of May. We started
weighing on the 6th, so that will be the first row on the date sheet.
However, chick 2 and 3 won't have any data for the first two to five rows. I
stop weighing on the 20th of June and I want to compare how much the chicks
weighed at 30 days of age. Imagine me doing this with a hundred chicks
hatched on a wide range of dates, not all weighed for the first time on day
1. I could manually move the data in the columns so that the age was the same
on each row for all chicks, but I'd prefer to have some sort of formula do it
for me. Mainly because, as I said, it's laborious and will be repeated every
year.

Does that make sense at all? Am I trying to do this in a really silly round
about way?

Thanks for the reply

I think I got it, I just couldn't picture your data before. What about
this?
=VLOOKUP(B$2+30,$A$1:$F$100,B$1+1)

Details, hopefully I've understood:
Row 1 is the chick number. So B1 would be "1", C1 is "2", etc.
Row 2 is the hatch date.
Col A is the test date.
So, B3:F100 is the weight data, if any.

The formula calculates the date 30 days after hatch, then looks up
based on that date. Copy the formula for each chick/column. You have
to have the chicks numbered simply (1, 2, 3, 4, etc) and in order for
this to work. And all the dates must be entered correctly, or it won't
find them.

It would take some tweaking to be on a different sheet, like you want.