Hello,

First I would like to wish you all the best for 2008!

I am struggling with the following: I have a worksheet with over 20
difference trial balances, to make life easier I am making a summary
sheet that shows me only the data I am looking for. As the position of
the individual trial balances and their format tends to change through
time I have to capture this in my formulas. Could someone advice which
formula to use for:

A B
C
1 HKVH DIVIDENDS RECEIVABLE --RESULT--

I am now using =INDIRECT(A1&"!J24") which works fine as long as the
value for 'DIVIDENDS RECEIVABLE' is always in J24.

Any suggestion is most appreciated.

Many thanks

Rgds,
Robert

On Jan 3, 9:39 pm, Robert wrote:
Hello,

First I would like to wish you all the best for 2008!

I am struggling with the following: I have a worksheet with over 20
difference trial balances, to make life easier I am making a summary
sheet that shows me only the data I am looking for. As the position of
the individual trial balances and their format tends to change through
time I have to capture this in my formulas. Could someone advice which
formula to use for:

A B
C
1 HKVH DIVIDENDS RECEIVABLE --RESULT--

I am now using =INDIRECT(A1&"!J24") which works fine as long as the
value for 'DIVIDENDS RECEIVABLE' is always in J24.

Any suggestion is most appreciated.

Many thanks

Rgds,
Robert

What changes, the column, the row or both?

Ken Johnson

Hi Ken,
Only the row changes.
Thanks
Rgds,
Robert

On Jan 3, 11:03 pm, Robert wrote:
Hi Ken,
Only the row changes.
Thanks
Rgds,
Robert

Assuming DIVIDENDS RECEIVABLE is a number then maybe...

=INDIRECT(A1&"!J" & MATCH(9.99E+307,J:J))

Ken Johnson

On Jan 3, 11:26 pm, Ken Johnson wrote:
On Jan 3, 11:03 pm, Robert wrote:

Hi Ken,
Only the row changes.
Thanks
Rgds,
Robert

Assuming DIVIDENDS RECEIVABLE is a number then maybe...

=INDIRECT(A1&"!J" & MATCH(9.99E+307,J:J))

Ken Johnson

I have of course assumed that DIVIDENDS RECEIVED is the last entry in
column J on each of the 20 sheets.
I hope this is a correct assumption.

Ken Johnson

What exactly do you mean with 9.99E+307?
You can assume that DIVIDENDS RECEIVED always lies within the range
J1:J100

Robert

On Jan 3, 11:53 pm, Robert wrote:
What exactly do you mean with 9.99E+307?
You can assume that DIVIDENDS RECEIVED always lies within the range
J1:J100

Robert

I assumed that DIVIDENDS RECEIVED would be the bottom-most entry in
column J.
The likelihood that the actual value of DIVIDENDS RECEIVED would be
equal to or greater than 9.99E+307 is essentially zero, otherwise you
are working for the richest person in the universe! (9.99E+307 is the
largest number that excel can deal with) .
So, when the MATCH function goes looking for the row in column J
containing 9.99E+307 it doesn't find it and it returns instead the
number of elements in the range. With the range set at J:J, MATCH
returns the row containing DIVIDENDS RECEIVED, but only if DIVIDENDS
RECEIVED is the bottom-most entry in column J.

Is DIVIDENDS RECEIVED the bottom-most column J entry on your sheets?

Ken Johnson

On Jan 4, 12:29 am, Ken Johnson wrote:
On Jan 3, 11:53 pm, Robert wrote:

What exactly do you mean with 9.99E+307?
You can assume that DIVIDENDS RECEIVED always lies within the range
J1:J100

Robert

I assumed that DIVIDENDS RECEIVED would be the bottom-most entry in
column J.
The likelihood that the actual value of DIVIDENDS RECEIVED would be
equal to or greater than 9.99E+307 is essentially zero, otherwise you
are working for the richest person in the universe! (9.99E+307 is the
largest number that excel can deal with) .
So, when the MATCH function goes looking for the row in column J
containing 9.99E+307 it doesn't find it and it returns instead the
number of elements in the range. With the range set at J:J, MATCH
returns the row containing DIVIDENDS RECEIVED, but only if DIVIDENDS
RECEIVED is the bottom-most entry in column J.

Is DIVIDENDS RECEIVED the bottom-most column J entry on your sheets?

Ken Johnson

Oops! RECEIVABLE, not RECEIVED

Ken Johnson

Dividends received is just one of the 100 difference trial lines and
is currenly located on line 37. The value I am looking for (lookup) is
in column K. I might be mistaking but your formula does not refer to
column K to return a value, is it?

Robert

On Jan 4, 12:38 am, Robert wrote:
Dividends received is just one of the 100 difference trial lines and
is currenly located on line 37. The value I am looking for (lookup) is
in column K. I might be mistaking but your formula does not refer to
column K to return a value, is it?

Robert

No, it's looking for the bottom-most entry in column J.

Your original formula...

=INDIRECT(A1&"!J24")

refers to column J and you said that only the row changes. I stuck
with column J and assumed that DIVIDENDS RECEIVABLE would be the
bottom-most column J entry, so I added the MATCH function to include
the row number of the bottom-most column J entry.

How does column K fit in to the scheme of things?

Ken Johnson

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

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

Exact!;-)

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

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

GREAT, it works perfect!
Thanks a lot for helping me out!

Rgds,
Robert

On Jan 4, 2:37 am, Robert wrote:
GREAT, it works perfect!
Thanks a lot for helping me out!

Rgds,
Robert

Great!
Thanks for the feedback.

Ken Johnson