dynamic named range function

I have a worksheet named: RB04244200-A5,B5 that contains many columns of data.

I would like to retrieve a subset of data from the worksheet from a web page using OLEDB. I can do it easily by creating a named range and from the web page retrieve the desired data.

my problem is how to do this dynamically! Searching the web I found the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so I've modified to meet my needs.

I need to pull data starting from row 34 in column B until column B is blank. So this is what I've got:
=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
where Model is column B header value and 60 is the number of column to pull (ie is there a way to dynamically figure out how many columns wide a worksheet is?)

How do I get the results of this function? Each time I open the worksheet and allow macros the function is there but no value. Is the formula not correct?

thanks for any assistance with these 2 questions. (getting number of columns,how do I get the results of the formula if it is indeed correct?).

thx,
- MJB

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>

Hi!

>=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)

What is the numerical value of COUNTA(Model) ?

> is there a way to dynamically figure out how many columns wide
> a worksheet is?

If every column has a header (no empty cells), say, in row 1 B1:IV1

=OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),COUNTA(1:1))

The main thing to understand about this formula is that as written it's an
array formula and is meant to return a range of cells and not just a single
cell.

COUNTA(Model) is the Height argument (how many rows starting from row 34
(B34) )

60 or COUNTA(1:1) is the Width argument (how many columns starting from
column B (B34) )

So if COUNTA(Model) evaluates to 5 and and the Width argument is 60, you
need to select a range 5 rows by 60 columns then enter the formula as an
array.

Biff

"MJB" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet named: RB04244200-A5,B5 that contains many columns of
> data.
>
> I would like to retrieve a subset of data from the worksheet from a web
> page using OLEDB. I can do it easily by creating a named range and from
> the web page retrieve the desired data.
>
> my problem is how to do this dynamically! Searching the web I found
> the following. =OFFSET(mySheet!$A$1,0,0,COUNTA(mySheet!$A:$A),1) so
> I've modified to meet my needs.
>
> I need to pull data starting from row 34 in column B until column B is
> blank. So this is what I've got:
>
> =OFFSET(RB04244200-A5,B5!B$34,0,0,COUNTA(Model),60)
> where Model is column B header value and 60 is the number of column to
> pull (ie is there a way to dynamically figure out how many columns wide
> a worksheet is?)
>
> How do I get the results of this function? Each time I open the
> worksheet and allow macros the function is there but no value. Is the
> formula not correct?
>
> thanks for any assistance with these 2 questions. (getting number of
> columns,how do I get the results of the formula if it is indeed
> correct?).
>
> thx,
> - MJB
>
>
> --
> MJB
> ------------------------------------------------------------------------
> MJB's Profile:
> http://www.excelforum.com/member.php...o&userid=25922
> View this thread: http://www.excelforum.com/showthread...hreadid=392940
>