INDIRECT Formula

[surkdidat]

I have the following formula:-

=INDIRECT("'"&TEXT($P$1,"d mmmm yyyy")&"'!"&"A4")

How can this be amended so that the A4 part will adjust (so A5, A6, A7) etc when dragged down?

 

[Joe4]

Re: INDIRECT Formula help

What row are you putting the original formula in?
Let's say it is row 3, then you could use:

=INDIRECT("'"&TEXT($P$1,"d mmmm yyyy")&"'!A" & [COLOR=#ff0000]ROW()+1[/COLOR])

 

[surkdidat]

Re: INDIRECT Formula help

was on row 2, but thank you , used that and it has worked (omitting the +1 bit obviously !)

 

[Joe4]

Re: INDIRECT Formula help

If the formula is on row 2, and you want it to return cell A4 in the formula,
and the next formula is on row 3 and should return cell A5, then difference is 2, so you would use:

=INDIRECT("'"&TEXT($P$1,"d mmmm yyyy")&"'!A" & [COLOR=#ff0000]ROW()+2[/COLOR])

​