Vlookup-Wrong Sheet!

[Guest]

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,

 

[Max]

Maybe you could post the vlookup formula ?

---

 

[Guest]

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

 

[Max]

=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 Insert>Name>Paste>Paste List

Copy n paste the defined range
for ADDRESS in reply here.

 

[Guest]

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]

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

 

[Guest]

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.

 

[Guest]

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

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.

---

 

[Guest]

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

BB

 

[Max]

welcome, BB.

Typo correction for this front IF part:
It should be:
=IF(B$3="","",


---