Therefore I decided to post some tips which can be referred to if necessary when posting an answer.
This tip is about Dynamic Ranges using the OFFSET function
Applies to : all the XL versions for PC( I don't know about MAC but I suspect it's the same
Need to know :
- OFFSET function
How to Define Names
How it works :
this time I will refer you to the following link which has all necessary info :
http://www.contextures.com/xlNames01.html#Dynamic
Drawbacks
- OFFSET is a volatile function ( more info at http://www.decisionmodels.com/calcsecretsi.htm about volatile functions)
The OFFSET syntax might be a bit awkward
It will recalculate each time something is changed on the worksheet, slowing down large sheets or sheets with many calculations
Entering a formula in the Define Name input box is not always easy
Alternatives
See part 2 and 3 which will be coming soon