Trying to save time on updating V-Lookup

[Olly9]

Hi,

Bit of a difficult one to explain, but I have a V-Lookup on a staff planning sheet that looks to see if a person has worked or hasn't work on a Saturday. If true it puts a tick, if false it puts a cross.

Currently this lookup looks like this:

=IF(VLOOKUP($B:$B,January!$B:$AH,6,FALSE)="08:30 - 14:30","ü",IF(VLOOKUP($B:$B,January!$B:$AH,6,FALSE)="08:30 - 15:00","ü",IF(VLOOKUP($B:$B,January!$B:$AH,6,FALSE)="09:00 - 15:00","ü","û")))

The different times are just the shift patterns people work on a Saturday - I'm aware this could be shortened but I have to be specific with this as I don't want it to count things like holiday or days off if the cell is filled with that.

This then proceeds across the month and across the year. I have different months on different sheets so the same v-lookup in February looks like this:

=IF(VLOOKUP($B:$B,February!$B:$AH,32,FALSE)="08:30 - 14:30","ü",IF(VLOOKUP($B:$B,February!$B:$AH,32,FALSE)="08:30 - 15:00","ü",IF(VLOOKUP($B:$B,February!$B:$AH,32,FALSE)="09:00 - 15:00","ü","û")))

and so on an so forth.

The issue I'm trying to solve is a way to automate updating the formula each year without having to manually do it as days move each year.

So for example, say the first Sat in Jan 2020 falls in col_index_num 6, on my sheet then next one would be column 13 (+7) etc etc. However in 2021 the first Saturday might fall in col_index_num 8, the next being 15....hopefully you get the idea!

At the moment every time I am doing a new year I have to manually go in and work out the column number for each Saturday, and change it...for all 52. There are some short cuts I make where I only really have to change the first row then I can drag the formula to the rest, but it's still 52 individual changes each year, and that's not counting that I have to do this 3 times per row as there are 3 different shifts!

What I'd like if it's possible is a way to rewrite the v-lookup so that once I've done the first it will add 7 to the next column, then 7 to that column - then I would only have to change 12. I know you can do +7 in a count formula but I'm unsure how to embed that into the column index on a v-lookup, but then I'm also unsure whether it would have to be +7, +14 +21 if it was only referencing the first row?

Hah! Hope some of that makes sense to someone!

[AliGW]

You can always use a MATCH function to identify the column. With uniform column names, this should make it easier for you.

Post a sample workbook if you need help to achieve this.

[Pete_UK]

It's hard to be exact when we don't have the sheet in front of us to try things out before posting back to you, and I'm not sure about the $B:$B as the lookup value at the beginning of the VLOOKUPs, but if you only want the 6 to become 13, then 20 etc. as you copy the formula across in consecutive cells, then you can change that 6 to:

(COLUMNS($A:A)-1)*7 + 6

The column references don't mean very much here - they just return 1 from the COLUMNS term, which then has 1 subtracted from it (leaving zero) and this is multiplied by 7, so zero is added to the 6 in the first column where the formula resides (and, of course, the 6 could be replaced by an absolute reference to another cell which contains 6, which may be replaced by 8 in the following year, as per your description). Anyway, when the formula is copied across to another column, the bit that I gave you above will change to (COLUMNS($A:B)-1)*7 + 6 then (COLUMNS($A:C)-1)*7 + 6 and so on in consecutive columns, which will return the values 13 then 20.

Hope this helps.

Pete

[menem]

I've try to create this file to verify your requirements.
List of concepts in my sample file.
(And apologize in advance for misunderstood if any.)

1. Use INDIRECT to locate for 'Monthly' sheets.
2. Use Sumproduct() to count as array of Name & TimeShift


Base on.
1. Employee in each month are not the same.
2. n'th Saturday auto location in each month.
3. Some month may not presented yet.


By specify year at Summary!B3
Summary!C4 will find 1st Saturday of the year.

Please Login or Register  to view this content.

Summary!D4 and so on just +7

Summary!C5 calculate for Month text (for refer to Sheet Name)

Please Login or Register  to view this content.

Summary!B5 check for column number of name field from JANUARY sheet.

Please Login or Register  to view this content.

And convert it to Column name in Summary!B6

Please Login or Register  to view this content.

Summary!C6 find column name of each Saturday in each month

Please Login or Register  to view this content.

Summary!C15 check each Saturday in each month work time

Please Login or Register  to view this content.

Regards.