Forum Discussion
Copy cells which contain a reference to another worksheet - Excel 2010
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.
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.
2 Replies
Hello Canterslowly,
That could be like this:
="='Invoice "&1000+ROW(A8)&"'!$N$22"
Then copy the list and paste values.
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.
