4

For many somewhat complicated reasons – it has to do with transposing columns and rows because of third party software limitations, etc. – I have had to use the INDIRECT function in order to make copying a formula across a range of cells feasible. However, I would like to make the references direct. Consider the following example:

image

in which a cell (B2 in the example) contains =INDIRECT(A2), and cell A2 contains the text value B1. Thus, cell B2 evaluates to =INDIRECT("B1"); i.e., it fetches the value from cell B1.

Is there anyway to actually change =INDIRECT(A2) to just =B1?

3
  • You could have a macro transforming indirect functions to their actual value. Is that kind of a solution you are looking for? Apr 21, 2015 at 19:54
  • 1
    VBA will be the way to do this. I mean it's possible using a bunch of formulas, but not easy. Apr 22, 2015 at 12:38
  • @shA.t: What are you talking about?  I don't know any more behind information or inside information or whatever than you do,  But I think the requirement is comprehensible — the OP has a large spreadsheet (or workbook) that contains many indirect references (as shown) and wants to replace them with equivalent direct references en masse in an automated way. May 9, 2015 at 17:48

1 Answer 1

1

OK, this is a little long winded but it's a way I know how to complete this. I'll use your example.

The first thing to do is replace INDIRECT with "="& You can do this using find and replace if you have many cells or just in the formula bar for the example above.

The cell should now return =B1

Copy and paste special as values so it's no longer a formula.

The cell will show =B1 but won't resolve as 2 yet. Do a find and replace for = with =.

I know your just replacing a character with the same character but Excel will now return the result of the formula correctly

That should work for your situation.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .