I have the following scenario:
Customer
Has multiple Suppliers
Has multiple Fullfillment Centers
Has multiple inventory items
Sub Total of Items
Sub Total of FC
All Suppliers totals
For example, (FC=Fulfillment Center):
A B
1 Supplier #1
2 FC1 Item #1 1.00
3 FC1 Item #2 2.00
4 FC1 Total 3.00
5 FC2 Item #1 4.00
6 FC2 Total 4.00
7 Supp #1 Total 7.00
8 Supplier #2
9 FC1 Item #1 3.00
10 FC1 Item #2 2.00
11 FC1 Total 5.00
12 FC2 Item #1 6.00
13 FC2 Total 6.00
14 Supp #1 Total 11.00
15 Total (All) 18.00
What I end up with is a formula like this in my Grand Total:
=SUM(G10:G11,G20:G22...), the sheet has 3 nested forEach's and I run into the
problem that SUM only takes 30 comma separated values. I think it is because
there are text column headers for each section. I may need to figure out a way
to tuck the amounts into columns someplace else and get a sum from there but
the loops are still creating gaps...
It seems like using named cell ranges is going to be the solution here but jXLS
doesn't seem to want to respect named cells. When it processes the spreadsheet
it seems to be renaming them back to their original value (ie A1). Is there
anway to tell jXLS not to do this? I can't find it anywhere in the
documentation...
Thanks to any and all that can help or offer suggestions!
Jim
>> This solution below was so close but didn't quite work as described above;
Sounds like you are having a column with Subtotals and Grand Totals. The SUM
function that yields your grand total does not need to pick out ranges. Just
run the SUM function over the entire column
For example
A B
1 Supplier #1 1.00
2 2.00
3 Subtotal @SUM(B1:B2)
4 Supplier #2 3.00
5 4.00
6 Subtotal @SUM(B4:B5)
7 GRAND TOTAL @SUM(B1:B5)
You would think that the GRAND TOTAL would be double the correct result,
because it looks like the formula includes the subtotals at B3 and B6. But such
is not the case. The SUM function will ignore cells which contain subtotals
from cells already included in the SUM.
I'm not sure that the POI Formula Evaluator behaves this way. But Excel does.
------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:
Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
jXLS-user mailing list
jXLS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jxls-user
