Work Schedule Formula

[avixion]

I am working on a work schedule and wondering if there's a way to shorten this formula. There are different shifts assigned to different letters and by changing the letter it changes the shifts time for each team member.

=IF(J14="E",BN14,IF(J14="M",BO14,IF(J14="L",BQ14,IF(J14="O",CJ14,IF(J14="V",CK14,IF(J14="D",BM14,IF(J14="A",BN14,IF(J14="Q",BR14,IF(J14="X",CP14,IF(J14="W",BP14,IF(J14="Y",BN14,IF(J14="F",BS14,IF(J14="B",BT14,IF(J14="K",BU14,IF(J14="S",BX14,IF(J14="T",BY14)

This is how it looks:

Capture3.gif

Thank you!
Avixion

[HansV]

It's difficult to see what you're doing since the formula refers to cells such as BN14, BO14 etc., but those are all hidden in the screenshot.
Could you attach a copy of the worksheet without sensitive information?

[avixion]

I am getting an error message trying to attach the file, that file size of too big (file size is 453 KB).

Thanks,
Avixion

[avixion]

The reasons I am trying to shorten the formula in the hope to reduce down file size, also Excel takes a long time to process any change I make to the file probably due to lengthy formulas, not sure.

Avixion

[HansV]

If you cannot cut down the size of the workbook to less than 256 KB, you can upload it to Dropbox, Google Drive, OneDrive or similar, obtain a link to the uploaded file and post that in a reply.

[ChrisGreaves]

The reasons I am trying to shorten the formula in the hope to reduce down file size, also Excel takes a long time to process any change I make to the file probably due to lengthy formulas, not sure.

Hello Avixion, I have found that Excel2003 takes a long time to save a workbook (compared to Word2003 saving a larger document file), and thought that it might be due to the number of fragments written to disk.
The consensus seems to be that fragmentation is a function of the Operating system (Win10 in my case), but I maintain that if Excel2003 issues 112 requests to save a chunk of a workbook, that is, if a Workbook is split into 112 separate logical writes, then the operating system can satisfy that as 112 requests for 112 areas of storage.

That said, I think that a formula such as "=IF(J14="E",BN14,IF(J14="M",BO14,IF(J14="L",BQ14,IF(J14="O",CJ14,IF(J14="V",CK14,IF(J14="D",BM14,IF(J14="A",BN14,IF(J14="Q",BR14,IF(J14="X",CP14,IF(J14="W",BP14,IF(J14="Y",BN14,IF(J14="F",BS14,IF(J14="B",BT14,IF(J14="K",BU14,IF(J14="S",BX14,IF(J14="T",BY14)" cries out for simplification, if only by writing a well-documented user function

Cheers
Chris

[avixion]

Thank you both Han and Chris for looking in to this, I was able to reduce the file size to attach here for you to review by deleting all other worksheet tabs and changing it from Macro-Enabled file to normal Excel file. Normally this file has at least 12 tabs for each month and the reason I keep it as Macro-Enabled to run a macro to automatically password protect the file to preserve accidental formula deletion by other users.

The problem I am having with this file is every time I change shift for each employee by changing a letter assigned to that shift, Excel freezes up and takes about 8 to 10 seconds to processes the changes which is very time consuming when I am working on a whole month. So trying to understand if it's lengthy formula or number of fragments.

Thank you,
Avixion

[HansV]

Thanks.

Columns BM to CO contain the same values in all the non-empty rows.
Will this always be the case, or could those values vary from row to row?

[avixion]

Those value stays the same, I have them copies in all rows to keep the formulas straight copy and paste.

Thanks,
Avixion

[HansV]

The formulas are not consistent. The same letter can have a different meaning for Monday - Friday and for Saturday. That will make it very difficult to simplify them...

[p45cal]

You could test something like:

Code: Select all

=CHOOSE(MATCH(J14,{"D","A","Y","M","W","L","Q","F","B","K","S","T","O","V","X"},0),BM14,BN14,BN14,BO14,BP14,BQ14,BR14,BS14,BT14,BU14,BX14,BY14,CJ14,CK14,CP14)

[avixion]

Thank you,

avixion