Automatic data input

J

jkelly

Not sure if this is possible but hope someone can help.

I have a group of people with name, address, city, post code etc listed
on an excel 2003 spreadsheet1 and in an Access 2003 database

I have created a speadsheet2 where users input data onto the sheet and
when they insert a value into a cell it is automatically placed in
other required cells.

I would like to automate this process further.

If a cell on sheet2has a name placed in it then the rest of the address
data is collected from sheet1 or from Access and placed in the
appropriate cells on sheet2.

Thanks in advance

John
 
E

Eddie O

It sounds like you're describing a situation where a user enters a name in
sheet2 which already exists in sheet1, and you want the data associated with
that name in sheet1 pulled over into sheet2. Setting aside the Access issue,
which is more complicated, you could pull data from sheet1 into sheet2 using
VLOOKUP. For example, if the name is typed into Column A, you could have
columns B through E (for address, city, post code, etc.) contain VLOOKUP
formulas which pull the data from sheet1 into sheet2.
To do the same thing but retrieve the data from Access, you might have to do
VBA programming, or play around with Excel's SQL.REQUEST formula, which I'm
not really familiar with.
 
J

jkelly

Thanks for your reply that is what i need.

I am not fvery amiliar with using functions and would like some
assistance with this.
Data on Sheet 1
A1 Username
B1 Address
C1 City
D1 Post Code

A2 Username
B2 Address
C2 City
D2 Post Code

and so on.

Sheet 2
When i put the username into Cell B7 i need the username to also appear
in Cell A13 with the Address in A14, City in A15 and Post Code in A16.

Can you assist me with the Function Syntax

Thanks

John
 
G

Gord Dibben

You will need 3 VLOOKUP formulas entered in A14:A16

Sheet2 A13 enter =B7

Sheet2 A14 enter =VLOOKUP(B7,Sheet1!$A$1:$D$100,2,False)

Sheet2 A15 enter =VLOOKUP(B7,Sheet1!$A$1:$D$100,3,False)

Sheet2 A16 enter =VLOOKUP(B7,Sheet1!$A$1:$D$100,4,False)

The above assumes your data reange on Sheet1 is A1:D100....adjust as needed.


Gord Dibben MS Excel MVP
 
J

jkelly

Thanks

I will try that and post back

John

Gord said:
You will need 3 VLOOKUP formulas entered in A14:A16

Sheet2 A13 enter =B7

Sheet2 A14 enter =VLOOKUP(B7,Sheet1!$A$1:$D$100,2,False)

Sheet2 A15 enter =VLOOKUP(B7,Sheet1!$A$1:$D$100,3,False)

Sheet2 A16 enter =VLOOKUP(B7,Sheet1!$A$1:$D$100,4,False)

The above assumes your data reange on Sheet1 is A1:D100....adjust as needed.


Gord Dibben MS Excel MVP
Click to expand...
 
J

jkelly

Hi

After a few minor tweaks on our spreadsheet, this worked very well.
Thank you for your input and time taken on this. It is much
appreciated.

John
 
Top