Re: Nesting Address function in Offset without Quotes???

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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_offset,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" <Aaron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F0DF5E5E-2050-498C-8629-2389200754B9@xxxxxxxxxxxxxxxx
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" <Aaron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50BAF6AA-D09B-4BA0-A85D-3EBD633F7E67@xxxxxxxxxxxxxxxx
| 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?





.

Relevant Pages

  • RE: Wild Card Vlookups
    ... 'Cell Formula using the OFFSET worksheet formula with multiple criteria to ... 'Returns a reference to a range that is a specified number of rows and columns ...
    (microsoft.public.excel.misc)
  • RE: Using an offset formula for the reference in a relative refere
    ... Sheet1!A1:C4 and A1 is linked to a cell in Sheet1!A1:C4 ... I will need to figure out how to extract the variable cell reference data ... Sheet 2 to populate the row and column info in the INDEX/MATCH ... I can embed an OFFSET formula in the ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Using an offset formula for the reference in a relative refere
    ... I will need to figure out how to extract the variable cell reference data ... from cell A1, Sheet 2 to populate the row and column info in the INDEX/MATCH ... since the number of rows for the offset will be ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Create cell formula from a relative position
    ... reference - just as if you had typed it). ... You can try to use your code to figure out the proper cell addresses to ... > but it only calculates the absolute position of C1*H1 ... >> And, in general, this is the way it works: Offset gives a range the same ...
    (microsoft.public.excel.programming)
  • Re: Help with creating a function
    ... I removed the quotes around the true result ... and removed the quotes in the false result and it updates the function ... If you wish G2 to be a cell reference, it doesn't have quotes around it. ...
    (microsoft.public.excel.worksheet.functions)