SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-795026%22%20slang%3D%22en-US%22%3ECopy%20cells%20which%20contain%20a%20reference%20to%20another%20worksheet%20-%20Excel%202010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795026%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20number%20of%20worksheets%20which%20are%20all%20the%20same%20apart%20from%20the%20title%20of%20the%20worksheet.%26nbsp%3B%20They%20are%20Invoice%201008%2C%20Invoice%201009%2C%20Invoice%201010%20etc.%26nbsp%3B%20I%20have%20a%20(totals)%20worksheet%20which%20collects%20data%20from%20each%20of%20the%20other%20worksheets.%26nbsp%3B%20My%20reference%20in%20the%20first%20row%20would%20be%20%3D'Invoice%201008'!N22.%26nbsp%3B%20The%20second%20row%20would%20be%20%3D'Invoice%201009'!N22%20and%20so%20on.%26nbsp%3B%20I%20would%20like%20to%20drag%20down%20to%20autofill%20the%20rows%20below%20with%20the%20reference%20for%20the%20next%20sheet%20but%20when%20I%20do%20the%20Invoice%20number%20does%20not%20change%20and%20the%20cell%20reference%20N22%20increases.%26nbsp%3B%26nbsp%3B%20I%20would%20like%20the%20Invoice%20number%20to%20change%20by%20one%20each%20time%20but%20the%20cell%20reference%20to%20stay%20the%20same.%26nbsp%3B%20I%20have%20tried%20selecting%20only%20the%20first%20row%20and%20dragging%20down%20and%20I%20have%20also%20tried%20selecting%20a%20series%20(first%203%20rows)%20and%20dragging%20down%20but%20can't%20get%20what%20I'm%20after.%26nbsp%3B%20Suggestions%20in%20plain%20English%20PLEASE%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-795026%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795188%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20cells%20which%20contain%20a%20reference%20to%20another%20worksheet%20-%20Excel%202010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795188%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388615%22%20target%3D%22_blank%22%3E%40Canterslowly%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20could%20be%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%22%3D'Invoice%20%22%26amp%3B1000%2BROW(A8)%26amp%3B%22'!%24N%2422%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20copy%20the%20list%20and%20paste%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795196%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20cells%20which%20contain%20a%20reference%20to%20another%20worksheet%20-%20Excel%202010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795196%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20-%20I%20saw%20something%20about%20the%20%25%20sign%20but%20wasn't%20sure%20where%20to%20put%20it.%26nbsp%3B%20I've%20only%20done%20very%20simple%20references%20and%20formulae%20before.%26nbsp%3B%20I'll%20try%20this%20solution.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Canterslowly
New 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.

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 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
7 Replies