Nesting Address function in Offset without Quotes???
 

For the reference syntax of my offset formula I am using the Address
function. What I need is A1 but of course the Addres function returns "A1"
which means nothing to the offset function. how do I get the address formula
to just return A1? I suppose I could use MID, but is that the only way?

Nesting Address function in Offset without Quotes???
 

Actually, I can't use mid, since that also returns text I still get quotes.
I'm really stuck, how do I nest the result without the quotes??? Please help!

"Aaron" wrote:

For the reference syntax of my offset formula I am using the Address
function. What I need is A1 but of course the Addres function returns "A1"
which means nothing to the offset function. how do I get the address formula
to just return A1? I suppose I could use MID, but is that the only way?

Nesting Address function in Offset without Quotes???
 

What are you trying to achieve?
What formula did you try (with what input values)?
What result did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron" wrote in message ...
| Actually, I can't use mid, since that also returns text I still get quotes.
| I'm really stuck, how do I nest the result without the quotes??? Please help!
|
| "Aaron" wrote:
|
| For the reference syntax of my offset formula I am using the Address
| function. What I need is A1 but of course the Addres function returns "A1"
| which means nothing to the offset function. how do I get the address formula
| to just return A1? I suppose I could use MID, but is that the only way?

Nesting Address function in Offset without Quotes???
 

Hi Niek, thanks for writing back. The first arguement of an offset function
is a cell reference. I am trying to fill that arguement with the Address
function (because my cell reference is not always the same, I need a function
to describe it). Address does the trick except that the offset function
thinks of the result of the address functiona as "A1" (literally with the
quotes). But for offset to work properly and recognize the cell it has to be
A1 (no quotes). =offset(A1,...... not =offset("A1",....

If you want to see for yourself, nest Address in the first arguement of
offset then highlight the nested address function in the formula bar and hit
F9, you will see what I'm saying. Any help is appreciated!

"Niek Otten" wrote:

What are you trying to achieve?
What formula did you try (with what input values)?
What result did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron" wrote in message ...
| Actually, I can't use mid, since that also returns text I still get quotes.
| I'm really stuck, how do I nest the result without the quotes??? Please help!
|
| "Aaron" wrote:
|
| For the reference syntax of my offset formula I am using the Address
| function. What I need is A1 but of course the Addres function returns "A1"
| which means nothing to the offset function. how do I get the address formula
| to just return A1? I suppose I could use MID, but is that the only way?

Nesting Address function in Offset without Quotes???
 

The INDIRECT function will convert your text string "A1" into a cell
reference.

I can't see, however, why you are using ADDRESS and then trying to feed that
into an OFFSET formula.

If you have ADDRESS(row_add,column_add) and then trying to do
OFFSET(INDIRECT(ADDRESS,row_add,column_add),row_of fset,column_offset), then
I can't see why you don't just use
OFFSET(A1,row_add+row_offset-1,column_add+col_offset-1)
--
David Biddulph

"Aaron" wrote in message
...
Hi Niek, thanks for writing back. The first arguement of an offset
function
is a cell reference. I am trying to fill that arguement with the Address
function (because my cell reference is not always the same, I need a
function
to describe it). Address does the trick except that the offset function
thinks of the result of the address functiona as "A1" (literally with the
quotes). But for offset to work properly and recognize the cell it has to
be
A1 (no quotes). =offset(A1,...... not =offset("A1",....

If you want to see for yourself, nest Address in the first arguement of
offset then highlight the nested address function in the formula bar and
hit
F9, you will see what I'm saying. Any help is appreciated!

"Niek Otten" wrote:

What are you trying to achieve?
What formula did you try (with what input values)?
What result did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron" wrote in message
...
| Actually, I can't use mid, since that also returns text I still get
quotes.
| I'm really stuck, how do I nest the result without the quotes???
Please help!
|
| "Aaron" wrote:
|
| For the reference syntax of my offset formula I am using the Address
| function. What I need is A1 but of course the Addres function
returns "A1"
| which means nothing to the offset function. how do I get the address
formula
| to just return A1? I suppose I could use MID, but is that the only
way?

Nesting Address function in Offset without Quotes???
 

Thanks so much David, if that works it is exactly what I'm asking for. To
answer your question, I can't use A1 as the first arguement because that ref
is a variable. Might be B15 sometimes. The address function has Match
function nested in it so I get may different refs for the first offset
arguement depending on what has happened on the spreadsheet. Does that make
sense or am I making life more difficult for myself?

"David Biddulph" wrote:

The INDIRECT function will convert your text string "A1" into a cell
reference.

I can't see, however, why you are using ADDRESS and then trying to feed that
into an OFFSET formula.

If you have ADDRESS(row_add,column_add) and then trying to do
OFFSET(INDIRECT(ADDRESS,row_add,column_add),row_of fset,column_offset), then
I can't see why you don't just use
OFFSET(A1,row_add+row_offset-1,column_add+col_offset-1)
--
David Biddulph

"Aaron" wrote in message
...
Hi Niek, thanks for writing back. The first arguement of an offset
function
is a cell reference. I am trying to fill that arguement with the Address
function (because my cell reference is not always the same, I need a
function
to describe it). Address does the trick except that the offset function
thinks of the result of the address functiona as "A1" (literally with the
quotes). But for offset to work properly and recognize the cell it has to
be
A1 (no quotes). =offset(A1,...... not =offset("A1",....

If you want to see for yourself, nest Address in the first arguement of
offset then highlight the nested address function in the formula bar and
hit
F9, you will see what I'm saying. Any help is appreciated!

"Niek Otten" wrote:

What are you trying to achieve?
What formula did you try (with what input values)?
What result did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron" wrote in message
...
| Actually, I can't use mid, since that also returns text I still get
quotes.
| I'm really stuck, how do I nest the result without the quotes???
Please help!
|
| "Aaron" wrote:
|
| For the reference syntax of my offset formula I am using the Address
| function. What I need is A1 but of course the Addres function
returns "A1"
| which means nothing to the offset function. how do I get the address
formula
| to just return A1? I suppose I could use MID, but is that the only
way?

Nesting Address function in Offset without Quotes???
 

Yes, you are making life difficult for yourself.

If your ADDRESS function is using ADDRESS(15,2) [perhaps using calculations
to get the 15 and 2 parameters] to return the address B15, and you then have
OFFSET(B15,3,4) to get to reference F18, then you could just add 15-1 and
2-1 respectively to the 2nd and 3rd parameters (3 and 4 respectively) of
OFFSET to show how far you are offset from A1, and OFFSET(A1,15+3-1,2+4-1)
will equally well get you to F18.
--
David Biddulph

"Aaron" wrote in message
...
Thanks so much David, if that works it is exactly what I'm asking for. To
answer your question, I can't use A1 as the first arguement because that
ref
is a variable. Might be B15 sometimes. The address function has Match
function nested in it so I get may different refs for the first offset
arguement depending on what has happened on the spreadsheet. Does that
make
sense or am I making life more difficult for myself?

"David Biddulph" wrote:

The INDIRECT function will convert your text string "A1" into a cell
reference.

I can't see, however, why you are using ADDRESS and then trying to feed
that
into an OFFSET formula.

If you have ADDRESS(row_add,column_add) and then trying to do
OFFSET(INDIRECT(ADDRESS,row_add,column_add),row_of fset,column_offset),
then
I can't see why you don't just use
OFFSET(A1,row_add+row_offset-1,column_add+col_offset-1)
--
David Biddulph

"Aaron" wrote in message
...
Hi Niek, thanks for writing back. The first arguement of an offset
function
is a cell reference. I am trying to fill that arguement with the
Address
function (because my cell reference is not always the same, I need a
function
to describe it). Address does the trick except that the offset
function
thinks of the result of the address functiona as "A1" (literally with
the
quotes). But for offset to work properly and recognize the cell it has
to
be
A1 (no quotes). =offset(A1,...... not =offset("A1",....

If you want to see for yourself, nest Address in the first arguement of
offset then highlight the nested address function in the formula bar
and
hit
F9, you will see what I'm saying. Any help is appreciated!

"Niek Otten" wrote:

What are you trying to achieve?
What formula did you try (with what input values)?
What result did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron" wrote in message
...
| Actually, I can't use mid, since that also returns text I still get
quotes.
| I'm really stuck, how do I nest the result without the quotes???
Please help!
|
| "Aaron" wrote:
|
| For the reference syntax of my offset formula I am using the
Address
| function. What I need is A1 but of course the Addres function
returns "A1"
| which means nothing to the offset function. how do I get the
address
formula
| to just return A1? I suppose I could use MID, but is that the
only
way?

Nesting Address function in Offset without Quotes???
 

"David Biddulph" <groups [at] biddulph.org.uk wrote...
....
I can't see, however, why you are using ADDRESS and then trying to
feed that into an OFFSET formula.

If you have ADDRESS(row_add,column_add) and then trying to do

OFFSET(INDIRECT(ADDRESS,row_add,column_add),row_o ffset,column_offset),

then I can't see why you don't just use

OFFSET(A1,row_add+row_offset-1,column_add+col_offset-1)
....

or even

INDEX($1:$65536,row_add+row_offset,column_add+col_ offset)

Nesting Address function in Offset without Quotes???
 

Ah, I see. That is the same thing and I appreciate you showing it to me.

"David Biddulph" wrote:

Yes, you are making life difficult for yourself.

If your ADDRESS function is using ADDRESS(15,2) [perhaps using calculations
to get the 15 and 2 parameters] to return the address B15, and you then have
OFFSET(B15,3,4) to get to reference F18, then you could just add 15-1 and
2-1 respectively to the 2nd and 3rd parameters (3 and 4 respectively) of
OFFSET to show how far you are offset from A1, and OFFSET(A1,15+3-1,2+4-1)
will equally well get you to F18.
--
David Biddulph

"Aaron" wrote in message
...
Thanks so much David, if that works it is exactly what I'm asking for. To
answer your question, I can't use A1 as the first arguement because that
ref
is a variable. Might be B15 sometimes. The address function has Match
function nested in it so I get may different refs for the first offset
arguement depending on what has happened on the spreadsheet. Does that
make
sense or am I making life more difficult for myself?

"David Biddulph" wrote:

The INDIRECT function will convert your text string "A1" into a cell
reference.

I can't see, however, why you are using ADDRESS and then trying to feed
that
into an OFFSET formula.

If you have ADDRESS(row_add,column_add) and then trying to do
OFFSET(INDIRECT(ADDRESS,row_add,column_add),row_of fset,column_offset),
then
I can't see why you don't just use
OFFSET(A1,row_add+row_offset-1,column_add+col_offset-1)
--
David Biddulph

"Aaron" wrote in message
...
Hi Niek, thanks for writing back. The first arguement of an offset
function
is a cell reference. I am trying to fill that arguement with the
Address
function (because my cell reference is not always the same, I need a
function
to describe it). Address does the trick except that the offset
function
thinks of the result of the address functiona as "A1" (literally with
the
quotes). But for offset to work properly and recognize the cell it has
to
be
A1 (no quotes). =offset(A1,...... not =offset("A1",....

If you want to see for yourself, nest Address in the first arguement of
offset then highlight the nested address function in the formula bar
and
hit
F9, you will see what I'm saying. Any help is appreciated!

"Niek Otten" wrote:

What are you trying to achieve?
What formula did you try (with what input values)?
What result did you expect and what did you get instead?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron" wrote in message
...
| Actually, I can't use mid, since that also returns text I still get
quotes.
| I'm really stuck, how do I nest the result without the quotes???
Please help!
|
| "Aaron" wrote:
|
| For the reference syntax of my offset formula I am using the
Address
| function. What I need is A1 but of course the Addres function
returns "A1"
| which means nothing to the offset function. how do I get the
address
formula
| to just return A1? I suppose I could use MID, but is that the
only
way?