Formula to add one to the previous column, with one exception

[scheidc]

DA6.jpg

The pictures shows the general layout of what I want. I entered these numbers manually but I'm looking for a formula to do it.

Each column simply adds one to the previous column, with one exception. There are different set of numbers for work days and non-work days. So if my number on Monday is 5, Tuesday will be 6, Wednesday will be 7...Friday would be 9. But when you get to the weekend (or Holiday) my "non-workday" number may be 5, which would make Sunday 6, but then Monday would be 10 because it's going off the 9 from Friday. The next non-workday would be 7 going off the 6 from last Sunday.

I want one formula that I can drag across the row that will be able to add one to the previous day respectively (work day or non-work day).

This is used to assign duty. The person with the highest number gets assigned the duty that day if there are any. On a day that duty is assigned, a "0" is entered, restarting that persons number. The next same type day would be a 1 then.

Thanks.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[scheidc]

I tried to upload the sheet the first time and it didn't work which is why I did the picture. See if this works.

So there are about 50 rows of names and the columns can go forever. That being said we normally plan about 90 days out.

[Glenn Kennedy]

Apols. I didn't notice your reply. Anyhow. I had to add two "pump priming" columns... to reflect the position IMMEDIATELY before the start date. I tidied up your CF a bit, so that zeros and weekends are CF'd the way you want them (copy/pasting across screwed up your W/E shading).

Here's the formula that you wanted, in D3, copied across and down:

=IF($A3="","",IF(D$1="","",IF(D$2="W",LOOKUP(2,1/($B$2:C$2="W"),$B3:C3)+1,LOOKUP(2,1/($B$2:C$2=""),$B3:C3)+1)))

basically if the day is a W it looks for the last number corresponding to a W and adds one to it. Same for non-W. So, when you manually enter a zero the numbering system starts over.

The salmon coloured shading (which is rather ugly) was applied manually just to show you the raneg currently covered by the formula and the CF.

Don't forget that if it gets too wide and you want to reset the sheet, you'll need a new pair of pump priming columns AND you will have to copy/paste the formula again as you will ahve over-written some of the cells with a zero.

[scheidc]

That works great. Thanks.

How about a Conditional Formatting that highlight the highest number in each column as well? Without having to set a new "Top/Bottom" rule for each column.

[Glenn Kennedy]

There's a problem with that. Yes, it can be done... but. Once you overwrite the max number with a zero, then another cell will become the maximum and will highlight. That sounds MESSY to me.

[63falcondude]

I'm looking at the workbook from post #4.

Highlight D3:AZ20 (or however far you want to go) > Conditional Formatting > New Rule > Use a formula
=D3=MAX($D3:$AZ3)
Format: Fill color of your choice > OK > OK

[scheidc]

I understand. Shortly after the max is identified and replaced with a zero, that column will be hidden. This just gives us the data needed to fill out a roster and then we hide it. Thanks again.

[scheidc]

That only highlighted numbers in the last row with data (AJ). I need the highest number in each column to be highlighted. Thanks.

[scheidc]

Didn't mean to post this additional replay and can't figure out how to delete it.

[Glenn Kennedy]

Now hideable row 2 is blank until a date is entered. Then the MAX in the coluumn is displaced UNLESS there is a 0 in the column, in which case it's blank (see AJ2)....

[Glenn Kennedy]

Pooh. I've just seen Post 9, but will wait for your reaction before firing off again.

[63falcondude]

My mistake, I thought you said the highest number in each row.

Change the formula from post #7 to this:

=D3=MAX(D$3:D$20)

[scheidc]

The formula from post #13 doesn't seem to be working either.

[Glenn Kennedy]

But what about the one in Post 11??? i was waiting to hear from you.

[63falcondude]

If you follow the instructions from post #7 using the formula in post #13, the largest number in each column will be highlighted.