Solved Sheet Sequencing for Microsoft excel 2013

May 10, 2013 at 09:21:33
Specs: Windows 7
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$40

I'm currently doing this manually but hoping to use a different formula and the drag function

See More: Sheet Sequencing for Microsoft excel 2013

Report •

May 10, 2013 at 11:36:00
✔ Best Answer
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.

Report •
Related Solutions

Ask Question