PASTE LINK QUERY

[FISKO]

Hi all I have a large number of forms that pupils fill in using Excel, at the moment I transfer this data by hand to my master mark sheet. I would like to automate this and save all the extra work.
I can make it work in a small way by using copy and PasteLink to sincronise the data from the excel form they fill in to my workbook. However I thought if I get them to standardise the naming of their Documents ie: STRUCTURES#01001 ,STRUCTURES#01002 ect I could then use this extension number in a formula in my workbook, and yes I can. What I now need is a way to use the 01001 etc in my workbook to create the formula. I have columns with their name and beside that a column with thier unique number.
ie fred smith 01001 the manual Paste link gives me
='G:\MARK BOOK\[STRUCTURES#01001.xls]Sheet1'!C2
Is there a way to take the 01001 from the column and put it into a formula? I can the just do a Fill down and populate all the cells and the pupils excel results would appear in my Workbook. any Ideas I have tried using &(cell ref) but no luck.
Thanks in advance.

 

[bkpchs237]

FISKO,
I tried using the Indirect() function which is the one to use in this instance, but had no luck. However, if you already have the structure of the workbook and the cells and as you say they use a set form, you can easily copy the formula down and edit it as needed already.
For example if the first cell is C2 (as you have listed above) and in every workbook it is a reference to the same locaion for all pupils to go into that location keep it there. Then if he other cells are likewise, if you set things up for the first pupil in the first column for all such references in the desired location you have most of the setup done.
Then highlight all of the items in the first column and copy them across all the columns needed. All of them should reference the first workbook/pupil.
Now highlight each column and replace "01001" without the quotes with the appropriate number (i.e. second column "01002", third column "01003", etc.)
Of course if the items are in columns rather than rows simply use the same procedure above substituting rows where I mentioned columns and columns where I mentioned rows to obtain the same results.

Hope this helps.