Linking data in columns to get automatic data change

[Guest]

I would like to know how to link columns on separate sheets so that when I
change data in the column on one sheet it changes automically in the column
of the other sheet, supposing both columns have the same name. If this is not
possible to do in Excel 2003 please tell me how to do it Access.

 

[Rick B]

By "sheet" I assume you mean form? In Access, each form can be based on a
table or query. If you have two forms pulling the same data from a table,
changing it in one will change it in the other. The form is just a window
to your data in the underlying table.

Access, unlike Excel, is a relational database. In a relational database,
you don't store the same data in two separate tables. If you have the same
"column name" (field name) in two separate "sheets" (tables), then your data
structure is flawed and needs to be normalized.

In short, Excel is a spreadsheet, Access is a database. They are not
interchangeable. Some things make sense in Excel; others in Access.

 

[Guest]

Thanks Rick B for that but the problem is that i would need to find a way to
link columns in Excel as I find it easier to use and all my data is already
on Excel. Is there any simple way to do that but in Excel? Sorry to bother
you its because if I don't find a solution in Excel its going to be much more
time consuminf for me. Thanks anyway!

 

[Rick B]

Then I would probably post the question to an Excel newsgroup.

 

[strive4peace]

In Access, you would just set the data up in the table where it
logically makes sense to store it. On the form where you collect
information for that table, you could change the data.

On other forms that are based on related tables, you will store an ID in
the related table to correspond to information in the main table, then
you simply display that main information. You do not want to store the
same information in multiple places unless it is a field for linking.

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access

------------------

In Excel, you would do something similar -- input the data in one place
and simply echo it in others

for instance, and I will just use one sheet for this example...

you have data in B1 that you also want displayed in AJ450... the formula
in AJ450 -->
=B1


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Molo--I answered your question in the Excel group--this sounds like something
you need to do in a relational db (like Access), not Excel.

I think strive is getting at the answer you need...

Dave