Unexpected result from calculated column

wstoffel1

New Member
Joined
Apr 6, 2007
Messages
11
I thought the calculated column in a table was the answer to my problems. I have a start and end data in A1 and A2. I build my table in A4 to H4, column A has this formula in it:

=IF(WORKDAY(A$1-1,ROWS(A$4:A4))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A4)))

but when I tab through the table to create a new row, I'm expecting the date to increase in column A based on the range of A1-A2 and it does not.

Example, from A4 to A8 I get the following changes to the formula:

=IF(WORKDAY(A$1-1,ROWS(A$4:A4))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A4)))
=IF(WORKDAY(A$1-1,ROWS(A$4:A8))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A8)))
=IF(WORKDAY(A$1-1,ROWS(A$4:A8))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A8)))
=IF(WORKDAY(A$1-1,ROWS(A$4:A8))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A8)))
=IF(WORKDAY(A$1-1,ROWS(A$4:A8))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A8)))

Any thoughts?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

wstoffel1

New Member
Joined
Apr 6, 2007
Messages
11
Not so sure I follow. 12/1 to 12/23 is my range in A1 and A2. Column1 (A) would be the list of dates, the start date calculates, but every time a new row is created...

Thursday, December 01, 2011
Saturday, December 23, 2011
Column1 Column2 Column3 Column4 Column5 Column6
Thursday, December 01, 2011
Thursday, December 08, 2011
Thursday, December 08, 2011
Thursday, December 08, 2011
Thursday, December 08, 2011
Thursday, December 08, 2011
 

wstoffel1

New Member
Joined
Apr 6, 2007
Messages
11
I'm not even so sure I'm going about this problem the right way, I'm just assuming I need a table. I do need a total row, and the table gives me one. But i'm all ears if there's a better option.

So now what I've done, i started changing the sort options in my date column, specifically unchecking the Blanks option, so the table doesn't show the blank rows. Which gives me the end result I'm looking for, sort of.

I create a table that's 200 rows, with a total row, the first column is my date column with =IF(WORKDAY(A$1-1,ROWS(A$4:A4))>A$2,"",WORKDAY(A$1-1,ROWS(A$4:A4))) copied all the way down.

I enter my start date and end date, 12/1/2011 - 12/5/2011 and I get 3 dates listed, Thur 12/1, Fri 12/2 and Mon 12/5. All is right with the world, except I have 197 blank lines....

So I sort the date column to not include blank lines and I get a nice 3 line table, which is what i want...

Then if I change the end date to 1/15/2012, nothing happens. I have to click the sort button again, and just click OK since Blanks is not checked. It's the only way to refresh the table, and now my table goes from 12/1 to 1/15/2012, which is 32 rows long.

Sorry to be long winded on this, but the end result is exactly what I'm looking for...I'm just looking for a way to do this without clicking the sort button.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top