Return last cell that contains a particular value

[Guest]

The scenario:
I have a spreadsheet to track my stocks. Each stock has its own worksheet
with the stock symbol as the worksheet name (ie IBM). I also have a
worksheet that summarizes all the individual sheets. On the individual stock
sheets there is a column (B) for the transaction type (buy sell dividend
split etc) and a column (C) for the amount.
Now for my problem:
I want to be able to list on the summary sheet the last amount received for
each transaction type for each stock. I have tried various LOOKUP functions
and the OFFSET function, but I always just come up with the last value in the
column, not the last value for a particular transaction type.

I am new to the forum so any and all help would be appreciated.

 

[Biff]

Hi!

Try this:

Transaction type in column B, amount to return in column C:

=LOOKUP(2,1/(B1:B20="buy"),C1:C20)

Biff

 

[Otto Moehrbach]

K.L.
Maybe there is a formula solution to your problem but I don't know what
it is. I would use VBA and simply search the column for transaction type
with the search going from the bottom up. This would always find the last
entry for that transaction type. Post back if you think this might work for
you. Include more detail about the layout of your data in both the summary
sheet and the stock sheets. HTH Otto

 

[Guest]

Biff,
Thanks a million! I pasted you answer into my spreadsheet and it worked
perfectly!

 

[Biff]

You're welcome. Thanks for the feedback!

You can add more flexibility by using a cell to hold the criteria:

D1 = some transaction type (buy, sell, dividend, split)

=LOOKUP(2,1/(B1:B20=D1),C1:C20)

Biff

 

[Guest]

Otto

I'm very weak in VBA and would like to see your solution so I can learn
something.
Instead of trying to give you all of the details here, could I email you my
spread sheet ? If that would be violating the forum's etiquette, I'll
include necessary details in a later post.

Thanks

 

[Otto Moehrbach]

K.L.
The VBA solution would fill in all the information you want in the
Summary sheet for all the stocks and for all the transaction types for each
stock, all in one swack. Yes, send me your file. Include as much
explanation as you can. Remember that you are the only one that knows what
you want and knows your file. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto