offset alternative that a list of cells can be selected not a range.

[iainjsimons]

I have the following formula below that work well if the range of cell in the height and width are concurrent in the case of the formula below one row height and three columns width (eg A1:A3)

SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-7,0,1,3))

But what if instead of a continues row you can select cells along that row as in A1+A4+A6 ? Is ther a function to do this? I have searched numerous help sites including this one but cannot find the correct function, I don't believe this is possible with offset.

Many thanks in advance.

[6StringJazzer]

What is the rule for determining which cells you want to refer to? There is probably a way to do it but you just gave us an example of three random cells.

Also, why are you using INDIRECT in this formula to generate a cell reference rather than just using the cell reference directly?

Please Login or Register  to view this content.

[iainjsimons]

Hi Jeff,

The string above is taken from part of the whole function, it aim is to add up two cell and see if they are greater than 0 if not it will then do a sum. This formula is being applied to a calendar 12mth and 3years, it works great over the first year as up to month 12 it will look back to month 10 and 11. But at the end of the year are totals with text in two columns before year two starts so in the first month it looks back at the last two columns with text in , I know it looks counter productive and why would I not use the cell refs but there are other reasons why that will take to long to go into.

[Glenn Kennedy]

Can you post a small sample sheet? I can't visualise what you want from your description.


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[6StringJazzer]

I am still not seeing the rule for determining which cells you want to refer to. I cannot envision what your data looks like from your description. Can you attach a file with sample data, point out where your formula is, and explain what result you would expect?

[iainjsimons]

Thanks to every one that replied, I realised I was making it a lot more complicated than it needed to be and I used direct cell refs.