Aug 08 2019 05:02 AM
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.
Aug 08 2019 06:31 AM
Hello @Canterslowly,
That could be like this:
="='Invoice "&1000+ROW(A8)&"'!$N$22"
Then copy the list and paste values.
Aug 08 2019 06:33 AM
SolutionThank 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.
Aug 08 2019 06:33 AM
SolutionThank 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.