Excel 2007 : changing a sheet reference in formula more than one time

[meslija]

Hello,
How can i change a sheet reference in a formula not manually?
Example

=IF(Nr.Bare.L!D4="-","-",SUMIFS(Tot.L!$D:$D,Tot.L!$A:$A,Nr.Bare.L!D4,Tot.L!$B:$B,Nr.Bare.L!$D$2))
The marked sheetreference i would like to change to slen!
I have at least 50 cells where i would have to change the formula.
Thanks

[MarvinP]

Hi meslija,

Look at the attached to see if it helps.
I've created a string in A2 from a dropdown in A1 that then does an Indirect to show what I think you want. There are most likely many other ways.

Look at sheet 3 and follow the logic.

[meslija]

thanks,
in the future i will use this.