Is it possible for excel to autofill/complete when it's an external link?

[adamgm]

I have a spreadsheet with several pages inside. Several of the pages are named after the month of the year, and obviously only January has relevant data so far.
I want some graphs on another sheet to get info from tables on the month sheets.
So, for example, I had a cell adding up the same cell in each month.
=sum(January!$L$5)
=sum(January!$L$5+February!$L$5)

etc.

I would like to just drag down these tables and autofill them as more months are filled with data, but excel doesn't like that.
Is there a way to do this?

[Aardigspook]

If the cell is always the same on the different cells, then a 3-D reference will do what you need.

In other words, if you have twelve sheets named Jan,Feb,Mar...,Nov,Dec then these two formulae are equivalent:

Please Login or Register  to view this content.

So you could put in a series of 3-D formulae for your various months. Note that 3-D formulae just note the start and end sheets, so if you were to add another sheet/sheets in between your monthly sheets (e.g. 'Q1','Q2' etc) then those sheets $L$5 would be included as well. More info on 3-D references is here: https://support.office.com/en-gb/art...a-b2ed54cc79a2

Dragging a 3-D reference down to change the end-sheet is harder, as Excel needs to know what to put in when you drag down. One way which might work for you is this:
On your summary sheet (where you're adding the data), put the names of your other sheets in a column in order. Let's say you put your 12 months in A1:A12 (A1 - Jan, A2 - Feb, etc). In B1 put this formula and drag it down to B12:

Please Login or Register  to view this content.

This will give you the equivalent of =SUM(Jan:Jan!$L$5) in B1, =SUM(Jan:Feb!$L$5) in B2, etc, down to =SUM(Jan:Dec!$L$5) in B12.
You'll get a #REF error if the end-sheet doesn't exist - you could wrap the above formula in =IFERROR(aboveformula,"") to get rid of those, if you want.

Attached is a file showing this working - the numbers in the L5 cells are just 1-12, so the sum is easy to see working.

Hopefully that's of some use?

[adamgm]

If the cell is always the same on the different cells, then a 3-D reference will do what you need.

In other words, if you have twelve sheets named Jan,Feb,Mar...,Nov,Dec then these two formulae are equivalent:

Please Login or Register  to view this content.

So you could put in a series of 3-D formulae for your various months. Note that 3-D formulae just note the start and end sheets, so if you were to add another sheet/sheets in between your monthly sheets (e.g. 'Q1','Q2' etc) then those sheets $L$5 would be included as well. More info on 3-D references is here: https://support.office.com/en-gb/art...a-b2ed54cc79a2

Dragging a 3-D reference down to change the end-sheet is harder, as Excel needs to know what to put in when you drag down. One way which might work for you is this:
On your summary sheet (where you're adding the data), put the names of your other sheets in a column in order. Let's say you put your 12 months in A1:A12 (A1 - Jan, A2 - Feb, etc). In B1 put this formula and drag it down to B12:

Please Login or Register  to view this content.

This will give you the equivalent of =SUM(Jan:Jan!$L$5) in B1, =SUM(Jan:Feb!$L$5) in B2, etc, down to =SUM(Jan:Dec!$L$5) in B12.
You'll get a #REF error if the end-sheet doesn't exist - you could wrap the above formula in =IFERROR(aboveformula,"") to get rid of those, if you want.

Attached is a file showing this working - the numbers in the L5 cells are just 1-12, so the sum is easy to see working.

Hopefully that's of some use?

Thanks very much! I will give this a try and report back.

Using a column with all of the headings was something I was trying to get working, but I wasn't sure how to write it in, I'll try your example.

[adamgm]

That looks like it's working!

The only (small) issue is that a break in the line (until we get to the months that aren't here yet), result in Ref errors. Is there a way to make excel ignore these for now?
Underneath the column of Jan-Dec I also have a sum total of everything in the column, which won't be able to show a value until the end of December now...

Edit: It looks like I may be able to be put an IFERROR around everything you had above...

[adamgm]

It's working perfectly, thanks :D

[Aardigspook]

You're welcome, glad to be of help.
If that's your problem solved, please mark the thread as Solved so others know there's an answer here (instructions in my sig) - thanks.

[newdoverman]

If the monthly worksheets are together without other worksheets in between this will work. Name the first worksheet Jan and the last worksheet Dec. This formula will sum all values in L5 of each worksheet between the first and last worksheet inclusive.

Please Login or Register  to view this content.

You can start with just the 2 worksheets and add worksheets between them for each month as required or you can create all the worksheets at one time. Just be sure that the first and last worksheets are as listed in the formula.
Sum months.JPG

[newdoverman]

This variation you might be able to use for graphing purposes. Add the months and place between Jan and End worksheets.
Attachment 499064