Macro to generate repetitive formulas?

[ljones123]

I have a master sheet (Master) that references another workbook (Details) that I use for inventory control. Details has a separate sheet for each part number (Part 1, Part 2, etc.). When a new quantity of Part 1 is entered (in cell A1, Part 1 tab, Details workbook) I want it to show up on the Master sheet. The link on the Master sheet would look like this:

=’[Details.xlsx]Part 1’!$A$1

Subsequently, the link to the next part would be:
=’[Details.xlsx]Part 2’!$A$1

And so on. My problem is that I have hundreds of lines on my sheet, and simply dragging the formula does not change the tab number, which is the only part of the formula I need to change. Is there a macro to generate these formulas so I don’t have to manually change it to Part 3, Part 4 and so on? I know enough excel to be dangerous, so I need expert help. I also need to know if there would be a better approach or formula to return the desired value. Thanks for your help!

 

[Scott Huish]

If your Details.xlsx workbook is open, you could use INDIRECT:

=INDIRECT("'[Details.xlsx]Part " &ROWS($A$1:A1)&"'!A1")

Copy down.

 

[ljones123]

Thank you so much Scott! I tried it and it worked like a charm. I knew there had to be a way..