Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formulas And Functions Between Workbooks

0

 I am working on a formular which pulls information from one workbook to another. How can I make the SHEETS to automatically change when the formular is dragged through the cells of workbook where the formula is set?

The formular is as stated below:

='[Yearlyexpenses.xls]Sheet1'!$B$4.

So, how can I make Sheet1 to autmatically change to subsequent Sheets?

Post Edited
Title Not Appropriate: Title was not appropriate. (could be all caps, annoying punctuation, etc.)
Answer
Discuss

Discussion

Please don't use all caps in your title - that means you are YELLING AT US!
cappymer1 (rep: 120) Aug 9, '17 at 5:56 pm
I don't believe that is possible. However, you can use Find & Replace to change the reference in all formulas on a sheet in one go.
Variatus (rep: 4889) Aug 9, '17 at 10:02 pm
The answer below extracts the same cell from a number of sheets into one column of a new worksheet. My above suggestion applies if you wish to copy various cells from from various sheets to various sheets using the same formula. Your question wasn't clear in this regard. So you have answers for both cases. :-)
Variatus (rep: 4889) Aug 10, '17 at 10:53 am
Add to Discussion

Answers

0

Aldrin, try this:

=INDIRECT("Sheet"&ROW(A1)&"!$B$4")

Just copy this down and it will return the value from cell B4 from as many sheets as you need it to.Test this in your own workbook before you try to do it across separate files.

Hope this helps :)

Discuss

Discussion

To add the workbook reference to Cappymer's formula, do it like this:
=INDIRECT("'[Yearlyexpenses.xls]Sheet"&ROW(A2)&"'!$A$1")
don (rep: 1989) Aug 10, '17 at 7:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login