software

I have data that is entered into each column by different people. Each
column is totaled. Below the column total is a running total with each
column added to the last. The formula used for that column is:
=IF(AND(E18,F17),E18 F17,quot;quot;). What I need is that if a column or even two is
not filled in I need the total, basically skipping the empty columns. Is
this possible?
Thanks for any help offered!!!

Mike Rogers


You could use Conditional Format on the column totals, such that if the
count of this column items = zero then the text is white-on-white (ie,
hidden)

This will accomodate the entries in the column totaling zero, and will
allow the appearance of totals for only the columns with entries.

HTHMike Rogers Wrote:
gt; I have data that is entered into each column by different people. Each
gt; column is totaled. Below the column total is a running total with
gt; each
gt; column added to the last. The formula used for that column is:
gt; =IF(AND(E18,F17),E18 F17,quot;quot;). What I need is that if a column or even
gt; two is
gt; not filled in I need the total, basically skipping the empty columns.
gt; Is
gt; this possible?
gt; Thanks for any help offered!!!
gt;
gt; Mike Rogers--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531714Thanks for the reply Bryan but that does not help with the running total that
is carried across the bottom of all the columns. Right now if I skip a
column the rest of the columns have the ole quot;#VALUE!quot; error. I am trying to
keep a running total across the columns even if I skip one (or two).

Mike Rogers

quot;Bryan Hesseyquot; wrote:

gt;
gt; You could use Conditional Format on the column totals, such that if the
gt; count of this column items = zero then the text is white-on-white (ie,
gt; hidden)
gt;
gt; This will accomodate the entries in the column totaling zero, and will
gt; allow the appearance of totals for only the columns with entries.
gt;
gt; HTH
gt;
gt;
gt; Mike Rogers Wrote:
gt; gt; I have data that is entered into each column by different people. Each
gt; gt; column is totaled. Below the column total is a running total with
gt; gt; each
gt; gt; column added to the last. The formula used for that column is:
gt; gt; =IF(AND(E18,F17),E18 F17,quot;quot;). What I need is that if a column or even
gt; gt; two is
gt; gt; not filled in I need the total, basically skipping the empty columns.
gt; gt; Is
gt; gt; this possible?
gt; gt; Thanks for any help offered!!!
gt; gt;
gt; gt; Mike Rogers
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=531714
gt;
gt;


Mike,

Keep the totals going across the blank columns and, if possible, use
=Sum(B1:B98) A99 etc, which ignores blanks.

BryanMike Rogers Wrote:
gt; Thanks for the reply Bryan but that does not help with the running total
gt; that
gt; is carried across the bottom of all the columns. Right now if I skip
gt; a
gt; column the rest of the columns have the ole quot;#VALUE!quot; error. I am
gt; trying to
gt; keep a running total across the columns even if I skip one (or two).
gt;
gt; Mike Rogers
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; You could use Conditional Format on the column totals, such that if
gt; the
gt; gt; count of this column items = zero then the text is white-on-white
gt; (ie,
gt; gt; hidden)
gt; gt;
gt; gt; This will accomodate the entries in the column totaling zero, and
gt; will
gt; gt; allow the appearance of totals for only the columns with entries.
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt;
gt; gt; Mike Rogers Wrote:
gt; gt; gt; I have data that is entered into each column by different people.
gt; Each
gt; gt; gt; column is totaled. Below the column total is a running total with
gt; gt; gt; each
gt; gt; gt; column added to the last. The formula used for that column is:
gt; gt; gt; =IF(AND(E18,F17),E18 F17,quot;quot;). What I need is that if a column or
gt; even
gt; gt; gt; two is
gt; gt; gt; not filled in I need the total, basically skipping the empty
gt; columns.
gt; gt; gt; Is
gt; gt; gt; this possible?
gt; gt; gt; Thanks for any help offered!!!
gt; gt; gt;
gt; gt; gt; Mike Rogers
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=531714
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531714
Hi Mike,

I assume your problem is that when you hit a blank column, you get an
error in your running total. If so, try changing your formula to
=IF(AND(E18,F17),E18 F17,0). Zeros can be made invisible by selecting
tools-options-view and unchecking the zero values box.
[If you also additionally want to hide the totals in empty columns, you
can use conditional formatting to achieve this, but I don't read this as
your requirement]--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531714Bryan,
Thanks for the post back, that works almost, but now all the way across the
row is the same total as the last column with data because it ignors blanks,
now if I go to an quot;IF(AND)quot; function to only have totals to the last column
with data it also ignors the blank columns. I think I am going in circles.
And it sounds like I can't have it both ways. Any other ideas?

Mike Rogers

quot;Bryan Hesseyquot; wrote:

gt;
gt; Mike,
gt;
gt; Keep the totals going across the blank columns and, if possible, use
gt; =Sum(B1:B98) A99 etc, which ignores blanks.
gt;
gt; Bryan
gt;
gt;
gt; Mike Rogers Wrote:
gt; gt; Thanks for the reply Bryan but that does not help with the running total
gt; gt; that
gt; gt; is carried across the bottom of all the columns. Right now if I skip
gt; gt; a
gt; gt; column the rest of the columns have the ole quot;#VALUE!quot; error. I am
gt; gt; trying to
gt; gt; keep a running total across the columns even if I skip one (or two).
gt; gt;
gt; gt; Mike Rogers
gt; gt;
gt; gt; quot;Bryan Hesseyquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; You could use Conditional Format on the column totals, such that if
gt; gt; the
gt; gt; gt; count of this column items = zero then the text is white-on-white
gt; gt; (ie,
gt; gt; gt; hidden)
gt; gt; gt;
gt; gt; gt; This will accomodate the entries in the column totaling zero, and
gt; gt; will
gt; gt; gt; allow the appearance of totals for only the columns with entries.
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Mike Rogers Wrote:
gt; gt; gt; gt; I have data that is entered into each column by different people.
gt; gt; Each
gt; gt; gt; gt; column is totaled. Below the column total is a running total with
gt; gt; gt; gt; each
gt; gt; gt; gt; column added to the last. The formula used for that column is:
gt; gt; gt; gt; =IF(AND(E18,F17),E18 F17,quot;quot;). What I need is that if a column or
gt; gt; even
gt; gt; gt; gt; two is
gt; gt; gt; gt; not filled in I need the total, basically skipping the empty
gt; gt; columns.
gt; gt; gt; gt; Is
gt; gt; gt; gt; this possible?
gt; gt; gt; gt; Thanks for any help offered!!!
gt; gt; gt; gt;
gt; gt; gt; gt; Mike Rogers
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Bryan Hessey
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; Bryan Hessey's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=531714
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=531714
gt;
gt;

John,

Thanks for the post back, that still does not skip empty columns. I am
beginning to think there is not small solution for this. Any other ideas
would be appreciated!!

Is there a way of doing this with a nested quot;IFquot; statement that would look
back and see if it is a zero or not?

Mike Rogers

quot;John Jamesquot; wrote:

gt;
gt; Hi Mike,
gt;
gt; I assume your problem is that when you hit a blank column, you get an
gt; error in your running total. If so, try changing your formula to
gt; =IF(AND(E18,F17),E18 F17,0). Zeros can be made invisible by selecting
gt; tools-options-view and unchecking the zero values box.
gt; [If you also additionally want to hide the totals in empty columns, you
gt; can use conditional formatting to achieve this, but I don't read this as
gt; your requirement]
gt;
gt;
gt; --
gt; John James
gt; ------------------------------------------------------------------------
gt; John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
gt; View this thread: www.excelforum.com/showthread...hreadid=531714
gt;
gt;


Mike,

If you use =Sum to do your totals you will not get the #Value errors
for blanks.

Then put the totals across all columns, and hide the columns that you
do not want to see, ie, where count of items = zero.

If your column total is in row 17, then in B17 somthing like
=Sum(B1:B16), and in B18 =Sum(A18 B17).

Then use the conditional format as shown to hide unused columns, does
this not provide the result you seek?

--

Mike Rogers Wrote:
gt; John,
gt;
gt; Thanks for the post back, that still does not skip empty columns. I
gt; am
gt; beginning to think there is not small solution for this. Any other
gt; ideas
gt; would be appreciated!!
gt;
gt; Is there a way of doing this with a nested quot;IFquot; statement that would
gt; look
gt; back and see if it is a zero or not?
gt;
gt; Mike Rogers
gt;
gt; quot;John Jamesquot; wrote:
gt;
gt; gt;
gt; gt; Hi Mike,
gt; gt;
gt; gt; I assume your problem is that when you hit a blank column, you get
gt; an
gt; gt; error in your running total. If so, try changing your formula to
gt; gt; =IF(AND(E18,F17),E18 F17,0). Zeros can be made invisible by
gt; selecting
gt; gt; tools-options-view and unchecking the zero values box.
gt; gt; [If you also additionally want to hide the totals in empty columns,
gt; you
gt; gt; can use conditional formatting to achieve this, but I don't read this
gt; as
gt; gt; your requirement]
gt; gt;
gt; gt;
gt; gt; --
gt; gt; John James
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; John James's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32690
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=531714
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531714
Mike,

OK. Now I understand what you mean (I think). Try this. In F18,
enter:
=IF(F17=quot;quot;,quot;quot;,SUM($A17:F17))

Then copy across the row.--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531714John, Bryan,
Guys you did great! I took a little of what you where both saying and made
it work. The Sum formula worked and does ignor blanks, but it also carries
the total of the last column totaled all the way to the right edge of my work
sheet, did not like that! So I referenced the total cell with conditional
formating and said if this cell is lt;.25 ((a reference number that will always
be less than anything in the column)) then formate the continious total with
white font. Picked it up with the format painter and finished the row out.
Then I grouped all 20 worksheets and made them all work right, so now thanks
to you guys I am back in the saddle again. Thanks again for the help.

Mike Rogers

quot;John Jamesquot; wrote:

gt;
gt; Mike,
gt;
gt; OK. Now I understand what you mean (I think). Try this. In F18,
gt; enter:
gt; =IF(F17=quot;quot;,quot;quot;,SUM($A17:F17))
gt;
gt; Then copy across the row.
gt;
gt;
gt; --
gt; John James
gt; ------------------------------------------------------------------------
gt; John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
gt; View this thread: www.excelforum.com/showthread...hreadid=531714
gt;
gt;