Find items 1 spreadsheet & show them on a different spread sheet-

[madchleo2000]

I have an "expenses" spreadsheet, an "income" spreadsheet and a "Shipping Account" spreadsheet.
In the 'expenses' spreadsheet:

• Column B3:B200 contains descriptive text which is selected from a drop down menu I created using Data validation. This contains several descriptions and 3 of them all start with the word "Shipping" (i.e. Shipping: Aust Post; Shipping: E-Fees & Shipping: other fees)-
• Column A3:A:200 contains the date
• Column J3:J200 contains the $$ spent (e.g. $20)

The 'income' spreadsheet has:

• the date in column A3:A200, and
• the amount paid by customer in column G3:G200

I want the 'shipping account' spreadsheet to automatically update to to reflect all shipping related transactions. In the 'shipping account' spreadsheet the following zones have the following data:

• A3:A200 (date)
• B3:B200 (shipping $ received)
• C3:C200 (shipping expenses)

When a description (B3:B200) in the 'expenses' spreadsheet begins with "Shipping" I want:

• The Column C value in 'shipping account' updated with the Column J value from 'expenses' & if at all possible , for Column A (date)

When the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">income</code> spreadsheet is updated I want:

• The Column B value in 'shipping account' updated with the Column G value in 'income' & if at all possible , for Column A (date)

I initially tried
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">=INDEX(J3:J200,MATCH("shipping",B3:B200,1))
=INDEX(J3:J200,MATCH((LEFT(Shipping,8),B3:B200,1))
=(INDEX(Expenses!J3:J200,MATCH(D8,Expenses!B3:B200,1)))
</code>D8 just containing the word shipping
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">=INDEX(J3:J200,MATCH("*"&shipping&"*", B3:B200, 0)
=INDEXJ3:J200(B:B;MATCH("Shipping";B:B;0))

</code>[h=1]I've since discovered :
Excel 2010 Magic Trick 797: Check Items In List And Have Them Appear On New Sheet - AGGREGATE - Yay I thought as it looked to be exactly what I needed- I worked and mimicked the formula step by step- I used "Shipping*" instead of Y and the countif formula works perfectly. Then when I got to the ROW formula it all went pear shaped. [/h]
=AGGREGATE(15,6,(ROW(Expenses!$A$3:$A$200)-1/(Expenses!$B$3:$B$200="Shipping*")))

I've been able to track parts of the formula - ROW(Expenses!$A$3:$A$200) - seems to work fine and brings back all the sheet row numbers, and from the next part onwards it does do as it's supposed to do except it does not return any "true"s so I only get "false" and my countif from earlier does recognise the 3 entries that should come back as "true"

what am i doing wrong????????

 

[madchleo2000]

re: Find items 1 spreadsheet & show them on a different spread sheet-

I've tried to move forward with the formula whilst hoping someone may be able/ willing to help- i've now come to the end of the array part of the AGGREGATE and only getting error messages

AGGREGATE(15,6,(ROW(Expenses!$A$3:$A$200)-ROW(Expenses!$A$3)+1/(Expenses!$B$3:$B$200="Shipping*"),ROWS(Expenses!B$3:B3))

I'm so stuck and need to finish this in less than 12 hours - any suggestions.tips would be very welcomed