SOLVED

Copy cells which contain a reference to another worksheet - Excel 2010

Copper Contributor

I have a workbook with a number of worksheets which are all the same apart from the title of the worksheet.  They are Invoice 1008, Invoice 1009, Invoice 1010 etc.  I have a (totals) worksheet which collects data from each of the other worksheets.  My reference in the first row would be ='Invoice 1008'!N22.  The second row would be ='Invoice 1009'!N22 and so on.  I would like to drag down to autofill the rows below with the reference for the next sheet but when I do the Invoice number does not change and the cell reference N22 increases.   I would like the Invoice number to change by one each time but the cell reference to stay the same.  I have tried selecting only the first row and dragging down and I have also tried selecting a series (first 3 rows) and dragging down but can't get what I'm after.  Suggestions in plain English PLEASE :)  Thank you.

2 Replies

Hello @Canterslowly,

 

That could be like this:

 

="='Invoice "&1000+ROW(A8)&"'!$N$22" 

 

Then copy the list and paste values.

best response confirmed by Canterslowly (Copper Contributor)
Solution

Thank you - I saw something about the % sign but wasn't sure where to put it.  I've only done very simple references and formulae before.  I'll try this solution. 

 

@PReagan 

1 best response

Accepted Solutions
best response confirmed by Canterslowly (Copper Contributor)
Solution

Thank you - I saw something about the % sign but wasn't sure where to put it.  I've only done very simple references and formulae before.  I'll try this solution. 

 

@PReagan 

View solution in original post