INDIRECT formula question

  • Thread starter Thread starter wardcanoe
  • Start date Start date
W

wardcanoe

I have used the INDIRECT formula many times on a
particular spreadsheet. ie

B1: D

A1=2 C1= INDIRECT(B1&A1)
A2=2 C2= INDIRECT(B1&A2)
A3=3 C3= INDIRECT(B1&A3)
A4=3 C4= INDIRECT(B1&A4)
A5=4 C5= INDIRECT(B1&A5)
A6=4
etc

Is there a way I can transform all my INDIRECT formulas
to the proper format?

ie change: C1=D2, C2=D2, C3=D3 etc

keep in mind there is no easy way to copy this stuff down
that is why I used the INDIRECT formula in the first
place.

So, I would like to use the INDIRECT formula to get the
right data then I would like to transform the INDIRECT
formula to an easier format. IS this possible?
 
Could you not just use Replace (Ctrl-H), replacing
INDIRECT(B1&A
with
D
?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I have used the INDIRECT formula many times on a
particular spreadsheet. ie

B1: D

A1=2 C1= INDIRECT(B1&A1)
A2=2 C2= INDIRECT(B1&A2)
A3=3 C3= INDIRECT(B1&A3)
A4=3 C4= INDIRECT(B1&A4)
A5=4 C5= INDIRECT(B1&A5)
A6=4
etc

Is there a way I can transform all my INDIRECT formulas
to the proper format?

ie change: C1=D2, C2=D2, C3=D3 etc

keep in mind there is no easy way to copy this stuff down
that is why I used the INDIRECT formula in the first
place.
Click to expand...

Don't use INDIRECT at all. In C1 enter the formula

="="&B$1&A1

Select C1 and fill down as far as needed. Then select the entire column C range
of these formulas, Edit > Copy, then Edit > Paste Special as values on top of
itself. You should now have text constants that look like your intended simple
formulas. With the full column C range still selected, Edit > Replace, find =
(just an equal sign) and replace with = (again, just an equal sign). This will
effectively enter the text constants as formulas.
 
Back
Top