Indirect function on Excel [Solved/Closed]

PM - Mar 29, 2011 at 06:57 AM - Latest reply:  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.

Thanks in advance


Excel 2003
See more 

10 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 31, 2011 at 11:21 AM
0
Thank you
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 2392 Posts Sunday September 12, 2010Registration dateContributorStatus July 16, 2018 Last seen - Apr 1, 2011 at 10:20 AM
0
Thank you
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
0
Thank you
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 2392 Posts Sunday September 12, 2010Registration dateContributorStatus July 16, 2018 Last seen - 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,

Thanks for the reply.

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 2392 Posts Sunday September 12, 2010Registration dateContributorStatus July 16, 2018 Last seen - 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 2392 Posts Sunday September 12, 2010Registration dateContributorStatus July 16, 2018 Last seen - 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 368 Posts Monday February 7, 2011Registration dateContributorStatus September 30, 2013 Last seen - Apr 12, 2011 at 08:27 AM
0
Thank you
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,""))

it may solve your problem
0
Thank you
Try this site will find more about indirect functions

http://tipsindeed.com/...