Any shortcuts to apply the INDIRECT function?

**sam beginer** · 01-21-2013, 11:42 PM

I want to change a number of cells in a sheet from this formula

=AVERAGE(C5:C7,C25:C27)

to this one

=AVERAGE(INDIRECT("C5:C7"),INDIRECT("C25:C27"))

The only trouble is that once the range is in quotations I can't auto fill any of the formulas to adjacent cells. The sheet is way too big to type the INDIRECT formula into each cell.

Are there any shortcuts I can use or is thre an alternative to the INDIRECT function that will do the same job?

**protonLeah** · 01-21-2013, 11:46 PM

Explain what you are trying to do that needs"indirect".

**sam beginer** · 01-22-2013, 12:04 AM

Im copying data into column C which generally needs to be rearranged so that the correct values end up in C5:C7 and C25:C27, I cant see a way to automate this step as the rearranging depends on the data and is different every time.

When I start to drag the column C data around this changes the range in "=AVERAGE(C5:C7,C25:C27)" so to avoid this I was trying to use the INDIRECT function.

**protonLeah** · 01-22-2013, 12:28 AM

Well, I still don't understand. However, you can use the "$" to anchor the cell reference to a particular row or column. E.g. C$5:C7 would hold C$5 fixed and when dragged down would become C$5:C7m C$5:C9, etc. If you use $C5:C7 and drag across it would change as C$5:D7, etc.

**sam beginer** · 01-22-2013, 12:36 AM

I have tried using absolute references, however, they lock the reference when moving the cell containing the formula but if I put data into C5:C7 and then move the data around the reference follows the data rather than staying as C5:C7 even when using $$