Is there an easier way vs. what I am doing?

[Budman361530]

I posted this question earlier this week… Formula that will allow data to be obtained from sheets as they are created?  This is an up date to that question….

I have a workbook templet that I can add up to ten formatted spread sheets, and another workbook that I can add an unlimited amount of formatted spread sheets.  Workbooks are used as a templet, and I try to keep them short and simple vs. to many pages.  In my workbook I have a summary sheet, that pulls the “total” off each workbook.

 Below is one table in my Summary Sheet…

Adder 1 sheet exist.  Adder 2 through 10 exist if needed by clicking the “Add WB” button above.  Adder 1 updates by a simple “='Adder 1'!I$18” formula located just under the “price” cell.  If I were to type that formula into the cells below, I have links that won’t update when the sheets are created.  So, I made a simple macro that runs if I click the “update” button next to the appropriate Adder sheet… Adder 2, 3… etc.  Here is my maco:

This works, but it’s not clean and I can see future problems.  This will also NOT work for my unlimited workbook because I need to have "update" buttons.  There has to be an easier way to do this?

[#2]

Hi@Budman361530,

If I understand your question correctly, you do not need a macro to create the cell reference to I18. You can use the Indrict() function or combine it with the Address() function.

I have created a small example document for you. See if the solution meets your expectations.

[#3]

@dscheikey 

Thank you for your assistance.  It is immensely appreciated!  However, am having trouble understanding how this works and subsequently cannot get it to work correctly.   I used your sheet and created a much-simplified version of my worksheet.   See attached.   I made a sheet that is hidden, and that's my "adder" templet.   The "adder 1" sheet is a simple reference on the summary sheet.   Can you please explain to me how that formula knows where to look?

[#4 · Accepted solution]

Hi @Budman361530,

the INDIRECT() function resolves the cell link existing within a string and outputs the value. You can compose the strings with the & symbols. You can also use the concatenate() function.

[#5]

@dscheikey 

Sometimes I you just need to see it working to see what you’re missing.   I didn’t have the “()” on each side of Adder 2, 3, 4, etc.   When your formula was referencing E11, E12, etc, I realized that the sheets were populating as Adder (2), Adder (3), etc. vs Adder 2, Adder 3…   Added the “()” on each side of each number, now it makes sense to me.  Program is looking for whatever is called out in cells E11, and so on…

Thank you so much for your help.