double lookup, nest, or macro?
 

aJulie
my apologies for the long delay
i've been through ms support & discussion archives in between daily routine
tasks.
Hopefully i can now articulate the issue a bit better....

using excel version 10.0.2614.0
ta, YES BOTH DOCUMENTS ARE OPEN
the cells are formatted as number and numbers are entered.
When I change 35 to a cell reference the return is #N/A
Data seems to be fine - as its working - to a point. The vlookup is going
directly to the 2nd spreadsheet so i have a problem. I need it to base the
lookup on the contents of a reference in the first spreadsheet. The formula's
got to lookup column A spreadsheet 1 & use the result to go and lookup
spreadsheet #2.


spreadsheet #1 Document with formula - following is in each cell
row # 8
(columnA) 00000035 (an item number that is formatted as text to keep the
zeros)
(columnB)CHARLOTTE'S WEB ( a written description)
(columnC) B (nothing to do with this process).
(columD)=VLOOKUP(35,'[rb Consignment Stock Sales
play.xls]6064'!$A$5:$D$2500,4,TRUE)

workbook 2 rb Consignment Stock Sales play
Row # 7
(columnA) 35 (an item number) - could be any row #.
(columB)CHARLOTTE'S WEB
(columnC) PTD
(columD) 5
colum D is a quantity in stock of charlottes web.

This works great & is looking up 35 from the spreadsheet #2 as the lookup
figure and returning the quantity in stock (colum D).

THE CRUNCH. 35 - works fine because its looking for 35 in spreadsheet #2
and it doesn't matter what cell 35 is in.
=VLOOKUP(35,'[rb Consignment Stock Sales play.xls]6064'!$A$5:$D$2500,4,TRUE)
=VLOOKUP(A20,'[rb Consignment Stock Sales play.xls]6064'!$A$5:$D$2500,4,TRUE)
returns #N/A - i think this is only an intermediate step.

because I don't want to have to enter the product code every row in sheet 1,
I want to fill down a formula through 4000 items - half of which are not on
the 2nd spreadsheet.
so I need it to find
"in spreadsheet #1 - whatever the number is that is in A7 (same row of
formula) ."
& use that to lookup the same number in spreadsheet #2 - then lookup the
corresponding column D in spreadsheet #2.

thanks for the help
angel


"JulieD" wrote:

Hi Josef

was the Consignment Stock qty.xls open when you tried using A6 in the
formula?

is the format of A6 text or number?
(use =ISTEXT(A6) or =ISNUMBER(A6) to determine which one is TRUE)

is the data in column A of Consignment Stock qty.xls also text or number (it
needs to be the same data type)

Cheers
JulieD


"Josef.angel" wrote in message
...
Hi JulieD
Yep - adding A6 was the first point of call.
But given that it didn't bring back the data I didn't hold
out hope for it to fill down. Next point of call was
adding in brackets,- thinking maybe I needed some sort of
isolating or defining. I tried to click on the cell but
the cell reference refused to "pop" in.
I thought I may have to double lookup - or "nest" a lookup
but NOT as 2D - row & column headings so it may be the
wrong terminology & I haven't found anything yet in helps.
WILL I have to go macro or VBA - or can lookup be
multilayered/multidocuments/multi"strings"?

its so great - this is a first time on newsgroup for me.
There are people out in cyberland happy to talk my little
excel prob with me.
angel

-----Original Message-----
Hi Josef

have you tried
=VLOOKUP(A6,'[Consignment Stock qty.xls]6064'!
$A$5:$D$2500,4,TRUE)
and filled down?

Cheers
JulieD

"Josef.angel"
wrote in message
...
HELP peoples, ..stuck& still early learning functions,
arguments,
formulas.
=VLOOKUP(49,'[Consignment Stock qty.xls]6064'!
$A$5:$D$2500,4,TRUE)
above entered in column D looking up a value in a
separate document
I want to change 49 to be a cell reference eg A6
Where column A is a list of product items & row 6 is
the same row as the
formula then based on the outcome go & lookup that
number, ie 49 using
Vlookup.

I then want to drop / drag the formula through 2500
rows always returning
the item number in column A BUT row numbers do not
correspond with the A
column product numbers. & don't know how to " nest "
to lookup the 2nd
document based on the outcome of A6 in the current
document given that 49
is
within the VLOOKup formula.

am hoping someone can follow the above
angel.


.

Hi Josef

i can't see why
=VLOOKUP(A20,'[rb Consignment Stock Sales
play.xls]6064'!$A$5:$D$2500,4,TRUE)
doesn't work if A20 has the number 35 in it. It works on the tests that i
have done.
If A20 is blank then the formula will return #NA until you enter 35 in A20.

OH, BTW why are you using TRUE in the fourth parameter - TRUE means do an
APPROXIMATE match whereas FALSE means an EXACT match - this might be causing
the problem!?!

if you would like to try it on two dummy workbooks and if it still doesn't
work, please zip them up & email them direct (julied_ng at hcts dot net dot
au) to me and i'll see if i can spot what is going on.

Cheers
JulieD


"Josef.angel" wrote in message
...
aJulie
my apologies for the long delay
i've been through ms support & discussion archives in between daily
routine
tasks.
Hopefully i can now articulate the issue a bit better....

using excel version 10.0.2614.0
ta, YES BOTH DOCUMENTS ARE OPEN
the cells are formatted as number and numbers are entered.
When I change 35 to a cell reference the return is #N/A
Data seems to be fine - as its working - to a point. The vlookup is going
directly to the 2nd spreadsheet so i have a problem. I need it to base
the
lookup on the contents of a reference in the first spreadsheet. The
formula's
got to lookup column A spreadsheet 1 & use the result to go and lookup
spreadsheet #2.


spreadsheet #1 Document with formula - following is in each cell
row # 8
(columnA) 00000035 (an item number that is formatted as text to keep
the
zeros)
(columnB)CHARLOTTE'S WEB ( a written description)
(columnC) B (nothing to do with this process).
(columD)=VLOOKUP(35,'[rb Consignment Stock Sales
play.xls]6064'!$A$5:$D$2500,4,TRUE)

workbook 2 rb Consignment Stock Sales play
Row # 7
(columnA) 35 (an item number) - could be any row #.
(columB)CHARLOTTE'S WEB
(columnC) PTD
(columD) 5
colum D is a quantity in stock of charlottes web.

This works great & is looking up 35 from the spreadsheet #2 as the lookup
figure and returning the quantity in stock (colum D).

THE CRUNCH. 35 - works fine because its looking for 35 in spreadsheet #2
and it doesn't matter what cell 35 is in.
=VLOOKUP(35,'[rb Consignment Stock Sales
play.xls]6064'!$A$5:$D$2500,4,TRUE)
=VLOOKUP(A20,'[rb Consignment Stock Sales
play.xls]6064'!$A$5:$D$2500,4,TRUE)
returns #N/A - i think this is only an intermediate step.

because I don't want to have to enter the product code every row in sheet
1,
I want to fill down a formula through 4000 items - half of which are not
on
the 2nd spreadsheet.
so I need it to find
"in spreadsheet #1 - whatever the number is that is in A7 (same row of
formula) ."
& use that to lookup the same number in spreadsheet #2 - then lookup the
corresponding column D in spreadsheet #2.

thanks for the help
angel


"JulieD" wrote:

Hi Josef

was the Consignment Stock qty.xls open when you tried using A6 in the
formula?

is the format of A6 text or number?
(use =ISTEXT(A6) or =ISNUMBER(A6) to determine which one is TRUE)

is the data in column A of Consignment Stock qty.xls also text or number
(it
needs to be the same data type)

Cheers
JulieD


"Josef.angel" wrote in message
...
Hi JulieD
Yep - adding A6 was the first point of call.
But given that it didn't bring back the data I didn't hold
out hope for it to fill down. Next point of call was
adding in brackets,- thinking maybe I needed some sort of
isolating or defining. I tried to click on the cell but
the cell reference refused to "pop" in.
I thought I may have to double lookup - or "nest" a lookup
but NOT as 2D - row & column headings so it may be the
wrong terminology & I haven't found anything yet in helps.
WILL I have to go macro or VBA - or can lookup be
multilayered/multidocuments/multi"strings"?

its so great - this is a first time on newsgroup for me.
There are people out in cyberland happy to talk my little
excel prob with me.
angel

-----Original Message-----
Hi Josef

have you tried
=VLOOKUP(A6,'[Consignment Stock qty.xls]6064'!
$A$5:$D$2500,4,TRUE)
and filled down?

Cheers
JulieD

"Josef.angel"
wrote in message
...
HELP peoples, ..stuck& still early learning functions,
arguments,
formulas.
=VLOOKUP(49,'[Consignment Stock qty.xls]6064'!
$A$5:$D$2500,4,TRUE)
above entered in column D looking up a value in a
separate document
I want to change 49 to be a cell reference eg A6
Where column A is a list of product items & row 6 is
the same row as the
formula then based on the outcome go & lookup that
number, ie 49 using
Vlookup.

I then want to drop / drag the formula through 2500
rows always returning
the item number in column A BUT row numbers do not
correspond with the A
column product numbers. & don't know how to " nest "
to lookup the 2nd
document based on the outcome of A6 in the current
document given that 49
is
within the VLOOKup formula.

am hoping someone can follow the above
angel.


.