Passing on your knowledge offline

[Jon von der Heyden]

Kind of along the same lines with Pivot tables is the use of the offset() function to define ranges.

I have a lot of people here that use Excel for record keeping and build graphs off the tables they create. When I started defining the table range with offset() and used refreshall in the worksheet level code, they were amazed to see that they only had to enter data and not rebuild the whole workbook each time they added a record.

Saved TONS of work around here with that litttle tidbit.

Thanks to all of you!

Hi

Offset is one of the volatile worksheet functions that some of us prefer to avoid to ensure efficient models. http://www.decisionmodels.com/calcsecretsi.htm

Another way of creating dynamic ranges may be to use INDEX instead. The following:

=OFFSET($A$1,,,COUNTA($A:$A)) could also be represented as:
=$A$1:INDEX($A:$A,COUNTA($A:$A))

Just thought you might find this useful.

 

[Darren Bartrup]

I've passed on so much knowledge from MrExcel.

The main one's that have been taken up would be

• SUMPRODUCT

• Variable named ranges - which I'm going to have to look at again now using INDEX (thanks Jon)

• FuzzyLookup - which I've been asked to convert into an SQL function on Oracle

Then there's the snippets of code I've incorporated into my projects such as

• Creating a pivot table on the fly

• Creating a chart on the fly

• Adding controls to a form at run-time and capturing their events

And not forgetting the other site's I've found through the forum:

• http://peltiertech.com/

• http://www.rondebruin.nl/

• http://spreadsheetpage.com/

The list is endless.... so many things I've learnt, and yet to learn

 

[Atroxell]

Jon,

Your response is EXACTLY why I love this forum so much. It's the little things that make the difference, and this site is an eruption of ideas, explanations and reason. Thanks so much!

It would probably be a good idea for me to do some searching for lists of volatile and non-volatile functions--well, I have some homework to do!

I will definitely incorporate your suggestions into my next generation of workbooks and will modify older workbooks as the availability arises.

Thanks again to all who are involved!

-----------------------------------------

Just went to your posted link, Jon. Most excellent! Thanks.

 

[arkusM]

Hi

Offset is one of the volatile worksheet functions that some of us prefer to avoid to ensure efficient models. http://www.decisionmodels.com/calcsecretsi.htm

Another way of creating dynamic ranges may be to use INDEX instead. The following:

=OFFSET($A$1,,,COUNTA($A:$A)) could also be represented as:
=$A$1:INDEX($A:$A,COUNTA($A:$A))

Just thought you might find this useful.

Jon,

Will a volatile function calculate in a named range? The link you gave says that volatile functions calculate on a hair trigger, and it also says that function in named ranges only calculate when called specifically, so would a volatile function within a named range be as touchy?

I use the =offset(...) in names ranges to define dynamic ranges.

Cheers,

M