Taking an indirect reference in Excel and making it a direct reference

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?

ExcelFreak

Posted 2015-04-21T19:39:03.187

Reputation: 41

You could have a macro transforming indirect functions to their actual value. Is that kind of a solution you are looking for? – Máté Juhász – 2015-04-21T19:54:17.327

1VBA will be the way to do this. I mean it's possible using a bunch of formulas, but not easy. – Raystafarian – 2015-04-22T12:38:41.030

@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. – G-Man Says 'Reinstate Monica' – 2015-05-09T17:48:05.170

Answers

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.

user1923975

Posted 2015-04-21T19:39:03.187

Reputation: 111

Asked: 2015-04-21T19:39:03.187

Viewed: 1 054 times

Active: 2015-12-26T19:55:59.270