Unexpected result from calculated column

[wstoffel1]

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?

 

[Vamplew]

Could you copy your table into your message, i.e ExcelGenie.

 

[wstoffel1]

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]

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!