Excel auto-fill programming

[XMan]

We have a report we generate using a couple of spreadsheets. We put the performance data in a monthly one, which then has number references in the actual summary sheet.

It would be super easy if I could just use autofill for this, but I can't because it wants to change the wrong variable.

In this case, each column is a month. Each category is a row. So for the first performance Row, the performance for January is summarized as follows: ='Jan 15'!B5. February is still in B5, but sheet name changes to Feb 15. Is there anyway to set autofill to leave the cell reference alone, but update the sheet reference instead?

 

[Binky]

There's probably an easier way to do this, but you could use the indirect function to "build" your formula on the fly.

For example, on the summary table, you might have rows 1-3 to drive your formula:
A1 = Jan (and A2 - A12 = Feb through Dec)
A2 = 15 (the year for that column)
A3 = B5 (the cell reference for your summary)

In A9, your strange looking indirect formula could be:
=INDIRECT("'"&A1&" "&A2&"'!"&A3)

That should pull back the same result as "='Jan 15'!B5" because it is literally making that formula.

Be sure to read about indirect. Indirect formulas are NOT good for external links, since they don't hold the prior result and your sheet might end up with a page full of ref errors.

 

[XMan]

This works but it doesn't increment properly when I do autofill. Is there a way to "fix" the values when doing an indirect? IE, the January results are always going to be the same, but as you autofill down the cell reference increments.

I can do it manually but it's a lot of copying/pasting and editing right now.

 

[Binky]

What do you mean by autofill? Do you mean when you copy formulas down the rows?

If yes, put dollar signs in front of the column/row reference you want to lock:

=INDIRECT("'"&A$1&" "&A$2&"'!"&A$3)

 

[XMan]

Thanks again!