Excel help please - referencing cells...

[mankymummymoo]

hi, I am trying to get address lines to come up based on the cell aboves reference (not sure that makes sense so will explain...).

On worksheet named "payees" I have client names in row one (so client 1 in column A, client 2 in column B etc.), with the rows underneath containing lines of their address. I have named the cells with client names accordingly, eg. A1 is named BT.

On worksheet named "invoice" in the space for client name and address I want to be able to type into cell reference A1 =BT (for example) and it comes up with the relevant client - thats fine I've managed to do that. But what I want to do in the cells underneath, i.e. A2, A3 etc. is pick up the lines of address from the cells underneath the client that I've referenced. I tried =A1+1 but it comes up with #VALUE! error.

Hope thats understandable. Any help very greatly received, am meeting with a client this afternoon !!!

[flier]

I think an HLOOKUP function would do what you need, If you do a search on this in excel you should be able to do it, I'd find it difficult to explain it here

hth

[MrsBadger]

on the Invoice sheet, grab the correctly-linked 'client name' cell by the handle in the bottom right corner and drag down the column.

Excel will then autofill that column with refs to the contents of the column on the Payees sheet.

[MrsBadger]

(and hlookup would do it but if you are putting in the refs to the payee's names by hand then you don;t need it)

[mankymummymoo]

Thanks for the reply. Just tried that and it just fills in with the same as the A1 cell reference, ie. each row says BT.

[mankymummymoo]

ok am trying search on hlookup. thanks.

[mankymummymoo]

oh my god, i do not have a clue what I am doing !

Maybe if I simplify it to ask for advice I may be able to go from there without wasting too much of anyones time!

So, on a worksheet I have the following text in the following cells

A1 - Client 1
A2 - Address 1
A3 - Town 1

B1 - Client 2
B2 - Address 2
B3 - Town 2

In D1 I will type =A1 or =B1 depending which client it is...

Then I want some code to put into D2 (and D3 and D4 for however may address lines there are) which will bring up the next row of the address depending on which client I have referenced...

Does that make any sense? Any help extremely gratefully received...

[mankymummymoo]

just tried offset, eg.

OFFSET(B2,1,0,1,1)

but it just puts the text I've typed in

I really am thick arent I?!

[MrsBadger]

if you have you typed =A1 into cell D1 then dragging down from the bottom right handle should autofill D2 with =A2, D3 with =A3 etc

if you can;t do that then I;m afraid I am stumped - try searching Help for Autofill

[mankymummymoo]

Sorry, if I do it that simply thats fine, but the problem is that I want to be able to find the client by using cell names, so I dont have to remember what cell number each client is in, but just type the client name, so infact what Im typing in D1 is =FirstClient, because I have actually named the cell in A1 FirstClient and the cell in B1 SecondClient.

So when I drag down as you suggest it just replicates whats in D1.

Thank you so much for bearing with me !

[MrsBadger]

ah in that case dragging won't work and you do need hlookup.

here goes:
on the Payees sheet, use rows 1-4 for their names and addresses as you said before

then on the Invoice sheet, you type the client name into row A

A1 BT

in A2 you need a formula:
=Hlookup(A1,Payees!1:4,2,FALSE)

in A3 you need a formula:
=Hlookup(A1,Payees!1:4,3,FALSE)

in A4 you need a formula:
=Hlookup(A1,Payees!1:4,4,FALSE)

This should give you the client name and 3 lines of their address from the Payees sheet

[MrsBadger]

and once you have done that, select Invoice A2:a4 and drag to the right - it'll fill in with N/As.
Now any business name you type on row 1 should come up with the correct address from the Payees sheet.

[mankymummymoo]

I will give that a try, just off to pick DS up.

Thank you soooo much.

[mankymummymoo]

OH
MY
GOD

thank you. thank you. thank you.

it worked !!!!

I shall appear a genius infront of my client !

Thank you....