VBA for index match and vlookup function

[TheLSD]

I have a code that run the index match and vlookup function, but the problem is that the data I receive is not always the same (sometimes only 3 or 5). When I run the code, the code always ask me to update the value of the unavailable sheets.

here's my code

Sub Function()

Dim ws As Worksheet

Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False


'COA
Sheets(2).Range("A6").FormulaR1C1 = _
"=IFERROR(INDEX('ACF'!R6C:R2000C,MATCH(RC3,'ACF'!R6C3:R2000C3,0)),IFERROR(INDEX('ACF'!R6C:R2000C,MATCH(RC3,'ACF'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('BIGBIKE'!R6C:R2000C,MATCH(RC3,'BIGBIKE'!R6C3:R2000C3,0)),IFERROR(INDEX('BIGBIKE'!R6C:R2000C,MATCH(RC3,'BIGBIKE'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('BOD'!R6C:R2000C,MATCH(RC3,'BOD'!R6C3:R2000C3,0)),IFERROR(INDEX('BOD'!R6C:R2000C,MATCH(RC3,'BOD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('CORPSEC'!R6C:R2000C,MATCH(RC3,'CORPSEC'!R6C3:R2000C3,0)),IFERROR(INDEX('CORPSEC'!R6C:R2000C,MATCH(RC3,'CORPSEC'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('DIGITAL'!R6C:R2000C,MATCH(RC3,'DIGITAL'!R6C3:R2000C3,0)),IFERROR(INDEX('DIGITAL'!R6C:R2000C,MATCH(RC3,'DIGITAL'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('GDG'!R6C:R2000C,MATCH(RC3,'GDG'!R6C3:R2000C3,0)),IFERROR(INDEX('GDG'!R6C:R2000C,MATCH(RC3,'GDG'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('HC3'!R6C:R2000C,MATCH(RC3,'HC3'!R6C3:R2000C3,0)),IFERROR(INDEX('HC3'!R6C:R2000C,MATCH(RC3,'HC3'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('HRGA'!R6C:R2000C,MATCH(RC3,'HRGA'!R6C3:R2000C3,0)),IFERROR(INDEX('HRGA'!R6C:R2000C,MATCH(RC3,'HRGA'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('IT'!R6C:R2000C,MATCH(RC3,'IT'!R6C3:R2000C3,0)),IFERROR(INDEX('IT'!R6C:R2000C,MATCH(RC3,'IT'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('LDD'!R6C:R2000C,MATCH(RC3,'LDD'!R6C3:R2000C3,0)),IFERROR(INDEX('LDD'!R6C:R2000C,MATCH(RC3,'LDD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('MCD'!R6C:R2000C,MATCH(RC3,'MCD'!R6C3:R2000C3,0)),IFERROR(INDEX('MCD'!R6C:R2000C,MATCH(RC3,'MCD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('MSD'!R6C:R2000C,MATCH(RC3,'MSD'!R6C3:R2000C3,0)),IFERROR(INDEX('MSD'!R6C:R2000C,MATCH(RC3,'MSD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('NPD'!R6C:R2000C,MATCH(RC3,'NPD'!R6C3:R2000C3,0)),IFERROR(INDEX('NPD'!R6C:R2000C,MATCH(RC3,'NPD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('PARTS'!R6C:R2000C,MATCH(RC3,'PARTS'!R6C3:R2000C3,0)),IFERROR(INDEX('PARTS'!R6C:R2000C,MATCH(RC3,'PARTS'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('PURCHASING'!R6C:R2000C,MATCH(RC3,'PURCHASING'!R6C3:R2000C3,0)),IFERROR(INDEX('PURCHASING'!R6C:R2000C,MATCH(RC3,'PURCHASING'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('TSD'!R6C:R2000C,MATCH(RC3,'TSD'!R6C3:R2000C3,0)),INDEX('TSD'!R6C:R2000C,MATCH(RC3,'TSD'!R6C5:R2000C5,0))" & _
")))))))))))))))))))))))))))))))"

'Status
Sheets(2).Range("D6").FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC3,'ACF'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'ACF'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'BIGBIKE'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'BIGBIKE'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'BOD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'BOD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'CORPSEC'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'CORPSEC'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'DIGITAL'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'DIGITAL'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'GDG'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'GDG'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'HC3'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'HC3'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'HRGA'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'HRGA'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'IT'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'IT'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'LDD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'LDD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'MCD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'MCD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'MSD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'MSD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'NPD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'NPD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'PARTS'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'PARTS'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'PURCHASING'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'PURCHASING'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'TSD'!R6C3:R2000C12,4,0),VLOOKUP(RC3,'TSD'!R6C5:R2000C12,2,0)" & _
")))))))))))))))))))))))))))))))"

end sub

how can I make the code run only on the available sheets? or perhaps any idea to suppress the update value pop up every time I run the code? thanks

nb:
the lookup column always the same
the location of the formula also always the same