CODEWITHSUNDEEP
excelexcel-indirect
yaylitzis
yaylitzis

Reputation: 5534

Not a valid cell/range reference when using indirect

I have 2 sheets and I want to show the value of a cell to the other sheet. The cell I want to show in the other sheet is C26and it has the value €719.39 and the name of that sheet is January. In a cell in another sheet, I write

=INDIRECT('January'!C26)

and I have an error 

Error Function INDIRECT parameter 1 value is '€719.39'. It is not a valid cell/range reference.

Why I am getting this error?

Upvotes: 0

Views: 18607

Answers (2)

user11337080
user11337080

Reputation: 21

Indirect uses text input so it is either in quotes or refers to a cell where there is text.

=indirect("January!C26")
=indirect("January!"&"C26")

Put "January!" in C1 and "C26" in D1, for example, then: 

=indirect($C$1&$D$1)

you could also use "row()" or "column()" with the address function (using r1c1 style):

=INDIRECT(ADDRESS(ROW(),COLUMN(),1,0,"January"),0)

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

Try this, though I do not think it is what you want:

=INDIRECT("'January'!" & C26)

I think you want the simple formula:

='January'!C26

The Indirect method is used to combine the value of a cell in a formula. For example in this instance it would look for the cell reference by the value in Cell C26 on the active page. So if that cell had the Value A1 it would return the value of A1 on sheet January.

If that is what you want then use the first formula.

But the second formula will return the value of cell C26 on the sheet January.

Upvotes: 6

Related Questions