Link cells to same location on multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1495563%22%20slang%3D%22en-US%22%3ELink%20cells%20to%20same%20location%20on%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495563%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20paste%20data%20from%201%20sheet%20to%20multiple%20sheets%20so%2C%3C%2FP%3E%3CP%3EI%20have%20a%20list%20on%20sheet%201%20each%20item%20on%20sheet%201%20goes%20to%20the%20same%20location%20on%20different%20sheets.%3C%2FP%3E%3CP%3EOn%20sheet%201%2C%20cell%20A1%20copies%20to%20sheet%202%2C%20cell%20A1%20but%20the%20data%20for%20sheet%203%20cell%20A1%20data%20comes%20from%20sheet%201%20cell%20A2%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20can%20do%20this%201%20at%20a%20time%20but%20is%20there%20a%20short%20cut%20to%20do%20multiple%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%202016%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1495563%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495737%22%20slang%3D%22en-US%22%3ERe%3A%20Link%20cells%20to%20same%20location%20on%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713051%22%20target%3D%22_blank%22%3E%40Headmelted%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20give%20a%20bigger%20picture%20of%20what%20it%20is%20you're%20doing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%2C%20don't%20just%20repeat%20what%20you've%20said%2C%20but%20%3CSTRONG%3Edescribe%20the%20context%3C%2FSTRONG%3E%3A%20what%20is%20the%20%3CEM%3Eunderlying%20goal%20or%20objective%3C%2FEM%3E%20being%20served%20by%20your%20series%20of%20sheets%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20for%20asking%20that%20is%20that%20there%20are%20often%20multiple%20ways%20to%20accomplish%20things%20in%20Excel%3B%20what%20makes%20sense%20in%20a%20particular%20case%20will%20often%20depend%20on%20the%20bigger%20picture.%20What%20kind%20of%20information%20is%20it%20that%20you're%20working%20with%3F%20What's%20in%20the%20various%20rows%20on%20Sheet%201%3B%20what%20differentiates%20the%20data%20in%20cell%20A1%20from%20the%20data%20in%20cell%20A2%20and%20so%20forth%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it's%20possible%2C%20without%20disclosing%20any%20confidential%20information%2C%20could%20you%20post%20a%20sample%20of%20your%20workbook%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1496314%22%20slang%3D%22en-US%22%3ERe%3A%20Link%20cells%20to%20same%20location%20on%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1496314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20illustrating%20my%20point%20that%20there%20are%20multiple%20ways%20to%20do%20things%20in%20Excel.%20%5Bsmiley%20face%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495906%22%20slang%3D%22en-US%22%3ERe%3A%20Link%20cells%20to%20same%20location%20on%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713051%22%20target%3D%22_blank%22%3E%40Headmelted%3C%2FA%3E%26nbsp%3B%20So%20I%20assume%20you%20are%20trying%20to%20do%20something%20like%20A10%20%3D%20A9%20%2B%20previousSheet!A10.%20But%20there%20is%20no%20way%20to%20reference%20something%20like%20thisSheet-1%20in%20a%20formula.%26nbsp%3B%20Some%20options%20you%20have%20include%20VBA%20or%20lookup%20list%20for%20sheet%20names.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20VBA%20you%20could%20do%20a%20number%20of%20options%20but%20here%20is%20simple%20one%20%5BEDIT%3A%20changed%20this%20to%20be%20%22Volatile%22%20so%20it%20auto-calculates%20and%20doesn't%20require%20you%20to%20select%20the%20cell%5D%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20pSheet()%0AOn%20Error%20GoTo%20E%0A%20%20%20%20Application.Volatile%0A%20%20%20%20With%20Application.Caller.Parent%0A%20%20%20%20%20%20%20%20pSheet%20%3D%20.Parent.Sheets(.Index%20-%201).Range(Application.Caller.Address)%0A%20%20%20%20End%20With%0AExit%20Function%0AE%3A%0A%20%20%20%20pSheet%20%3D%20%22N%2FA%22%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20you%20just%20use%20%3DA9%20%2B%20prevSheetVal()%3C%2FP%3E%3CP%3EBut%20if%20you%20are%20not%20comfortable%20with%20VBA%20(I%20highly%20suggest%20you%20learn)%20or%20prefer%20to%20avoid%20macros%20to%20make%20the%20sheet%20more%20universal%20and%20not%20have%20issues%20with%20permissions%20you%20can%20also%20do%20this%20using%20a%20look%20up%20table.%3C%2FP%3E%3CP%3ESo%20on%20another%20sheet%20let's%20call%20sheetNames%20you%20list%20off%20the%20names%20of%20all%20the%20sheets%20in%20column%20A%3C%2FP%3E%3CP%3Ethen%20use%20an%20indirect%20function%20like%20the%20following%20to%20in%20this%20case%20add%20A9%20on%20this%20sheet%20to%20A10%20on%20the%20previous%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DA9%20%2B%20INDIRECT(%22'%22%26amp%3BINDEX(sheetNames!A%3AA%2CSHEET()-1)%26amp%3B%22'!A10%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I need to paste data from 1 sheet to multiple sheets so,

I have a list on sheet 1 each item on sheet 1 goes to the same location on different sheets.

On sheet 1, cell A1 copies to sheet 2, cell A1 but the data for sheet 3 cell A1 data comes from sheet 1 cell A2 

I know I can do this 1 at a time but is there a short cut to do multiple?

 

Excel 2016

3 Replies

@Headmelted 

 

Could you give a bigger picture of what it is you're doing?

 

That is, don't just repeat what you've said, but describe the context: what is the underlying goal or objective being served by your series of sheets?

 

The reason for asking that is that there are often multiple ways to accomplish things in Excel; what makes sense in a particular case will often depend on the bigger picture. What kind of information is it that you're working with? What's in the various rows on Sheet 1; what differentiates the data in cell A1 from the data in cell A2 and so forth?

 

If it's possible, without disclosing any confidential information, could you post a sample of your workbook?

 

@Headmelted  So I assume you are trying to do something like A10 = A9 + previousSheet!A10. But there is no way to reference something like thisSheet-1 in a formula.  Some options you have include VBA or lookup list for sheet names.  

Using VBA you could do a number of options but here is simple one [EDIT: changed this to be "Volatile" so it auto-calculates and doesn't require you to select the cell]:

 

 

 

Function pSheet()
On Error GoTo E
    Application.Volatile
    With Application.Caller.Parent
        pSheet = .Parent.Sheets(.Index - 1).Range(Application.Caller.Address)
    End With
Exit Function
E:
    pSheet = "N/A"
End Function

 

 

 

then you just use =A9 + pSheet()

But if you are not comfortable with VBA (I highly suggest you learn) or prefer to avoid macros to make the sheet more universal and not have issues with permissions you can also do this using a look up table.

So on another sheet let's call sheetNames you list off the names of all the sheets in column A

then use an indirect function like the following to in this case add A9 on this sheet to A10 on the previous sheet:

 

 

 

=A9 + INDIRECT("'"&INDEX(sheetNames!A:A,SHEET()-1)&"'!A10")

 

 

 

 

@mtarler 

 

Thank you for illustrating my point that there are multiple ways to do things in Excel. [smiley face]