Offset referenced cell

[Guest]

Sheet1 A1 shows: =Sheet2!D40

What I’m trying to do is: Offset (Sheet2!D40,-15,0)

I want to do this on Sheet 1 off of cell A1: Offset (Cell referenced in
cell A1,-15, 0).

Is this possible?

 

[Don Guillett]

Have a look in the help index for INDIRECT

 

[Guest]

Thanks, Don

I had tried Indirect but it returns #REF.

 

[Don Guillett]

try
=OFFSET(INDIRECT(a1),-15,0)

 

[Guest]

I did -- I get #REF! I tried it with different rows -- not working! If I
put A1 in quotes, it works but it's pulling the data from Sheet!1 A1, not
from Sheet!2 D40.

 

[Guest]

For Indirect() to work you have to a have a text value that looks like a cell
address or a range address. Thus, if cell A1 contains =Sheet2!D40, you are
simply referencing that cell.

A1 *should* read: Sheet2!D40

Now you can use =Offset(Indirect(A1),-15,1)

 

[Don Guillett]

spaces?

=OFFSET(INDIRECT(TRIM(a1)),-15,0)

 

[Guest]

Ohhhhh! Well that works when I take out the =, but I have over a hundred
formulas to change. I can use a helper column and edit/replace, but is there
an easier wasy to accomplish what I'm trying to do?

 

[Don Guillett]

right(a1,len(a1)-1

 

[Guest]

Be sure to select all the cells that are SUPPOSED to have text, but have a
formula instead. SELECT ONLY THOSE CELLS. Now do a Find & Repalce,
replacing the equal sign with nothing.

 

[Guest]

Hey thanks guys! I wandered off and forgot to thank you for your help. Your
tips worked well!