Microsoft spreadsheet headf--k V2

HawkEye244

HawkEye244

Joined
18 Aug 2008
Messages
3,834
Reaction score
276
Location
Devon
Country
United Kingdom
K, this one might be slightly easier. I'd just like to thank everyone that contributed to the previous thread. We got there !!!

On this one I want two spreadsheets to link to one another. For example :

Spreadsheet 1)

A1 = 25
B2 = 25

I want to link to Spreadsheet 2)

So that values

A1 = 25
B2 = 25

So that when A1 changes in Spreadsheet 1) it automatically updates Spreadsheet 2) and also the reverse, so that if Spreadsheet 2) is updated it changes spreadsheet 1) automatically?
 
I think it is quite an easy one... can't be done. 1 way yes, 2 ways, doesn't really make any sense.
 
I figured out the paste option in excel which allows for cells to be automatically updated from one spreadsheet to another. If there are many sheets I suppose the idea is to have a main sheet from which all others take orders, i.e. a master/ slave scenario. Disregard my question then.
 
Your moving into data base area, so Access rather than Excel however you can use Excel documents with Access. I only touched on it at collage, and it is so easy to mess it up, I followed the instruction book but tried to run it before finished, I was lucky to have a sister who was an expert who fixed it over the phone. My collage lecturer had still not worked it when I returned the next week.

I only did it as it was part of collage course, but there are people who spend their whole lives working with Assess it is really powerful.
 
What you've described is a circular reference, A1a =A1b = A1a = A1b etc etc etc, both cells are formulas, neither can contain a value so it can't work.

It is possible if you store the value in another cell and use VBA to update each cell from the other, but as a general rule, if you're asking that question the VBA solution isn't what you want.

There's probably a better way to do it though. Get your data and parameters on one worksheet and your two tables, whatever they are, on separate worksheets and link both back to the parameters.
 
Lotus Approach is far simpler than Access.
If you can get hold of a copy. It will work on Windows 10
 
Hawkeye,
This is easy to do, but complex to maintain.

Open Excel 1 eg called "Invitation list.xlsx" and Excel 2 eg called "Names"
In Excel1"Invitation list.xlsx" select a Cell that you want to show data from Excel2"Names".
Press '='
Use your mouse to move your cursor to Excel 2"Names.xlsx" and select the cell you want to read.
Click on that Cell.
Type in any extra maths you my want to do (eg x 14)
Press Enter/return

You will get in Excel1"Invitation list.xlsx" an equation that looks as follows:
='[Names.xlsx]Sheet 1'!$C$3
So the cell in Excel1"Invitation list.xlsx" is looking for data in file in same folder called Names.xlsx, looking in Sheet 1, and looking in Cell C3 (and locking that cell hence the $ symbols).

Notes:
It all goes wrong if you rename the files or move the files from one folder to another.

SFK
 
You must log in or register to reply here.
Back
Top