I have developed a work order sheet for invoicing maintenance accomplished on
a fleet of aircraft. It consists of a series of 20 block of cells. Each block
of cells is one "item" detailing work accomplished, labor, parts, freight,
total item cost, etc. At the bottom of the sheet I have formulas set for work
order totals. So total labor is: =SUM(I9,I24,I39,I54,I69,I84, and so on until
I294). I have similar formulas for total parts, freight, etc. My problem is
that I want to delete the blocks of cells not used for a particular work
order. So if we used only 8 items, I want to delete item 9 thru 20. When I do
this, my totals formulas at the bottom of the sheet display an error message
because I deleted some of the cells in that formula. The cells I am totaling
are not next to each other so I cannot use a range of cells for the formula.
Is there a way to get around this? Many thanks in advance.

What if you used some sort of identifier in an adjacent cell to each of your
totals. Let's say column H contains the text "Invoice Total:" in each cell
adjacent to where your totals are in column I. You could then use:

=SUMPRODUCT(--(H1:H294="Invoice Total:"),I1:I294)

This would sum everything in column I where the adjacent cell in H contains
"Invoice Total:". That way it won't matter what gets added or deleted.

Or if Column H won't work, just use a blank column and insert an "X" or
something on each of your total lines.

HTH
Elkar


"Texhun" wrote:

I have developed a work order sheet for invoicing maintenance accomplished on
a fleet of aircraft. It consists of a series of 20 block of cells. Each block
of cells is one "item" detailing work accomplished, labor, parts, freight,
total item cost, etc. At the bottom of the sheet I have formulas set for work
order totals. So total labor is: =SUM(I9,I24,I39,I54,I69,I84, and so on until
I294). I have similar formulas for total parts, freight, etc. My problem is
that I want to delete the blocks of cells not used for a particular work
order. So if we used only 8 items, I want to delete item 9 thru 20. When I do
this, my totals formulas at the bottom of the sheet display an error message
because I deleted some of the cells in that formula. The cells I am totaling
are not next to each other so I cannot use a range of cells for the formula.
Is there a way to get around this? Many thanks in advance.

Instead of having your formula pick 'random' cells, is there perhaps
something in each of those particular rows that you could use as a flag?
Perhaps the world total in column H? Then you could just use:
=SUMIF(H9:H294,"Total",I9:I294)
This also makes for a much smaller, manageable formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Texhun" wrote:

I have developed a work order sheet for invoicing maintenance accomplished on
a fleet of aircraft. It consists of a series of 20 block of cells. Each block
of cells is one "item" detailing work accomplished, labor, parts, freight,
total item cost, etc. At the bottom of the sheet I have formulas set for work
order totals. So total labor is: =SUM(I9,I24,I39,I54,I69,I84, and so on until
I294). I have similar formulas for total parts, freight, etc. My problem is
that I want to delete the blocks of cells not used for a particular work
order. So if we used only 8 items, I want to delete item 9 thru 20. When I do
this, my totals formulas at the bottom of the sheet display an error message
because I deleted some of the cells in that formula. The cells I am totaling
are not next to each other so I cannot use a range of cells for the formula.
Is there a way to get around this? Many thanks in advance.

Yes I believe I can do that. I'll give it a try. Thank you so much for your
help!

"Elkar" wrote:

What if you used some sort of identifier in an adjacent cell to each of your
totals. Let's say column H contains the text "Invoice Total:" in each cell
adjacent to where your totals are in column I. You could then use:

=SUMPRODUCT(--(H1:H294="Invoice Total:"),I1:I294)

This would sum everything in column I where the adjacent cell in H contains
"Invoice Total:". That way it won't matter what gets added or deleted.

Or if Column H won't work, just use a blank column and insert an "X" or
something on each of your total lines.

HTH
Elkar


"Texhun" wrote:

I have developed a work order sheet for invoicing maintenance accomplished on
a fleet of aircraft. It consists of a series of 20 block of cells. Each block
of cells is one "item" detailing work accomplished, labor, parts, freight,
total item cost, etc. At the bottom of the sheet I have formulas set for work
order totals. So total labor is: =SUM(I9,I24,I39,I54,I69,I84, and so on until
I294). I have similar formulas for total parts, freight, etc. My problem is
that I want to delete the blocks of cells not used for a particular work
order. So if we used only 8 items, I want to delete item 9 thru 20. When I do
this, my totals formulas at the bottom of the sheet display an error message
because I deleted some of the cells in that formula. The cells I am totaling
are not next to each other so I cannot use a range of cells for the formula.
Is there a way to get around this? Many thanks in advance.

Yes, I believe I can revise my formula in that maner. I'll give it a try.
Thank you so much for your help!

"Luke M" wrote:

Instead of having your formula pick 'random' cells, is there perhaps
something in each of those particular rows that you could use as a flag?
Perhaps the world total in column H? Then you could just use:
=SUMIF(H9:H294,"Total",I9:I294)
This also makes for a much smaller, manageable formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Texhun" wrote:

I have developed a work order sheet for invoicing maintenance accomplished on
a fleet of aircraft. It consists of a series of 20 block of cells. Each block
of cells is one "item" detailing work accomplished, labor, parts, freight,
total item cost, etc. At the bottom of the sheet I have formulas set for work
order totals. So total labor is: =SUM(I9,I24,I39,I54,I69,I84, and so on until
I294). I have similar formulas for total parts, freight, etc. My problem is
that I want to delete the blocks of cells not used for a particular work
order. So if we used only 8 items, I want to delete item 9 thru 20. When I do
this, my totals formulas at the bottom of the sheet display an error message
because I deleted some of the cells in that formula. The cells I am totaling
are not next to each other so I cannot use a range of cells for the formula.
Is there a way to get around this? Many thanks in advance.