Auto-update Fill Series in column that has randomly spaced blank c

[Khind]

Hi,

I have a worksheet of projects listed by quarter. A cell (say A6) contains
QTR 1, followed by cells in column A filled with a series (1,2,3...say up to
12). Then comes a blank row followed by a row containing words "QTR 2" and
then the fill series continues from where it left under QTR 1 i.e.
13,14,15...say up to 18. And so on for QTR 3 and QTR 4.

When a new project is added, I want to be able to add a row and with minimal
steps, want the fill series below this added new project, to update.

When I delete a row (project), I want the fill series to automatically
update below the deleted project.

I have searched in various discussions and =A6+1 or =ROWS($1:1) solutions
that I saw do not work in the situation explained above.

I am working in Excel 2003 with Vista but if someone knows how to accomplish
this in 2003 and XP, I would greatly appreciate the help.

Thanks,
Khind

 

[Daryl S]

Khind -

This could be easier if the calculated cells were not in the same column as
the QTR 1, QTR 2, etc. cells. This is one solution that is pretty quick.

Put this formula in cell A7 (Assuming QTR 1 is in A6):

=IF(A6="QTR 1",1,IF(LEFT(A6,3)="QTR",A4+1,A6+1))

Then copy/paste it to all relevant cells in column A, skipping the cells
with QTR in them.

You only need to remove the contents of the last cells before QTR 2, QTR 3,
and QTR 4.

Going forward, when you add a row, just copy/paste the formula from one of
the cells in column A with this formula to the new rows plus the row below
any newly-added row. When you delete a row, copy the formula from one of the
cells above the deleted row to the row that used to be below the deleted row.
Note that all the rows below the deleted row will have #REF in it until you
paste into just the first cell with #REF, which is the one replacing the
deleted row.

 

[Khind]

Daryl,

Thanks for the tip. If I do not have QTR1, QTR2, etc. in this column then it
would leave 2 cells empty between each list of projects. I want to use the If
function to say that if the preceeding cell is empty, pick up the number from
2 cells above and add 1.

I do not know how to specify 'blank' in an IF function. For example my QTR 1
projects start in cell A13 and in cell A14 if I use
=IF(A13="",A11+1,A12+1)
then the formula returns "1" in cell A14. If I copy this to subsequent
cells, I get 2,2,3,3,4,4... The numbers appear twice.

In an IF function, how do I specify logical test to be an empty cell (or a
cell containing any value)?

Thanks.

 

[Daryl S]

Khind -

If QTR 1, QTR 2, etc. were in a different column, then it would be easy to
drag the appropriate formula down all cells.

You can drag or copy/paste to all cells ignoring the blank cell. The
formula I gave you already takes into account the blank cell above QTR 2, QTR
3, and QTR 4. If you copy it into all cells other than the QTR and blank
cells, it will count things correctly.

As for checking to see if cells are blank, there are a couple ways.
=ISBLANK(A6) will return TRUE if the cell is truly blank, but if there are
space characters in it, it will return FALSE, even though the cell looks
blank.

You can use something like =IF(LEN(TRIM(A7))=0,TRUE,FALSE) to return TRUE if
the cell is empty or has just spaces in it.