Extending formulas between sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-3186278%22%20slang%3D%22en-US%22%3EExtending%20formulas%20between%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3186278%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20multiple%20sheets%20that%20I%20would%20like%20to%20connect%20with%20a%20simple%20cell%3Dsheet1%20type%20formula.%20My%20problem%20is%20that%20when%20I%20extend%20it%20out%20the%20cell%20spacing%20is%20different%20between%20the%20sheets.%20For%20example%2C%20sheet%201%20has%20a%20spacing%20in%20between%20the%20cells%20I%20need%20of%2020%20and%20sheet%202%20has%2012%20spaces%20in%20between%20where%20I%20need%20the%20formula.%20So%20when%20I%20extend%20the%20formulas%20out%20they%20don't%20reference%20the%20correct%20cell.%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20create%20multiple%20cells%20that%20have%20the%20correct%20spacing%20to%20extend%20them%20that%20way%20but%20excel%20does%20not%20recognize%20the%20pattern%20I%20was%20trying%20to%20make.%20I%20really%20don't%20want%20to%20have%20to%20hand%20enter%20every%20reference%20so%20if%20anyone%20has%20something%20that%20can%20help%20I%20would%20appreciate%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3186278%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3191330%22%20slang%3D%22en-US%22%3ERe%3A%20Extending%20formulas%20between%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3191330%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1311839%22%20target%3D%22_blank%22%3E%40Jonathan65%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20could%20give%20the%20source%20cells%20or%20cell%20ranges%20a%20name%20(menu%20%22Formulas%20%7C%20Define%20Name%22)%20and%20then%20use%20this%20names%20in%20the%20other%20sheets%20as%20a%20reference%2C%20instead%20of%20using%20cell%20adresses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20idea%20if%20this%20makes%20sense%20in%20your%20scenario%2C%20but%20as%20there%20are%20no%20screenshots%20or%20example%20file%2C%20this%20would%20be%20my%20first%20advise.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have multiple sheets that I would like to connect with a simple cell=sheet1 type formula. My problem is that when I extend it out the cell spacing is different between the sheets. For example, sheet 1 has a spacing in between the cells I need of 20 and sheet 2 has 12 spaces in between where I need the formula. So when I extend the formulas out they don't reference the correct cell.

I have tried to create multiple cells that have the correct spacing to extend them that way but excel does not recognize the pattern I was trying to make. I really don't want to have to hand enter every reference so if anyone has something that can help I would appreciate it!

1 Reply

Hi @Jonathan65 

 

you could give the source cells or cell ranges a name (menu "Formulas | Define Name") and then use this names in the other sheets as a reference, instead of using cell adresses.

 

I have no idea if this makes sense in your scenario, but as there are no screenshots or example file, this would be my first advise.