Dynamic range

[Guest]

I need a way to determine a dynamic range in order to reference it in a
formula. The range I need to reference is in column B but the beginning row
will change each time and the length of the range will also vary. I tried
using an offset formula I found on the Contextures web site,
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) but I could not make it work,
perhaps because the starting row (and the name of the worksheet) will vary.
Can anyone help?

 

[arno]

Hi,

give a name to Sheet1!$A$1 like "myStart", give a name to Sheet1!$A:$A
like "myData". Then define a range "myNamedData" with the formula (in
menu Insert/Name/Define write in "refers to")

=offset(mystart, 0, 0, counta(mydata), 1)

if you want colum B to represent "myNamedData" then you have to adjust
your formula to

=offset(mystart, 0, 1, counta(mydata), 1)

I do not see a problem with this if you are moving around your colum A
(mystart and mydata) within the workbook, just make sure you move the
whole range.

regards

arno

 

[Guest]

Thanks for your reply. I was able set up the dynamic range as you suggested.
I made a few changes in the name to suit my project. However, the starting
place doesn't seem to move when I do a filldown. I am trying to use it as
part of the following formula: =IF(COUNTIF(JData,BData)=0,Bdata,"") If the
initial starting place for the range was B42, it appears to stay at B42 even
when I use fill down, so I'm not really checking for unique entries in the
two lists of data.

 

[arno]

Hi,

If the
initial starting place for the range was B42, it appears to stay at B42 even
when I use fill down

that's clear. Use B42 instead of a name if you want it to be flexible
when copying the formula.

arno

 

[Guest]

My problem with that is that I need to insert the formula with VBA and make
it so that my users only click a few buttons--they shouldn't even touch the
formula. The column will always be B, but the starting row will change. I
just wish there was a way to tell it to use the active cell.

 

[arno]

Hi Fin,

My problem with that is that I need to insert the formula with VBA

record a macro when you type in the formula. this formula will use
relative addresses. in your VBA select the cell and use the recorded
formula there.

arno