Serial Number generator by Day, two week cycle, pattern driven?

Well that's a fun thread title. I have a feeling this is fairly simple to do, I just can't think of the solution

The below explanation is in the sample file, it will make sense I promise!

-

Entering a date into cell E4 will VLOOKUP that date in A:C, and return the Day # and Week #.

Leaving E4 blank, will VLOOKUP the same information for =TODAY()'s date.

Any date needs to be able to be entered into E4, and an accurate Serial number based on the pattern in A:C must be returned.

What I would like to do is have the above table (E3:H4) function as it currently does, but without the need for the table in A:C. The table is updated manually every couple of months, and with manual entry comes error.

The pattern is fixed, Week and Day is separated on purpose and needs to remain as such.

Thanks!


twoweekswitch.xlsx

In F4:

Formula:

Please Login or Register  to view this content.

In G4:

Formula:

Please Login or Register  to view this content.

You don't need those cells though - you can determine the serial directly, in H4, by combining those two:

Formula:

Please Login or Register  to view this content.

Perfect! thanks.

I could combine them but I need them separate, about 40,000 formulas across 3 tabs and a couple macros reference these cells and I don't feel like remembering which ones to change