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?

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


"FinChase" schrieb im Newsbeitrag
...
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?

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" wrote:

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


"FinChase" schrieb im Newsbeitrag
...
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?

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

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" wrote:

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

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