Getting Excel to work a little like Access

[MikiH]

Hi All.

I'm working in excel 2003 and want it to work a little like Access.

What I have is two worksheets, the first is similar to a form, displaying a single record. The second worksheet is the table with many records (each with a unique number for reference).

What I after is a method of pulling data from the table when entering the unique number and populating the form. Then when I update the data on the form for it to update the corisponding row in the table.

Ie.
(On the form) "find record =10"
then
copy data:A10 to form:B1
copy data:B10 to form:B2
copy data:C10 to form:B3...

Update to Form
Cell B2 updated
copy form:B2 to data:B10


I know this is easy to do in Access but unfortunatly it is not a standard install and the company I'm working for don't want to instal it.

Thanks in-advance.
Mick

 

[SkipVought]

Hi,

You can use MS Query, which has similarities to the QBE grid in Access.

Data > New DB Query > Get External Data -- Excel Files*

then drill down to your WORKBOOK. The SHEETS in your workbook will display in the Add Tables window like Sheet1$.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

I set up a simple table (not formatted as a table) then from another sheet in the same workbook tried this and MS Query said "No visible tables". So I set up a table formatted as a table. The same thing happened.

It would appear from googling this, that it is very prone to problems. I suggest for practical purposes you need to use a different approach.

 

[strongm]

>I suggest for practical purposes you need to use a different approach

I'm afraid I'd have to disagree. MS Query works fine, as long as you follow the rules.

>from another sheet in the same workbook

Rule 1: In theory it gets external data. In general don't try and use it to query a sheet in the same workbook that you are running the query from if you are intending to try and edit the query using MSQE at a later time (you'll get 'unrecognised database format' errors). However, if you don't intend to edit/modify the query later, or if you are happy to manually modify the query that MSQE creates, then this is not a problem - and querying another sheet in the same workbook is fine.

Rule 2: See the caveat mantioned in Skip's link and add the following:
a) Name your table (which actually allows you to have multiple tables per sheet, contrary to what the cabeat says)
b) If you do not name your table, ensure that Show System Tables is selected in MS Query otherwise Sheet1$, Sheet2$ etc will not show, and MSQE will report "no visible tables"

 

[SkipVought]

In the Add Tables Window, click the OPTIONS button and CHECK ALL BOXES.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Wouldn't using the formula, VLOOKUP suffice for this? Or else, is there not a way you could use a pivot table for it?

 

[BNPMike]

I changed the table to have a proper name. Still doesn't work.

On 2007, Create Table doesn't have any options to tick. It just asks whether you've got headers or not. Afterwards there are some options in the toolbar but they relate to visual formatting. Nothing that looks like connection-related.

 

[GlennUK]

Hi BNPMike,

you have the wrong end of the stick ... the Add Tables Window is in the Query Wizard.

Cheers, Glenn.

Beauty is in the eye of the beerholder.