Help with Linking/Connecting Cells

[CameronSmart]

Linked Cells from a Shared Workbook do not correspond with the other cells

I have a set of cells which are automatically updated from a shared workbook. This link is working fine and the cells are updating.

I also have 4 columns at the end of my linked cells which are use to write comments on the sheet, these cells are present on my sheet but not the shared sheet. I insert the comments so they correspond with the linked cells, but when the linked cells are updated from the shared sheet the corresponding comment cells stay in the same place and therefore the wrong place (for example if a row is inserted).

I am wondering if there is a way to link the cells together so that they all move in one. I don't really have the option to put the comments in the shared sheet as the shared sheet is seen by many people and I don't want it seen or changed by anyone else.

Thanks

[Richard Buttrey]

Hi,

Can you give an example citing cell references and contents before and after an update

You seem to be saying that you have A1 on the shared workbook which contains say "abc". On workbook 2 you have cell A1 which is linked to A1 on the shared workbook and hence returns the value "abc" too.
You then write a comment say "def" in B1 in workbook 2.
Then, if I understand you correctly you insert a row in the shared workbook so that what was in A1 is now in A2. Are you saying that A1 in workbook 2 is not now linked to A2 in the shared workbook?

[CameronSmart]

Hi, Thanks for Replying

That's almost the problem I am having.

Like you said, A1 is linked to the shared workbook and when the shared workbook is updated, say insert a row above A1 with "ghi" so "ghi" is in A1 on the shared workbook, the link works fine and updates on the new workbook so that on the new workbook A1 = "ghi" and A2 = "abc". But then B1 on the new workbook is still "def" and I would like that then to move to B2 so it corresponds with the "abc" which is now in cell A2.

Hope I have explained this well

Thanks again

[Richard Buttrey]

mmm. The difficulty is that the new workbook doesn't know that any action has taken place on the shared workbook.

One way of getting round this might be to use a MATCH() formula in C1 of the new workbook which matches A1 with column A of the shared workbook. Initially this will return the value '1' - being row 1 in the shared workbook. After the shared workbook is updated this C1 formula would now read '2'. So perhaps one option is each time the new workbook is closed or saved, a Save/Close event macro copies C1 and pastes it to D1 as a value.

When the new workbook is next opened (after the shared workbook has been updated) then C1 will now be 2 and D1 will still be 1. The workbook open event could detect this difference and a macro could cut B1 and paste it to B2.

Obviously this needs a macro, but is that something you could work with?

[CameronSmart]

That's an idea, I never thought of that.

I will give it a go and play around with it, unfortunately my briefing was more or less ease of access and user-friendliness. For example in the last spreadsheet I was asked to make I had to create a command button with the word "refresh" on it which sorted a couple of columns because people were struggling with the idea of a custom sort.

So to be honest I think I will try and just get the items from the shared spreadsheet to come in date added order so that it is always sequential and nothing will have to be inserted and this will make it much easier for everyone to understand.

Thanks for your help!

[Richard Buttrey]

OK, good luck.

I noted that you are using a shared workbook. Be aware therefore that you could easily encounter many other problems dependant on what users are doing. Most of us avoid shared workbooks like the plague since in any serious application which has to 'do' a few things then shared workbooks invariably trip you up and just cause frustration.

If you need to gather information from several users then a better approach is to let users have their own individual workbooks and have a master workbook macro trawl around the individual file to gather information as necessary.