Indirect function on Excel
Solved/Closed
Related:
- Indirect function on Excel
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Agp function - Guide
- Excel marksheet - Guide
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
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
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 5, 2011 at 09:20 AM
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 11, 2011 at 08:37 AM
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
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
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
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
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 12, 2011 at 08:27 AM
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
