|The Sheet Name won't increment like a cell reference will. You'll have to be creative and use the INDIRECT and ROW functions:|
=INDIRECT("'Bid item " & ROW()+103 & "'!$K$40")
The INDIRECT function converts a text string into a cell reference.
The ROW function simply returns the Row number in which it resides.
If you place the formula above in Row 1, it will convert ROW()+103 to 104 and concatenate it with the rest of the string to end up with 'Bid item 104'!$K$40 which the INDIRECT function will use to reference the cell.
As you drag it down, the value returned by the ROW() function will increment. If you place this in any other Row besides 1, modify the 103 so that the first ROW() + x = 104
They may be hard to see, but the single quotes need to be included inside the double quotes where appropriate so that they get built into the string.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.