MicrosoftKanwalNo1 Hi,
There is actually no magic
This is something old there
Excel have one more datatype as we suppose. We know number (residing right by default), text (residing left by default) and logic/error values (residin middle by default). Forth datatype is reference (or range). Arrays looks like something new (DA formulas), but exist actually long time. I'm used matrix multipilcation to explain 2-dim transitions (rotate, size, etc)
Lets talk about references (don't mess with arrays). References or addresses. We have several function returning references (Offset, Indirect), but most people don't know that there is 3 operations with references - intersect, union, extend (or how to name this) allowing reference 'arithmetic'
extend - operation : colon allow create reference from 2 references refering minimal area containing BOTH operands
TRY =SUM(OFFSET(a1;0;0) : OFFSET(a1;10;10))for example (NB! replace ; with commas)
union - operation ; semicolon (or , coma in english locale) allow create multiarea reference
TRY =AREAS( (A1;A2)) // NB! =AREAS allows only 1 parameter, in brackets there is expession
intersect operation space alloing point to common part of two areas
TRY =(A:C 3:9)
This tehnique allows me do fancy dynamic things on the pre DA era. YOu can check a small example on my 'web' by address
www . sarv . ee / ftp / henn / excel - find DynamicChart and try to play with numbers in E1 and E2 (or with sliders) and check names in name manager and chart reference
