Hi DerbyDad03 I have this formula in my excel spreedsheet

='Bid item 104'!$K$40

and i want my sheet to sequence as follows

='Bid item 104'!$K$40

='Bid item 105'!$K$40

='Bid item 106'!$K$40

='Bid item 107'!$K$40

But it keeps repeating as follows='Bid item 104'!$K$40

='Bid item 104'!$K$40

='Bid item 104'!$K$40

='Bid item 104'!$K$40I'm currently doing this manually but hoping to use a different formula and the drag function

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()+103to 104 and concatenate it with the rest of the string to end up with'Bid item 104'!$K$40which 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.

Ask Your Question

Weekly Poll