CODEWITHSUNDEEP
excel
Michael Patrick
Michael Patrick

Reputation: 3

EXCEL: How to increment a worksheet but keep the cell reference the same by using autofill?

Given this formula:

='[New Daily Parts Target FY20.xls]FY20 APRIL'!$GO$30

I want to be able to drag this cell horizontally and increment the worksheet month by month while keeping the cell reference the same.

As an example:

='[New Daily Parts Target FY20.xls]FY20 APRIL'!$GO$30
='[New Daily Parts Target FY20.xls]FY20 MAY'!$GO$30
='[New Daily Parts Target FY20.xls]FY20 JUNE'!$GO$30

etc...

Is there an easy way to do this? Currently I have to manually change each cell which is becoming a pain and not feasible long term. I don't have the ability to change the worksheet names from the workbook 'New Daily Parts Target FY20.xls' because it is not my document and is updated every day. I am simply referencing information from that document in mine.

Hopefully someone here came help me with a solution. Thanks in advance!

Upvotes: 0

Views: 102

Answers (1)

BigBen
BigBen

Reputation: 50162

You can use INDIRECT and just dynamically build up the year/month portion of the filename:

=INDIRECT("'[New Daily Parts Target FY20.xls]FY" & UPPER(TEXT(DATE(2020,COLUMN(D:D),1),"Yy Mmmm")) & "'!$GO$30")

Upvotes: 0

Related Questions