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

Explain formula components please

0

Can you please explain different components of this formula to me ? I am trying to summarize data from 12 monthly worksheets to one worksheet and need to tweak this to work. Thanks

=INDIRECT("'T2."& MONTH($A16 & 0) & " Allocation " & LEFT($A16, 3)& "'!" & ADDRESS(MATCH(G$14,'T2.2 AllocationFeb'!$B:$B,0),MATCH($A$13,'T2.2 Allocation Feb'!$6:$6,0)))

Answer
Discuss

Answers

0
Selected Answer

In regards to this formula, it is creating a dynamically generated cell reference. This is a pretty advanced formula and here are the key points:

It is trying to find what is in cell G14 and A13 on the AllocationFeb sheet and will then return the location of that data back to the formula while combining it with manipulated data from cell A16, using the LEFT() function to get the first three characters from the cell and the MONTH() function to get the number of the month from cell A16, which, presumably, contains a text representation of the month.

And the INDIRECT() function is what combines everything into a usable cell reference.

An easier way to try to understand it is to select the cell with the formula and go to the Formulas tab and click the button Evaluate Formula within the Formula Auditing section.

(If you answer my comment on your original question I can continue to help you there as well.)

Discuss

Discussion

Many thanks ! Still need help. I am going to upload the spreadsheet with the formula on Summary sheet. It is working for Feb & Mar but not for Apr & May (giving me Ref error). Can you help there ? In the formula for both Feb & Mar row there is reference to "Allocateion Feb". Does it need to change with every month ?
ferocious12 (rep: 3) Sep 7, '16 at 9:46 pm
Upload the sample file in the question instead of posting a link to dropbox. Just edit your original question and you can add the file there.
don (rep: 1989) Sep 8, '16 at 10:01 pm
Add to Discussion


Answer the Question

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