Share via

Avoid problems with Indirect when changing the order of the referenced area

Anonymous
2023-03-17T15:03:11+00:00

Dear community,

Is there any way to stop formulas working incorrectly when making changing to the area that is referenced with the "Indirect" formula? I was using following formula: =Indirect("'Sheet'!H"&$AH97). However, as I moved column H in "Sheet", the referenced data changed. Is it possible to fix this so that the reference to column H would automatically update in the formula to keep the reference to the same column, even if its moved? COLUMN() alone does not do the job...

Many thanks!

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-03-17T16:38:01+00:00

Hi Exceluser_123!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

Please try using the ADDRESS and MATCH functions:

=INDIRECT("'Sheet'!" &ADDRESS(97,MATCH("H",'Sheet'!1:1,0)))

In this formula, the MATCH function finds the column number of the "H" header in the first row of the "Sheet" worksheet. The ADDRESS function then combines the row number (97 in this case) and the column number to create a dynamic reference to the cell you want to reference. The INDIRECT function then returns the value in that referenced cell.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru

Was this answer helpful?

1 person found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest