Help with automatic sheet referencing

[armando12345]

Hi all,

I had a question regarding automatic sheet referencing within a formula.

I have the below formula:

IF(VLOOKUP($Y4,'Pivot Paste'!A:G,5,FALSE)="TCOE",VLOOKUP($Y4,'Pivot Paste'!A:G,6,FALSE),IF(AND(AG4<>"0",COUNTIF('Pivot Paste'!A:A,'Oct''19'!Y4)>1),INDEX('Pivot Paste'!A:G,MATCH($Y4,'Pivot Paste'!A:A,0)+1,6),0))

'Oct 19' in the formula is the name of the sheet the formula is on. Would there be a way to write this formula so that if I were to post it on a sheet called 'Nov 19' It would automatically refer to that sheet?

[CK76]

Just remove the 'Oct 19'! (i.e. Sheet reference). As long as the cell/range reference refers to the same sheet as the cell holding the formula. It will work.

[The_Snook]

Hi armando12345,

Yes you could simply use find and replace (ctrl + H) to find 'Oct 19' and replace it with 'Nov 19'. Make sure you just select the relevant cells though before you do the find and replace, otherwise it will apply it to the whole worksheet.

Regards,

Snook