Formula $ Lock help ?

[zone709]

Hi I use this formula below, but my issue I am having is I use this in my macro I run which shifts the sheet called Pay!. What happens the $AY below in red changes to like Z Or anything Less then AY. Is there a way to lock the AY cell so it wont change at all? Even with $AY it changes so wanted to see if there is a way for it not to change.


=IFERROR(VLOOKUP($C$6:$C$248,Pay!$D$2:$AY$93,2,0),"")+IFERROR(VLOOKUP($C$6:$C$248,Pay!$D$2:$AY$93,4,0),"")

 

[sheetspread]

Try putting indirect(" ") around it

 

[zone709]

Ok i think i may need help putting this one together. I an adding what you say but cant get it to go.

 

[sheetspread]

Try:

=IFERROR(VLOOKUP($C$6:$C$248,indirect("Pay!$D$2:$AY$93"),2,0),"")+IFERROR(VLOOKUP($C$6:$C$248,indirect("Pay!$D$2:$AY$93"),4,0),"")


or:

=IFERROR(VLOOKUP($C$6:$C$248,Pay!$D$2:indirect("Pay!$AY$93"),2,0),"")+IFERROR(VLOOKUP($C$6:$C$248,Pay!$D$2:indirect("Pay!$AY$93"),4,0),"")

to exclude the col D reference

 

[zone709]

hi ok this works great thanks for the help. I was way off on adding the indirect thanks again.