# Creating a range within INDIRECT Function (help needed)

1. ## Creating a range within INDIRECT Function (help needed)

I am trying to update a function so that cell references A3 and A4 become range references. For example, I have 10 worksheets I want to sum cell F38 from. A3 would be Sheet 1 and A4 would be sheet 10. Right now it is only summing F38 from Sheet 1 and Sheet 10, and nothing in-between. Does anyone have a fix? See below for current formula.

This is also a formula where I would like to keep A3 and A4 to updatable references, for example I would easily be able to change the "values" within those cells so that I can expand the reference in the future.

=SUMPRODUCT(SUMIF(INDIRECT("'"&A3:A4&"'!f38"),"<>0"))

2. ## Re: Creating a range within INDIRECT Function (help needed)

These formulae all produce the same result:

Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

Note that you can add dummy Start and End worksheets that bracket the sheets you want to sum.

Can't help thinking that SUMIF(… , "<>0") is redundant … if a cell has a zero value, it won't affect the sum.

You do need to list all the sheets.

3. ## Re: Creating a range within INDIRECT Function (help needed)

Yes, I guess the real question here is if there is a way to get it to sum the cell in each sheet without needing to list all the sheets. That is the formula I am really trying to create here. Each quarter I have to adjust the A2:A11 range for hundreds of formulas (so next quarter I'd have to update it to A2:A12, for example) and want a work around so I only have to list Sheet #1 to the last Sheet in the range. Ex. List Sheet 1 in cell A2 and Sheet 10 (or last in the range) in cell A3.

4. ## Re: Creating a range within INDIRECT Function (help needed)

Your best bet, IMO, is to use the Start:End worksheets approach. They can, if you wish be hidden. Then all you need to do is include the sheets you want to sum in between the Start and End sheets. Equally, if you want to exclude any sheets, you can drag them outside the "bookends". You would not need to change any formulae or any "ranges".

I did try variations on your original formula but had no joy.

There are currently 1 users browsing this thread. (0 members and 1 guests)