On Jan 4, 1:32 am, Ken Johnson wrote:
On Jan 4, 1:14 am, Ken Johnson wrote:



On Jan 4, 1:00 am, Robert wrote:

Column K contains amounts which I would like to see in the summary
sheet. Simply said, in the summary sheet I would like to have an
overview which amount of dividends (K) is received per portfolio (A1).
So I have 2 values in the summary sheet on which I would like to apply
the lookup:
1. Portfolio code (match with one of the 20 sheets)
2. Trial item (retrieve the amount from K that of which the name in
column J matches Dividends receivable)

HTH

Robert

Let's see if I'm following.
You want a formula that will find the text "Dividends Receivable" in
column J, then look across to column K and return that column K value
on the same row.

Is that right?

Ken Johnson

If I am right, which will be a bloody miracle, then try...

=INDIRECT(A1&"!K"&MATCH("Dividends Receivable",INDIRECT(A1&"!J:J")))

Ken Johnson

If that formula gave the right result then so will this...

=VLOOKUP("Dividends Receivable",INDIRECT(A1&"!J:K"),2,FALSE)

which is an improvement because it involves only two function calls.

Ken Johnson