Lookup variabel field in variabel sheet

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

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
 
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
Click to expand...

What changes, the column, the row or both?

Ken Johnson
 
Hi Ken,
Only the row changes.
Thanks
Rgds,
Robert
Click to expand...

Assuming DIVIDENDS RECEIVABLE is a number then maybe...

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

Ken Johnson
 
Assuming DIVIDENDS RECEIVABLE is a number then maybe...

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

Ken Johnson
Click to expand...

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
 
What exactly do you mean with 9.99E+307?
You can assume that DIVIDENDS RECEIVED always lies within the range
J1:J100

Robert
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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 I am right, which will be a bloody miracle, then try...

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

Ken Johnson
Click to expand...

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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Sheets to another workbook 1
getting the sum of variabel ranges 4
How to temp disable Worksheet_Change Event while code in same completes? 4
if statement 2
Using a variabel to make an array? 4
Photo in a sheet 6
Sending an Outlook e-mail due to a condition in a cell.. 3
more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE** 3

Back
Top