Formula for IF a total goes to 3 pages do not have on 2nd page
 

I am in the process (still) of making a Bill of Lading in Excel that
transfers data onto the Packing List (our program at work is horrible). I
have a total of 8 sheets. The 1st Tab (PL) feeds to Tabs 2-4 (BOL pages 1-3).
Then Tab 5 (PL-pg2) feeds to Tabs 6-8 (BOL pages 4-6). I did it this way
since I am not an expert on Excel (as I am sure there was a much easier way).
I can fit 18 line items on the Packing List however the Bill of Ladings can
only fit 6 line items (which is why there are 3 pages for the BOL's).

Each page as a total weight for that page. I want the last page to have the
total weight for all pages.

The problem that comes in for me (not knowing formula's that well) is that
we do not always print all pages when there is no data on them. For example:
We only have 5 items so we only need the 1st BOL page. That is EASY, we have
the total of Page 1 on that page... Then say we have 10 items, I could have
the total of both pages on Page 2... easy again! BUT we might have 18 items.
Each page has the total (and Page 2 will have the total for pages 1 and 2 but
not page 3) and it is easy to just total the 3 pages and hand write it on the
3rd page. That looks crappy to me...

So how in the world would I write the formula for the TOTAL of all 3 pages
to show up on the 3rd page only if there are that many items and for it to
NOT show up on the 2nd page (which would show the total of pages 1 and 2) is
there is a 3rd page? I would also have to put a formula in on page 2 where I
would normally have the total if there were only 2 pages.

Example:

PL has 17 line items which will have the total weight from BOL - pg1 in cell
V49 (which it the total for V42:Z47). Then it will have the total weight from
BOL - pg2 in cell V49 (which is the total for V42:Z47). Then the total weight
from BOL - pg3 in cell V49 (which is the total for V42:Z47). Page 2 will have
the total of pages 1 and 2 (but I do not want it to show here because there
IS a page 3). I do want the total of pages 1, 2 and 3 to ONLY show up on the
3rd page in cell V50.

I sure hope this makes sense... I am still trying to get rid of the ZERO's
when I have the info going from PL to BOL's and there is nothing in that
particular cell. UGH!

Thank you and I sure hope this makes sense.

Formula for IF a total goes to 3 pages do not have on 2nd page
 

Tex,

There is probably a more sophisticated way, but...

Can you pinpoint a cell or range of cells on page 3 that will have
data in it/them if there are enough items on your list to run to page
3?

Say you can. Name that cell or range "Teller"

If you can, the formula =ISTEXT(Teller) will return the value FALSE if
there was no text in the cell or range, and TRUE if there is. (You
can also check for numbers, nulls, etc. Search the Help if you need
to.)

So, in the cell where you want the total to appear (or not appear) on
p 2, you could use Format | Conditional Formatting to format the text
in the cell. Instead of using "Cell value is...", use Formula is and
enter the formula =ISTEXT(Teller)=FALSE, and set a format of "White
text on a white background" or whatever.

If there is no text in Teller, voila, no total on p2.

Then in the cell where you want the total to appear (or not appear) on
p 3, use =ISTEXT(Teller)=TRUE...

Get it?

Again, not sophisticated, but there you go.

Another approach would be a pair of IF formulas...

P2: =IF(ISTEXT(Teller)=TRUE,"",SUM(B27:B30))

P3 =IF(ISTEXT(Teller)=TRUE,SUM(B27:B30),"")

Look up IF formulas if you are not familiar with the syntax, and
adjust what you are summing accordingly.

Hope that helps.

Patrick

Formula for IF a total goes to 3 pages do not have on 2nd page
 

You could also compare the numbers in the TOTAL field on P2 and the
TOTAL field on P3, and using conditional formatting,

on P3, if they are =, "white out" the TOTAL
on P2, if TOTALp2<TOTALp3, "white out" the TOTAL

Formula for IF a total goes to 3 pages do not have on 2nd page
 

I am going to attempt this. Thank you.

"PatrickA" wrote:

You could also compare the numbers in the TOTAL field on P2 and the
TOTAL field on P3, and using conditional formatting,

on P3, if they are =, "white out" the TOTAL
on P2, if TOTALp2<TOTALp3, "white out" the TOTAL