How to go through each cell in a column, test it against a whole column in another Sheet, and after finding a match, linking the second cell to the first one like: =Sheet1!C45 ?


I have several Sheets which I am filling up from a Master sheet. At first, I was linking the cells from different sheets to the master sheet, but then I made a blunder and forgot about it. Now I have a full master sheet of about 300 cells in a column, which arent linked to their counterparts in other sheets.

I need some script or something (in detail, because i dont know these things) which will, for example:
-take one cell in a given column (I think it’s C in both sheets and master) in Sheet1,

  • test every cell in a given column the Master sheet, until it finds a match
  • when It finds a match (bonus points for popup window telling a match hasnt been found, or just changing the color to red of the Sheet 1 cell), it should change the formula of the Master sheet cell to the corresponding cell in Sheet1, something like =Sheet1!C45
  • after it has done this, it goes to the next cell in Sheet1, and does it all over again.
  • the condition to stop can be something like “stop”, or a number like 99999 in Sheet1, on the bottom of the column.

IMHO you should clear the whole plunder, and start new from scratch! … nobody want write Scripts for Example or something like …

BTW. =Sheet1!C45 is Excel-syntax, but here is LibreOffice.