What am I doing wrong in this formula, as it is showing me that "Formula contains invalid reference"?
Next question is, can I make an indirect reference to cells with "date" format?
MacBook Pro with Touch Bar
Indirect is used with a cell address, not a cell reference. Rather than clicking on the cell or range you want to refer to, you must manually type the address as a string, or use the ADDRESS function to construct the address for you.
In this case, because the cell you want indirect to refer to is in the same table, you need only specify column and row. For example, using the western Latin alphabet: =INDIRECT(“D2”). Note the quotation marks. These indicate a string. if it is a different table, use the format: =INDIRECT(“Table::D2”). If it is on a different sheet (but still in the same document) the format becomes =INDIRECT(“Sheet::Table::D2”) where both Sheet and Table are the names assigned to each.
To answer your second question, you can use INDIRECT to reference cells with any format. Though there are generally better ways to reference a cell than using INDIRECT. It has very specific uses, such as when you have a cell address written as a string in another cell. Normally, if you make changes such as add or remove rows, or rename sheets or tables, references to those cells will change automatically. With indirect, they will not change, resulting in errors. In some cases this may be what you want. But as this does not seem to be the case here, I would recommend using INDEX if you want some flexibility, or simply =D2 if you want to refer to a specific cell.
If you are on Numbers for the Mac it will help to see the actual cell addresses if you turn off 'Use header names as labels' at Numbers > Preferences > General.
What exactly are you trying to do here?
SG
@SG What I wanted to do is this: I have some dates in some rows, and I want to add a formula to add say 5 or 10 days to that date. So, ultimate question is can I do arithmetic actions with dates?
Yes you can do calculations on dates. So if you have a date in D2 and you want to add 5 days to that date and have the result display in H2 then you would use this formula in H2:
=D2+1
SG
"Indirect" formula shows "invalid reference" error
