I know how to do a "one way" link in a spreadsheet. For example, if you change the value in A1, you can make it automatically change the value in A2 also.

However, is there a way, if you change the value A2 that it will also change the value of A1? Thus, whichever you change, A1 or A2, will change the other, instead of having to change the source only to get the linked destination to update.

ScottGem

No. Either a spreadsheet cell contains an actual value or it contains an expression that calculates a value. An Expression may include references to other cells that have either a value or an expression.

If an expression refers to a cell that has an expression referring back to that cell, you create a "circular reference" that cause Excel to gag.

I can't see where you would ever want to do something like you describe. Can you elaborate on why you think it might be necessary?

zix

I sometimes use the SORT function to see my same data, but displayed in different ways. I am looking for a way to see my data, for example, displayed alphabetically, but then in another worksheet to see the data displayed (sorted) numerically. I don't want to have to keep sorting it alphabetically and then numerically and then back to alphabetically over and over again. Its a pain switching it back and forth, but I dont know how to view it in 2 separate worksheets AND have it link 2 way.

I want any changes I make to either worksheet to be linked back to the other, but I realize links are only ONE WAY, not 2 way.

So is there a way to VIEW the data SORTED in either numerical or alphabetical, AND being able to modify the cells and having it update in BOTH worksheets?

I have 8000 rows/records and 50 columns.

Perhaps there is another way to accomplish what I want to do in Excel? The only thing I know is to import my data into a database and program it to use different "views" for the same data. However, I am not experienced in programming databases and Ive never used MS Access.

colbtech

try this, extract the zip to your machine and unzip.

Seems to work.

Attached Files

Test1.zip (2.2 KB, 42 views)

ScottGem

The answer, again, is No. You cannot modify data in 2 different places and have them both kept in sync. What you need to do is separate the functions of data entry and data display. Which is essentially, what Col's example does. The actually data goes in one sheet and that's where you enter or modify your data. The other sheets are used only to view your data. What he did is protect the view cells so that, after double clicking on the cell, you get moved to the cell that stores the actual data.

If you have Access (Microsoft's database program), what you can do is create queries on a table. The queries can be sorted or filters in a variety of ways, then you can enter data into the queries.

zix

Thanks all for your help. Yes I need MS Access or some other database front end for greater functionality than the spreadsheet can provide. Would MS Access be the best choice? If yes, what would be the best training guides/books for self-teaching this?

ScottGem

If you already have Office Pro, with Access, yes it would definitely be the best choice. Access is the most popular and best supported database program out there.

As for books, I'm currently working as technical editor for Roger Jenning's Special Edition Using Microsoft Office Access 2007 from Que books. There are editions for each version of Access.

colbtech

As an afterthought, is Microsoft Works database secure enough to do the job?