Excel drag copy and paste but change sheet name

[tbones]

I have an problem which I thought I could solve by using the answer from this Link thread . I have tried to recreate the same thing but to no avail.

Here is my problem.

I have an Excel doc that has say 46 sheets on it. All of the sheets are identical apart from the sheet name . The sheet names are sheet 1 to sheet 46.In each of the sheets I want to create a formula for cell A3 that I will be able to link a cell (G3) from another workbook that has the same worksheet names Sheet 1 to Sheet 46.
So in workbook 1 cell A3 in sheet names sheet 1 to Sheet 46 I want it to be the same as what is in work book 2 Cell G3 sheet names sheet 1 to sheet 46 in that workbook.

Once I have the formula I will then look to drag the formula on Workbook 1 in cell A3 to all cells A3:B17 that will represent the cells G3 to H17 in workbook 2.

I have looked to use the INDIRECT formula

So cell A3 in workbook 1 would have this formula

=INDIRECT(WORKBOOK2(cellWithName&”!G3”))
This does not seem to work.

What I am doing now is using this formula in Workbook1 cell A3
='[workbook 1.xlsx]sheet1 '!G3 This works but then I have to go on to each sheet on workbook 1 to then drag the formula manually across each and every sheet of the 46 sheets that are there.

I was hoping there would be a quick way of doing this by opening all sheets and then putting the indirect formula in.

Any help would be great.

 

[tbones]

I have an problem which I thought I could solve by using the answer from this Link thread . I have tried to recreate the same thing but to no avail.

Here is my problem.

I have an Excel doc that has say 46 sheets on it. All of the sheets are identical apart from the sheet name . The sheet names are sheet 1 to sheet 46.In each of the sheets I want to create a formula for cell A3 that I will be able to link a cell (G3) from another workbook that has the same worksheet names Sheet 1 to Sheet 46.
So in workbook 1 cell A3 in sheet names sheet 1 to Sheet 46 I want it to be the same as what is in work book 2 Cell G3 sheet names sheet 1 to sheet 46 in that workbook.

Once I have the formula I will then look to drag the formula on Workbook 1 in cell A3 to all cells A3:B17 that will represent the cells G3 to H17 in workbook 2.

I have looked to use the INDIRECT formula

So cell A3 in workbook 1 would have this formula

=INDIRECT(WORKBOOK2(cellWithName&”!G3”))
This does not seem to work.

What I am doing now is using this formula in Workbook1 cell A3
='[workbook 1.xlsx]sheet1 '!G3 This works but then I have to go on to each sheet on workbook 1 to then drag the formula manually across each and every sheet of the 46 sheets that are there.

I was hoping there would be a quick way of doing this by opening all sheets and then putting the indirect formula in.

Any help would be great.

The actual formula I am using now is
='[Football League Source Sheet 1.xlsx]Gameweek 1'!G3
So I type this in to cell A3 on the workbook 1 sheet 1.
The source is Football League Source Sheet 1.xlsx and the sheet name in Gameweek 1 and the cell would be G3
Once I type the formula in I then drag it over the cells I want it to drag over and this then populates as I would like it. To do this on all 46 sheets where I have 26 workbooks as well with the same amount of sheets would take a very long time to complete. I have been doing that but thought there could be a quicker way of doing this.

 

[tbones]

I think I can put it in another way

I have 2 workbooks.
Each workbook contains 46 sheets and I call them week numbers 1 to 46 on both workbooks.
Each workbook is unique however the 46 sheets are identical in each of their respective workbooks.

So the first workbook I am trying to put a link formula into cell A3 say for week 1 .
The information I am putting into that cell would be the data from G3 in week 1 of the other workbook.
Once I put the formula into the first workbook in cell A3 I then will drag the formula from A3 to B17 which will then get the data from the 2nd workbook from cell G3 to H17.
I can do that part quite easy as I just link the two cells straight away no problem. I then want to do the same with all of the other sheets in the work books for week 2 to week 46. I can do that easy enough but it is very time consuming.

I then have an additional 25 workbooks in the same format as workbook 1 that I want to repeat the function as I have done with workbook 1. All the other workbooks would be linked to workbook 2.
As I say I can do it ok but it is very time consuming and was hopeful I could take the workbook1 and select all sheets so that then I only have to do the link and drag once with the other workbooks 2 to 46 all having their own week number on it.E.G cell A3 ='[workbook1.xlsx]week 1'!G3 and then repeated for each sheet so the next sheet would say ='[workbook1.xlsx]week 2'!G3 and so on.Is there a quick workaround with this at all?