Vlookup-Wrong Sheet!
 

Hello,
I had a workbook set up with a vlookup function, but I deleted that and
created a new workbook, with the same sheet names (other things are working
better in this new workbook). However....when I enter the vlookup function
on my new sheet in my new workbook, it tries to take the data from the old
deleted sheet!!! Renaming them hasn't worked. Have I completely stuffed
things up?
Thanks,

Vlookup-Wrong Sheet!
 

Maybe you could post the vlookup formula ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Vlookup-Wrong Sheet!
 

Um, yes, would probably help....
=VLOOKUP($B$3,ADDRESS,ROW(A2),0)
Thank you Max,
Regards,


"Max" wrote:

Maybe you could post the vlookup formula ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Vlookup-Wrong Sheet!
 

=VLOOKUP($B$3,ADDRESS,ROW(A2),0)

What is ADDRESS?
A defined range for the table array?
This part could be key to your problem.

In a new sheet,
Click InsertNamePastePaste List

Copy n paste the defined range
for ADDRESS in reply here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote in message
...
Um, yes, would probably help....
=VLOOKUP($B$3,ADDRESS,ROW(A2),0)
Thank you Max,

Vlookup-Wrong Sheet!
 

A B C
D E
Customer Name Address
1 Mr Edward Morris 42 Murray Street Toowoomba 4350
2 Mrs Gayle Telford 18 Cavell Street Toowoomba 4350
3 Mrs Caroline Goddard Lot 5, 24 Greens Road Highfields 4352

I've set up a macro (my first proper one, that actually works!!), but this
isn't involved in the macro Max. I need this info to be copied over to sheet
one, depending on which customer number I enter.

"Max" wrote:

=VLOOKUP($B$3,ADDRESS,ROW(A2),0)

What is ADDRESS?
A defined range for the table array?
This part could be key to your problem.

In a new sheet,
Click InsertNamePastePaste List

Copy n paste the defined range
for ADDRESS in reply here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote in message
...
Um, yes, would probably help....
=VLOOKUP($B$3,ADDRESS,ROW(A2),0)
Thank you Max,

Vlookup-Wrong Sheet!
 

This should work for you ..

Presuming the source data as posted is in Sheet2, cols A to E from row2
down, with the key col = col A (cust#)

In Sheet1,

Assuming cust#s are entered in B3 down,

Put in C3:
=IF($B3="","",VLOOKUP($B3,Sheet2!$A:$E,COLUMNS($A: A)+1,0))
Copy C3 across to F3, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote in message
...
A B C
D E
Customer Name Address
1 Mr Edward Morris 42 Murray Street Toowoomba 4350
2 Mrs Gayle Telford 18 Cavell Street Toowoomba 4350
3 Mrs Caroline Goddard Lot 5, 24 Greens Road Highfields 4352

I've set up a macro (my first proper one, that actually works!!), but this
isn't involved in the macro Max. I need this info to be copied over to
sheet
one, depending on which customer number I enter.

Vlookup-Wrong Sheet!
 

Hi Max,
I'm sorry, I haven't been very clear. When I enter the cutomer # into B3 on
sheet1, I want the address for that customer to enter into cells B6,B7,B8,B9,
corresponding to the address (sheet2) info in cells A2,B2,B3,B4 (customer #
in cell A1 of the address worksheet-sheet 2). The macro clears the contents
for each new customer.

"Max" wrote:

This should work for you ..

Presuming the source data as posted is in Sheet2, cols A to E from row2
down, with the key col = col A (cust#)

In Sheet1,

Assuming cust#s are entered in B3 down,

Put in C3:
=IF($B3="","",VLOOKUP($B3,Sheet2!$A:$E,COLUMNS($A: A)+1,0))
Copy C3 across to F3, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote in message
...
A B C
D E
Customer Name Address
1 Mr Edward Morris 42 Murray Street Toowoomba 4350
2 Mrs Gayle Telford 18 Cavell Street Toowoomba 4350
3 Mrs Caroline Goddard Lot 5, 24 Greens Road Highfields 4352

I've set up a macro (my first proper one, that actually works!!), but this
isn't involved in the macro Max. I need this info to be copied over to
sheet
one, depending on which customer number I enter.

Vlookup-Wrong Sheet!
 

ok, one way is to use OFFSET instead of VLOOKUP here ..

In Sheet1,
Assuming cust#s are entered in B3 down,

Put instead in B6:
=IF($B3="","",OFFSET(Sheet2!$A$2,MATCH(B$3,Sheet2! $A:$A,0)-2,ROWS($1:1),))
Copy down to B9. B6:B9 will return the matched results from cols B to E in
Sheet2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote:
Hi Max,
I'm sorry, I haven't been very clear. When I enter the cutomer # into B3 on
sheet1, I want the address for that customer to enter into cells B6,B7,B8,B9,
corresponding to the address (sheet2) info in cells A2,B2,B3,B4 (customer #
in cell A1 of the address worksheet-sheet 2). The macro clears the contents
for each new customer.

Vlookup-Wrong Sheet!
 

Thank you Max! That works a treat!!!!
Much Appreciated,

BB

"Max" wrote:

ok, one way is to use OFFSET instead of VLOOKUP here ..

In Sheet1,
Assuming cust#s are entered in B3 down,

Put instead in B6:
=IF($B3="","",OFFSET(Sheet2!$A$2,MATCH(B$3,Sheet2! $A:$A,0)-2,ROWS($1:1),))
Copy down to B9. B6:B9 will return the matched results from cols B to E in
Sheet2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote:
Hi Max,
I'm sorry, I haven't been very clear. When I enter the cutomer # into B3 on
sheet1, I want the address for that customer to enter into cells B6,B7,B8,B9,
corresponding to the address (sheet2) info in cells A2,B2,B3,B4 (customer #
in cell A1 of the address worksheet-sheet 2). The macro clears the contents
for each new customer.

Vlookup-Wrong Sheet!
 

welcome, BB.

Typo correction for this front IF part:
=IF($B3="","",

It should be:
=IF(B$3="","",

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BB" wrote in message
...
Thank you Max! That works a treat!!!!
Much Appreciated,

BB