1. Visiting from the US? Why not try DIYnot.US instead? Click here to continue to DIYnot.US.
    Dismiss Notice

Microsoft spreadsheet headf--k V2

Discussion in 'Software' started by HawkEye244, 11 Feb 2019.

  1. HawkEye244

    HawkEye244

    Joined:
    18 Aug 2008
    Messages:
    3,441
    Thanks Received:
    236
    Location:
    London
    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?
     
  2. EddieM

    EddieM

    Joined:
    15 Feb 2009
    Messages:
    6,741
    Thanks Received:
    253
    Country:
    United Kingdom
    I think it is quite an easy one... can't be done. 1 way yes, 2 ways, doesn't really make any sense.
     
    • Like Like x 1
  3. HawkEye244

    HawkEye244

    Joined:
    18 Aug 2008
    Messages:
    3,441
    Thanks Received:
    236
    Location:
    London
    Country:
    United Kingdom
    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.
     
  4. ericmark

    ericmark

    Joined:
    27 Jan 2008
    Messages:
    14,001
    Thanks Received:
    1,215
    Location:
    Llanfair Caereinion, Nr Welshpool
    Country:
    United Kingdom
    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.
     
  5. EddieM

    EddieM

    Joined:
    15 Feb 2009
    Messages:
    6,741
    Thanks Received:
    253
    Country:
    United Kingdom
    Access is crap!! but yes, this is beyond the "remit" of excel.
     
  6. paulrockliffe

    paulrockliffe

    Joined:
    28 Aug 2014
    Messages:
    82
    Thanks Received:
    12
    Location:
    Durham
    Country:
    United Kingdom
    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.
     
    • Like Like x 1
  7. jasonzyx

    jasonzyx

    Joined:
    6 Feb 2019
    Messages:
    1,618
    Thanks Received:
    19
    Country:
    United Kingdom
    Lotus Approach is far simpler than Access.
    If you can get hold of a copy. It will work on Windows 10
     
  8. SFK

    SFK

    Joined:
    23 Jul 2010
    Messages:
    1,009
    Thanks Received:
    231
    Location:
    Oxfordshire
    Country:
    United Kingdom
    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
     
Loading...

Share This Page