If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 TOTALp2TOTALp3, "white out" the TOTAL |
#4
|
|||
|
|||
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 TOTALp2TOTALp3, "white out" the TOTAL |
Thread Tools | |
Display Modes | |
|
|