The INDIRECT worksheet function has two arguments; ref_text and a1. Ref_text is some representation of a cell address. It can be a string like “B10”, a range reference like C10 where cell C10 contains the string “B10” or even the result of a formula like “B” & (5*2).
The a1 argument can be True or False. True indicates that the ref_text you supply is in the A1 style. True is the default of this optional argument and can be omitted. False indicates that ref_text is in the R1C1 style.
Obviously passing a string like “B10” to INDIRECT isn’t very efficient. You can just refer to it directly like =B10. However, if you have to build your cell address from other cells, INDIRECT can be very useful. Here’s some examples of INDIRECT in action.
You can also use INDIRECT to reference cells on another sheet or in another workbook. It won’t work on closed workbooks, so beware of that. The syntax can be tricky, so I usually create a real reference without indirect to see what it should look like, then build my ref_text using the example.
In the examples above, I used a sheet name and workbook name that had a space in it. This is to illustrate that you need apostrophes around the worksheet and workbook names in some circumstances. It’s a good practice to include them even if you don’t need them. The whole point of INDIRECT is to build ref_text from cells that may change. If you start with a ref_text that doesn’t need apostrophes and a cell changes such that you do need apostrophes, you’ll get a #REF! error. Better safe than sorry.