Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-28-2017, 05:03 PM
abbani abbani is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Feb 2016
Posts: 9
abbani is on a distinguished road
Default Repeat Function By Changing One Variable

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.
Reply With Quote
  #2  
Old 09-28-2017, 09:49 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 794
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

="(='C:\Trip\[Job"&ROWS($1:1)&".xlsx]sheet2'!$M$2)"
Reply With Quote
  #3  
Old 10-04-2017, 12:02 AM
abbani abbani is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Feb 2016
Posts: 9
abbani is on a distinguished road
Default

The results are correctly displayed but it is coming as text not as a function
Reply With Quote
  #4  
Old 10-04-2017, 12:41 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
function auto complete

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 06:37 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft