PDA

View Full Version : DynamicRanges



Pinokkio
11-13-2006, 10:01 AM
Can anyone gif me and example of the formula thats work on a sheet?

http://www.ozgrid.com/Excel/DynamicRanges.htm
Nr 7
Expand Down One Row Each Week
=OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)



P.

mvidas
11-13-2006, 10:14 AM
Hi P,

Please excuse the extra random post there, user error in posting.

=WEEKNUM(TODAY())
returns 46 (at least today it does)

so your formula is basically
=OFFSET(A1,0,0,46,1)
which translates to
"Start with A1, move down 0 rows, move right 0 columns, expand it to 46 rows tall and 1 column wide", in other words A1:A46

Pinokkio
11-13-2006, 11:17 AM
Thanks for the reply.

Is it possible to give an example in a file?

Thanks in advance

P.

mvidas
11-13-2006, 11:31 AM
Are you just looking for a reason you might use a dynamic range like this? The ozgrid page should have a lot of good reasons you might use dynamic ranges, but if you really want to see a sample usage, I'll write up a sample sheet that shows it being used.

I've attached a sample file that lists fake days off taken by week. Using a variation of your dynamic formula, this shows the number of days taken off so far (including the current week) and the number remaining.

Pinokkio
11-13-2006, 11:52 AM
Thank you for the file, but I get two error for the formulas ?
Cell B2 the translation
=SOM(VERSCHUIVING(F2;0;0;WEEKNUMMER(VANDAAG());1))
=SUM(OFFSET(F2;0;0;WEEKNUM(TODAY());1))

the error : #Name?


Any suggestions?

P.

mvidas
11-13-2006, 12:00 PM
The use of weeknum 'Requires the "Analysis Toolpak" to be installed. Tools>Add-ins>Analysis Toolpak' - per the ozgrid link above

Pinokkio
11-13-2006, 02:06 PM
Thanks!!
For taking the time to help and for the code working as well.

P.

mvidas
11-14-2006, 06:20 AM
Not a problem :) Let me know if you need anything else!
Matt