I’m having trouble doing something I suspect should be easier than I’m finding it and would love some help…

In the first tab I have range of 3 columns and 53 rows (A1:C53) – this data is just essentially just a lookup table where column 1 is a week number (1 through 53), column 2 is the week starting date and column 3 is the week ending date.. easy…

In a second tab I have a single column range that is 566 rows long (A1:A566) – these records relate to independent time sheet entries so there may be multiple rows with the same date

scene is set..

now what I want to do in the second tab is add a column that reports the week number for each individual row. For instance, if the date in tab 2 falls between the start and end date of week 16, then 16 is written to the cell.

any ideas??

It looks like you have stumped them all, diddly.

(I’m no use,I haven’t got a clue)

buffy said:

It looks like you have stumped them all, diddly.

(I’m no use,I haven’t got a clue)

Oi, give us a chance, only just arrived!

diddly-squat said:

I’m having trouble doing something I suspect should be easier than I’m finding it and would love some help…

In the first tab I have range of 3 columns and 53 rows (A1:C53) – this data is just essentially just a lookup table where column 1 is a week number (1 through 53), column 2 is the week starting date and column 3 is the week ending date.. easy…

In a second tab I have a single column range that is 566 rows long (A1:A566) – these records relate to independent time sheet entries so there may be multiple rows with the same date

scene is set..

now what I want to do in the second tab is add a column that reports the week number for each individual row. For instance, if the date in tab 2 falls between the start and end date of week 16, then 16 is written to the cell.

any ideas??

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

The Rev Dodgson said:

diddly-squat said:

I’m having trouble doing something I suspect should be easier than I’m finding it and would love some help…

In the first tab I have range of 3 columns and 53 rows (A1:C53) – this data is just essentially just a lookup table where column 1 is a week number (1 through 53), column 2 is the week starting date and column 3 is the week ending date.. easy…

In a second tab I have a single column range that is 566 rows long (A1:A566) – these records relate to independent time sheet entries so there may be multiple rows with the same date

scene is set..

now what I want to do in the second tab is add a column that reports the week number for each individual row. For instance, if the date in tab 2 falls between the start and end date of week 16, then 16 is written to the cell.

any ideas??

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

The Rev Dodgson said:

The Rev Dodgson said:

diddly-squat said:

I’m having trouble doing something I suspect should be easier than I’m finding it and would love some help…

In the first tab I have range of 3 columns and 53 rows (A1:C53) – this data is just essentially just a lookup table where column 1 is a week number (1 through 53), column 2 is the week starting date and column 3 is the week ending date.. easy…

In a second tab I have a single column range that is 566 rows long (A1:A566) – these records relate to independent time sheet entries so there may be multiple rows with the same date

scene is set..

now what I want to do in the second tab is add a column that reports the week number for each individual row. For instance, if the date in tab 2 falls between the start and end date of week 16, then 16 is written to the cell.

any ideas??

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

ok.. not sure that will work but give me a sec

The Rev Dodgson said:

The Rev Dodgson said:

diddly-squat said:

I’m having trouble doing something I suspect should be easier than I’m finding it and would love some help…

In the first tab I have range of 3 columns and 53 rows (A1:C53) – this data is just essentially just a lookup table where column 1 is a week number (1 through 53), column 2 is the week starting date and column 3 is the week ending date.. easy…

In a second tab I have a single column range that is 566 rows long (A1:A566) – these records relate to independent time sheet entries so there may be multiple rows with the same date

scene is set..

now what I want to do in the second tab is add a column that reports the week number for each individual row. For instance, if the date in tab 2 falls between the start and end date of week 16, then 16 is written to the cell.

any ideas??

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

no, I can’t see that working Rev.

I mean I guess you could do it in a series of successive steps to identify which date was greater then or less than a certain range, but that seems very long winded…

diddly-squat said:

The Rev Dodgson said:

The Rev Dodgson said:

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

ok.. not sure that will work but give me a sec

If the week numbers go from 1 to 53 that should work. If you want to return a number other than a sequence incrementing by 1 there are two options:
=INDEX”(Col A address, MATCH”(A1,Sheet1!$B$1:$B$53)”)”

or use the new XLookup function, if you have it.

diddly-squat said:

The Rev Dodgson said:

The Rev Dodgson said:

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

no, I can’t see that working Rev.

I mean I guess you could do it in a series of successive steps to identify which date was greater then or less than a certain range, but that seems very long winded…

I need it to be —> date IN, week number OUT.. ultimately Sheet2 is about 50 columns wide and I want to create a pivot table of the information inside but sum it up by week, not by date (which is essentialy each day)

diddly-squat said:

The Rev Dodgson said:

The Rev Dodgson said:

In Tab2 cell B1 enter:
=MATCH
then copy it down.

To get the format right for the Sheet1 address, select the range you want, then press F4 to enter the $s.

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

no, I can’t see that working Rev.

I mean I guess you could do it in a series of successive steps to identify which date was greater then or less than a certain range, but that seems very long winded…

It returns the row number before the first row with a date greater than the lookup date. Why would you need successive steps?

The Rev Dodgson said:

diddly-squat said:

The Rev Dodgson said:

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

ok.. not sure that will work but give me a sec

If the week numbers go from 1 to 53 that should work. If you want to return a number other than a sequence incrementing by 1 there are two options:
=INDEX”(Col A address, MATCH”(A1,Sheet1!$B$1:$B$53)”)”

or use the new XLookup function, if you have it.

I guess I could create a lookup table that was 365 rows where column 1 is the date and column 2 in the week number and just use a simple lookup

The Rev Dodgson said:

diddly-squat said:

The Rev Dodgson said:

Try again:
=MATCH”(A1,Sheet1!$B$1:$B$53)”
but without the quotes either end, and select the Sheet1 range, rather than typing in.

no, I can’t see that working Rev.

I mean I guess you could do it in a series of successive steps to identify which date was greater then or less than a certain range, but that seems very long winded…

It returns the row number before the first row with a date greater than the lookup date. Why would you need successive steps?

no your’re right.. well done.. thanks

diddly-squat said:

The Rev Dodgson said:

diddly-squat said:

ok.. not sure that will work but give me a sec

If the week numbers go from 1 to 53 that should work. If you want to return a number other than a sequence incrementing by 1 there are two options:
=INDEX”(Col A address, MATCH”(A1,Sheet1!$B$1:$B$53)”)”

or use the new XLookup function, if you have it.

I guess I could create a lookup table that was 365 rows where column 1 is the date and column 2 in the week number and just use a simple lookup

You don’t need 356 rows, and you don’t need the end dates. You just need the start day of each week, in ascending order.

Did you try it?

diddly-squat said:

The Rev Dodgson said:

diddly-squat said:

no, I can’t see that working Rev.

I mean I guess you could do it in a series of successive steps to identify which date was greater then or less than a certain range, but that seems very long winded…

It returns the row number before the first row with a date greater than the lookup date. Why would you need successive steps?

no your’re right.. well done.. thanks

OK, good :)

diddly-squat said:

The Rev Dodgson said:

diddly-squat said:

no, I can’t see that working Rev.

I mean I guess you could do it in a series of successive steps to identify which date was greater then or less than a certain range, but that seems very long winded…

It returns the row number before the first row with a date greater than the lookup date. Why would you need successive steps?

no your’re right.. well done.. thanks

No worries.