I usually define a range and use the range name as the source dat for the
pivot. Here is an example :

=OFFSET(Calendar!$A$1,0,0,MAX(2,COUNTA(Calendar!$A :$A)),4)

This is the defination of a range called CALENDAR which uses the a dynamic
range on a worksheet called calendar whcih stretches from A1 to Ax where x is
the last row in column A.

Hope this helps

"David" wrote:

Hi,

What will change? Will the lookup value always be in the same column and
value brought back be in the same column, so just the "length" of the data
will be changing?

What is the dynamic part of the lookup?



"vardhan" wrote:

I want to apply vlookup formula to Range of cells, but data may not be static.
Help me to how to approach that...