Calk - lookup cell in column

users
#1

Hi

Now this might take a bit of explaining

I have a spreadsheet that records data for each day of the year
Vertically row by row I have the days of the year
I like my week to start on a Monday
So I have my year/days set to start on the first Monday of the year
So the actual first day of the year position/row is dynamic either on the
Monday or above
This all works ok

However I then (for manipulating data by month) need to know the row/cell in
which the first day of the year (or month) appears
How do I look up/find within a column the cell in which the first day of the
month is - I assume that if I know how to do it for say January then I can
do it for any month.
I'm totally clueless as to how to do this

/Hopping that my explanation isn't to bamboozling/

Any ideas - please

#2

Calk - lookup cell in column

I imagine it's your spelling checker that thinks Calc is called Calk?

I have a spreadsheet that records data for each day of the year. Vertically row by row I have the days of the year. I like my week to start on a Monday. So I have my year/days set to start on the first Monday of the year. So the actual first day of the year position/row is dynamic either on the Monday or above.

Er, can you run that last sentence past me again, please?!

This all works ok

Nice to hear it!

However I then (for manipulating data by month) need to know the row/cell in which the first day of the year (or month) appears. How do I look up/find within a column the cell in which the first day of the month is - I assume that if I know how to do it for say January then I can do it for any month. I'm totally clueless as to how to do this.

I fear so shall any one else be! One likely possibility is to use VLOOKUP(), which enables you to look for a match in the date column(s) and then retrieve data from the corresponding cells in neighbouring columns. But exactly how you do this depends on the form of your date data. Do you have a single column of proper dates (however formatted)? Or by "days of the year" do you mean that you have an integer from 1 to 365 or 366? Or do you have this data spread between more than one column - perhaps with the month in one column and the day in another? Or what?

It might help if you list the date data for the first few days of the current year. This might explain both conundrums.

/Hopping that my explanation isn't to bamboozling/

Gymnastics quite unnecessary, in fact.

I trust this helps.

Brian Barker

#3

Calc not Calk - dam that spell checker - - can we agree to blame the spell
checker

vlookup thanks for pointing me at that does exactly what i wanted - though
it did kill a few grey cells getting it to work

now i can happily move on to the next problem

#4

The closest I came to a solution was a scenario where all the dates for the year were in one column.

I put the dates for the year (Monday 12/31/2012 thru Sunday 1/5/2014 in A5:A375.
Then, in A1 I put whatever date I'm looking for.
Off to the side (D1) I entered: =VLOOKUP($A$1,$A$5:$A$375,1,0)-(A5-1)+4
      VLOOKUP(A1,$A$5:$A$375,1,1) finds the date you're looking for.
      -(A5-1) subtracts the day before the first date on the list FROM the date found.
      +4 adds in the four rows above the range.
The result is the row number of the date you're looking for.

I hope this helps.

-- Tim

#5

A simpler formula: =MATCH($a$1,$a$5:$a$375,0)+row($a$5)-1

MATCH returns the offset into the array (so if $a$1 = $a$5, MATCH would return 1), and row($a$5)-1 adjusts it to give the absolute row.

The reason I do it this way is that if you name the array (e.g. name it YearRange), it works as follows:

=MATCH($a$1,YearRange,0)+row(YearRange)-1

So you don't need to know what row your array starts on to write the formula.

Jim