range variable

**edric** · 10-01-2012, 05:38 PM

the issue of how to reference a cell using a variable has been covered previously using INDIRECT and ADDRESS but I am trying to use a variable that refers to a cell range within a function e.g. =SUM(A1:A5) where I would like to replace "A1:A5" with a cell location which contains the cell range as the function I am using is in many places on the spreadsheet and it would be tedious to have to edit every formula that contains the cell range to change it to another cell range...any ideas?

**TMS** · 10-01-2012, 06:01 PM

=SUM(INDIRECT(D1))

where cell D1 contains A1:A5

Can't help thinking that if you have to edit a cell so that all your formulae referencing a range are updated, it could do with re-engineering. Can you not specify a dynamic named range?

Regards, TMS

Regards, TMS

**edric** · 10-02-2012, 02:04 AM

Thanks TMS I should have made my problem clearer and more specific as the cell range I am trying to make into a variable is contained within a function which refers to another worksheet in the form of FUNCTION('WORKSHEET!A1:A5')and excel returns an error if i try to use a function like INDIRECT within a reference to another worksheet

**Ace_XL** · 10-02-2012, 03:02 AM

TMS' formula should still work as long as input in D1 is WORKSHEET!A1:A5

Is 'worksheet' a separate worbook or separate worksheet in the same workbook?

**edric** · 10-02-2012, 08:26 AM

its a worksheet in the same workbook. i tried using the full cell range reference NAME!A1:A5 but alas still an error, I also tried putting single quotes around 'NAME' but still no joy.

**TMS** · 10-02-2012, 11:04 AM

Sheet2!A1:A5 in cell D1 works just fine for me.

C1: =SUM(INDIRECT(D1)) = 30
D1: Sheet2!A1:A5

Sheet2:A1:A5 = 2, 4, 6, 8, 10

If you put single quotes around the sheet name, the first one will be interpreted by Excel as the cell being Text and you will get a #REF! error. The implication is that you would struggle to have a reference where the sheet name had a space in it.

Regards, TMS

**edric** · 10-02-2012, 02:23 PM

Thanks for your patience on this. I had tried exaclty what you had suggested and I am now wondering whether it is something to do with the particular function I am using which is SUMIFS rather than just SUM ( I only used SUM in my original post as I though it would simplify matters but maybe thats not the case.

**edric** · 10-02-2012, 02:32 PM

I have just re-read your post and double checked my sheet name and it does have a space in the name and within my spreadsheet where I am using the function SUMIFS the worksheet does have single quotes around the two word sheet name.