# Indirect function on Excel

Solved/Closed
Related:

- Indirect function on Excel
- How many if function in excel - Guide
- Print function in excel ✓ - Forum - Excel
- How to use if function in excel with multiple words ✓ - Forum - Excel
- How to freeze today function in excel - Forum - Excel
- How to apply if function in Excel: with dates, with text - Guide

## 5 replies

rizvisa1

Mar 31, 2011 at 11:21 AM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

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

Apr 1, 2011 at 10:20 AM

- Posts
- 2888
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- August 16, 2022

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

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???

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

Apr 5, 2011 at 09:20 AM

- Posts
- 2888
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- August 16, 2022

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

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

TrowaD

Apr 11, 2011 at 08:37 AM

- Posts
- 2888
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- August 16, 2022

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

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

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

Apr 12, 2011 at 10:36 AM

- Posts
- 2888
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- August 16, 2022

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

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

Apr 12, 2011 at 08:27 AM

- Posts
- 365
- Registration date
- Monday February 7, 2011
- Status
- Contributor
- Last seen
- September 30, 2013

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,""))

it may solve your problem

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

Didn't find the answer you are looking for?

Ask a question