Repeat Function By Changing One Variable

abbani · #1 09-28-2017, 05:03 PM

I have a function
(='C:\Trip\[Job1.xlsx]sheet2'!$M$2)

I would like to repeat this function in around a 1000 rows by increasing the value of the sheet
from Job1 to Job2, Job3, and so on
Please advice.
Thanks.

xor · #2 09-28-2017, 09:49 PM

="(='C:\Trip\[Job"&ROWS($1:1)&".xlsx]sheet2'!$M$2)"

abbani · #3 10-04-2017, 12:02 AM

The results are correctly displayed but it is coming as text not as a function

Debaser · #4 10-04-2017, 12:41 AM

You can't do that with a native formula. You would have to use INDIRECT but that won't work with a closed workbook. So you will need the MOREFUNC add-in to use INDIRECT.EXT, or use code to populate the formulas in the first place, or do a manual copy/paste then replace = with = to convert the results of the formulas above to actual formulas.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Can a function or sub be called using a variable name?	omahadivision	Excel Programming	18	12-05-2013 08:14 PM
Repeat formula 5 times and repeat?	Jenny345	Excel	4	06-14-2013 04:37 PM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM