I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()
Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed
Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.
You see INDIRECT does a simple job
Converts a text that represent a reference, into reference
So…
If you do
=INDIRECT("G4")
It gives you the value in G4
Notice that “G4” has the double-quotations, means
=INDIRECT("G"&SUM(S1:S4))
Will give you the value in cell G8 if total number of S1 to S4 is 8
This is powerful, and this is why I was able to do most of the tricks, like
iframe in Excel (XLiFrame), Rotate table using 1 formula, and Insert Blank rows into table using functions
Plus more
