mjbancroft
asked on
Mapping QuickBooks data in excel
I use QuickBooks for my business. I have a report in QB for my P&L that I can download into excel. In one column (Col A) I have the individual line items. These line items have a QB reference number (eg 4201) followed by a user field describing the line item. Col B is the value associated with the line item.
eg:
Col A Col B
Row 1 4200 Food 0
Row 2 4201 Credit card $10,000
Row 3 4202 Cash sales $20,000
Row 4 Total 4200 Food =B2+B3
Cell B4 has a formula in it.
I want to construct a spreadsheet where in the column A I can put the QB ref and in column B I can "lookup" the value associated with that line item eg:
Col A Col B
Row 1 4200 0
Row 2 4201 $10,000
Row 3 4202 $20,000
Where in B1 I pickup any number under "4200" but not the "Total 4200 ..." cell.
What formula do I need to put in Col B to extract the "correct" values pls?
Now imagine columns B, C, D, E ... all have different numbers in them corresponding to different dates. So in Col B I need a formula for line item "4200" that is NOT "4200 Total" and corresponds to the correct month.
eg:
Col A Col B
Row 1 4200 Food 0
Row 2 4201 Credit card $10,000
Row 3 4202 Cash sales $20,000
Row 4 Total 4200 Food =B2+B3
Cell B4 has a formula in it.
I want to construct a spreadsheet where in the column A I can put the QB ref and in column B I can "lookup" the value associated with that line item eg:
Col A Col B
Row 1 4200 0
Row 2 4201 $10,000
Row 3 4202 $20,000
Where in B1 I pickup any number under "4200" but not the "Total 4200 ..." cell.
What formula do I need to put in Col B to extract the "correct" values pls?
Now imagine columns B, C, D, E ... all have different numbers in them corresponding to different dates. So in Col B I need a formula for line item "4200" that is NOT "4200 Total" and corresponds to the correct month.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are very welcome. SUMIF allows you to sum a bunch of rows using an index column to control the sums. It means you just need one formula assuming you have an index for each row (even if repeated). It is a very handy formula.
ASKER