We collect information by Transaction number (TNBR) and Line number (LNBR). Each transaction can have many lines. A client wants a report that shows a subtotal of sales for certain "split"lines (S/L) to look like this:
TNBR LNBR Sales
1 1 $35
1 3 $14
2 2 $22
2 S/L2 $34
3 1 $28
3 S/L1 $13
3 2 $22
- etc. -
I thought I could use the ABOVE function to get the "SL" lines via:
=if(ABOVE(LNBR,1)=LNBR,'S/L'&LNBR,LNBR), but this doesn't work.
I read elsewhere that the ABOVE function only resets when the first dimension changes. Since in this case, my first dimension is TNBR, and Split Lines ONLY occur when the TNBR and LNBR are the same, is this why ABOVE() doesn't give me what I want?
Is there another way to accomplish this? (NOTE: Client is a gov't org, and insist the spreadsheet we provide must follow their format (not all fields shown above).
Thanks,
Kevin
