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.

Hi!

Try this:

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

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

Biff

"K.L. Smith" wrote in message
...
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.

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
"K.L. Smith" wrote in message
...
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.

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" wrote:

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
"K.L. Smith" wrote in message
...
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.

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 .
Remove the "nop" from this address. HTH Otto
"K.L. Smith" wrote in message
...
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" wrote:

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
"K.L. Smith" wrote in message
...
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.