Return last cell that contains a particular value
 

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.

Return last cell that contains a particular value
 

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.

Return last cell that contains a particular value
 

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.

Return last cell that contains a particular value
 

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

"K.L. Smith" wrote in message
...
Biff,
Thanks a million! I pasted you answer into my spreadsheet and it worked
perfectly!

"Biff" wrote:

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.

Return last cell that contains a particular value
 

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.

Return last cell that contains a particular value
 

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.