CODEWITHSUNDEEP
excelfunctionformula
Elvis
Elvis

Reputation: 425

how to change the text number automatically in excel without retype the formula?

enter image description here

I want to get the reference number in the second worksheet named 2020-11-01. I try to use INDIRECT function and it works well. However, the question is, if I have 1000 rows, then I need to manually change the parameter inside the INDIRECT function (change A2 to A3, A4, A5,...,A1001). Seems like this is a text so I'm not allowed to drag down the formula like usual otherwise it will return the same value. Any better way to do this or how can I solve this problem?

Upvotes: 0

Views: 59

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

You're right, the A1 is part of a string, so won't update on drag.

To change that, build that part of the string with a formula that does update

Eg

=INDIRECT("'" & $C$6 & "'!$A" & ROW($A2))

Upvotes: 2

Related Questions