I have a similar formula in multiple cells: =OFFSET($D$1,0,1). However, the
cell address in the first part of the formula varies by cell. for example,
in another cell, the formula might be =OFFSET($D$22,0,1). I just want the
formula to equal the cell address after the work "OFFSET", not an offset
function. How do I do this without editing each individual cell?

In cell D1, type
=E1
Then copy this cell to all the other cells with your offset formulas. Excel
will figure out that you want each of these cells to reference the cell 1 to
the right.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

I have a similar formula in multiple cells: =OFFSET($D$1,0,1). However, the
cell address in the first part of the formula varies by cell. for example,
in another cell, the formula might be =OFFSET($D$22,0,1). I just want the
formula to equal the cell address after the work "OFFSET", not an offset
function. How do I do this without editing each individual cell?

Luke M: - Thanks for getting back so quickly.

Actually, its more complicated than I portrayed. The formula is actually:
=OFFSET('Sheet 2'!$D$1,0,'Ref'!$A$1)

Sheet 2 being a separate sheet with an array of data.
'Ref'!$A$1 is a changeable numeric value based on data vintage.

Keep in mind that the 'Sheet 2'! Cell address is different for each formula
I intend to change. I really don't want to change anything in 'Sheet 2'
since I concatenate various columns to perform lookups.

"Luke M" wrote:

In cell D1, type
=E1
Then copy this cell to all the other cells with your offset formulas. Excel
will figure out that you want each of these cells to reference the cell 1 to
the right.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

I have a similar formula in multiple cells: =OFFSET($D$1,0,1). However, the
cell address in the first part of the formula varies by cell. for example,
in another cell, the formula might be =OFFSET($D$22,0,1). I just want the
formula to equal the cell address after the work "OFFSET", not an offset
function. How do I do this without editing each individual cell?

Ah. Sounds like you need to remove the absolute reference.
=OFFSET('Sheet 2'!D1,0,'Ref'!$A$1)

Putting this in D1, and then copying to say, E1, would change formula to:
=OFFSET('Sheet 2'!E1,0,'Ref'!$A$1)

Is this what your're looking for?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

Luke M: - Thanks for getting back so quickly.

Actually, its more complicated than I portrayed. The formula is actually:
=OFFSET('Sheet 2'!$D$1,0,'Ref'!$A$1)

Sheet 2 being a separate sheet with an array of data.
'Ref'!$A$1 is a changeable numeric value based on data vintage.

Keep in mind that the 'Sheet 2'! Cell address is different for each formula
I intend to change. I really don't want to change anything in 'Sheet 2'
since I concatenate various columns to perform lookups.

"Luke M" wrote:

In cell D1, type
=E1
Then copy this cell to all the other cells with your offset formulas. Excel
will figure out that you want each of these cells to reference the cell 1 to
the right.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

I have a similar formula in multiple cells: =OFFSET($D$1,0,1). However, the
cell address in the first part of the formula varies by cell. for example,
in another cell, the formula might be =OFFSET($D$22,0,1). I just want the
formula to equal the cell address after the work "OFFSET", not an offset
function. How do I do this without editing each individual cell?

No,

If the formula is: =OFFSET('Sheet 2'!$D$1,0,'Ref'!$A$1)
I want to change it to ='Sheet 2'!$D$1

Again, keep in mind that I'm dealing with about 50 formulae where a similar
change has to be made. However, the 'Sheet 2' data is not sequentially laid
out, each cell reference is discrete. I tried a "wild card" edit/replace,
but it didn't take.

Thanks, again for taking the time to respond.

"Luke M" wrote:

Ah. Sounds like you need to remove the absolute reference.
=OFFSET('Sheet 2'!D1,0,'Ref'!$A$1)

Putting this in D1, and then copying to say, E1, would change formula to:
=OFFSET('Sheet 2'!E1,0,'Ref'!$A$1)

Is this what your're looking for?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

Luke M: - Thanks for getting back so quickly.

Actually, its more complicated than I portrayed. The formula is actually:
=OFFSET('Sheet 2'!$D$1,0,'Ref'!$A$1)

Sheet 2 being a separate sheet with an array of data.
'Ref'!$A$1 is a changeable numeric value based on data vintage.

Keep in mind that the 'Sheet 2'! Cell address is different for each formula
I intend to change. I really don't want to change anything in 'Sheet 2'
since I concatenate various columns to perform lookups.

"Luke M" wrote:

In cell D1, type
=E1
Then copy this cell to all the other cells with your offset formulas. Excel
will figure out that you want each of these cells to reference the cell 1 to
the right.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

I have a similar formula in multiple cells: =OFFSET($D$1,0,1). However, the
cell address in the first part of the formula varies by cell. for example,
in another cell, the formula might be =OFFSET($D$22,0,1). I just want the
formula to equal the cell address after the work "OFFSET", not an offset
function. How do I do this without editing each individual cell?

Hmm. So, you're just wanting to extract the one reference out of the formula?
Could do it in 3 Find & Replace operations.
First, find "=", replace with "#="
Next, find ",*", replace with "" (nothing)
Finally, find "*(", replace with "="
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

No,

If the formula is: =OFFSET('Sheet 2'!$D$1,0,'Ref'!$A$1)
I want to change it to ='Sheet 2'!$D$1

Again, keep in mind that I'm dealing with about 50 formulae where a similar
change has to be made. However, the 'Sheet 2' data is not sequentially laid
out, each cell reference is discrete. I tried a "wild card" edit/replace,
but it didn't take.

Thanks, again for taking the time to respond.

"Luke M" wrote:

Ah. Sounds like you need to remove the absolute reference.
=OFFSET('Sheet 2'!D1,0,'Ref'!$A$1)

Putting this in D1, and then copying to say, E1, would change formula to:
=OFFSET('Sheet 2'!E1,0,'Ref'!$A$1)

Is this what your're looking for?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

Luke M: - Thanks for getting back so quickly.

Actually, its more complicated than I portrayed. The formula is actually:
=OFFSET('Sheet 2'!$D$1,0,'Ref'!$A$1)

Sheet 2 being a separate sheet with an array of data.
'Ref'!$A$1 is a changeable numeric value based on data vintage.

Keep in mind that the 'Sheet 2'! Cell address is different for each formula
I intend to change. I really don't want to change anything in 'Sheet 2'
since I concatenate various columns to perform lookups.

"Luke M" wrote:

In cell D1, type
=E1
Then copy this cell to all the other cells with your offset formulas. Excel
will figure out that you want each of these cells to reference the cell 1 to
the right.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"txm49" wrote:

I have a similar formula in multiple cells: =OFFSET($D$1,0,1). However, the
cell address in the first part of the formula varies by cell. for example,
in another cell, the formula might be =OFFSET($D$22,0,1). I just want the
formula to equal the cell address after the work "OFFSET", not an offset
function. How do I do this without editing each individual cell?