# Indirect function on Excel

Solved/Closed
PM - Mar 29, 2011 at 06:57 AM
tipsindeed - Apr 13, 2011 at 07:00 AM
Hello,

I need to know how I can add the following to an exsiting cell reference in a whole column.

='Sheet1'!A2 to show =INDIRECT("'Sheet1'!A2")

I have about 30 columns I need to change the formula on, I need to know if there is a simpler way then typing it in each cell.

Hope someone can help.

Excel 2003
Related:

## 5 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 31, 2011 at 11:21 AM
Are the in sequence or what ? more information would help to see what would the best way. one has to see the pattern to tell u really. are they in one row, one column, scattered. are there other formulas etc.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 1, 2011 at 10:20 AM
Hi PM,

Let me see if I understand you correctly.

You want to change:
='Sheet1'!A2
to
=INDIRECT("'Sheet1'!A2")

You have to do that for 30 columns. So in the next column you will need to change:
='Sheet1'!B2
to
=INDIRECT("'Sheet1'!B2")
And so on...

If you remove the quotation marks, excel will automatically alter the column reference when dragged across columns.
So alter
='Sheet1'!A2
into
=INDIRECT(Sheet1!A2)
And drag this formula to the right for the A2 to change into B2, C2 etc...

.........
I just noticed that =INDIRECT("'Sheet1'!A2") with quotation marks does exaclty the same as ='Sheet1'!A2 !
I was going to suggest to maybe use find&replace but now I'm lost as to why change the formula when the result is going to be the same???

Awaiting clarification.

Best regards,
Trowa
Hi Guys,

thanks for the replies.

Firstly the cell references in each column are not in sequence. e.g.

(Cell A1) ='Sheet1'!B2
(Cell A2) ='Sheet1'!B5
(Cell A3) ='Sheet1'!B8
......

I have tried to do find and replace but this wont work with different cell references. Also I can't change the formula and drag down as they are not in sequence.

Any ideas???
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 5, 2011 at 09:20 AM
Hi PM,

Only enter !B2 in cell A1 and !B5 in cell A2.
Select both cells and drag it down as far as needed.
Now use find/replace:
Find: !B
Replace: =Sheet1!B

If you have more cells containing !B, then first select the cells you want to change.

Is this working for you?

Best regards,
Trowa
Hi Trowa,

I need to also add the indirect function in front of the formula, because I insert new rows on Sheet 1 each month, all the cell references shift down.

Regards

PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 11, 2011 at 08:37 AM
Hi PM,

Then replace "!B" with "=INDIRECT(Sheet1!B".
Excel will automatically add the final ")".

If this doesn't solve your query please provide a new example for better understanding.

Best regards,
Trowa
Hi Trowa,

I triedwhat you said but as soon as I try to add "=Indirect(..." excel tells me it cant do it. Also I dont get a close bracket added to the formula.

Here's another example:

='ATD - B'!\$G\$3

Need this converted to:

=INDIRECT("'ATD - B'!\$G\$3")

Have you been able to make this work using find and replace???

Thanks

PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 12, 2011 at 10:36 AM
Hi PM,

Yes find/replace does work for me, but you will have to lose the double brackects. With the double brackets the indirect function doesn't work anyway.
I put this content in a cell: ='ATD - B'!\$G\$3
Then I use find and replace:
Find: 'ATD - B'!
Replace: INDIRECT('ATD - B'!
It really works for me.

If it still doesn't work you can always use this structure:
="text here" & A1 & "text here"
So if ='ATD - B'!\$G\$3 is in A1then first replace the = with a ' and then the formula would look like:
="=INDIRECT(" & A1 & ")"
Downside to this method is that you have to copy/paste the result as value (can be done in the same cell), then you will have to double click the cell to go in it and then hit enter to confirm to activate the formula.

Hopefully you can work with this.

Best regards,
Trowa
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 12, 2011 at 08:27 AM
Hi PM Try This,

filter all cell contain formula (suppose this formula is in cell A1)
='ATD - B'!\$G\$3

now find and replace = with '

and then use formula in next column of this as
=INDIRECT(CONCATENATE("",A1,""))