excel formula - multiples sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1603895%22%20slang%3D%22en-US%22%3Eexcel%20formula%20-%20multiples%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603895%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20a%20formula%20that%20always%20links%20a%20cell%20to%20a%20given%20cell%20of%20the%20previous%20sheet%3F%3C%2FP%3E%3CP%3EFo%20instance%20I'm%20creating%20a%20lot%20of%20sheets%20on%20the%20same%20files%2C%20and%20every%20sheet%20reports%20a%20number%20in%20cell%20B3%20that%20is%20%2B1%20compared%20to%20the%20number%20shown%20in%20the%20B3%20of%20the%20previous%20sheet.%3C%2FP%3E%3CP%3ESo%20instead%20linking%20manully%20every%20single%20time%20B3%20of%20sheet%202%20with%20%3CEM%3E%3D'sheet1'B3%2B1%2C%20%3C%2FEM%3Eis%20there%20a%20formula%20to%20avoid%20the%20manual%20plug%20in%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603944%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formula%20-%20multiples%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F766974%22%20target%3D%22_blank%22%3E%40michelesabatini%3C%2FA%3E%26nbsp%3Bwithout%20testing%20I%20believe%20the%20formula%20below%20could%20solve%20your%20problem.%3C%2FP%3E%3CP%3E%3DINDIRECT(%22sheet%22%26amp%3BREPLACE(MID(CELL(%22filename%22%2C%20A1)%2C%20FIND(%22%5D%22%2C%26nbsp%3BCELL(%22filename%22%2C%20A1))%2B1%2C%2031)%2C%20%22sheet%22%2C%20%22%22)-1%26amp%3B%22!B3%22)%2B1%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi all,

 

is there a formula that always links a cell to a given cell of the previous sheet?

Fo instance I'm creating a lot of sheets on the same files, and every sheet reports a number in cell B3 that is +1 compared to the number shown in the B3 of the previous sheet.

So instead linking manully every single time B3 of sheet 2 with ='sheet1'B3+1, is there a formula to avoid the manual plug in formula?

 

Thanks

1 Reply
Highlighted

@michelesabatini without testing I believe the formula below could solve your problem.

=INDIRECT("sheet"&REPLACE(MID(CELL("filename", A1), FIND("]", CELL("filename", A1))+1, 31), "sheet", "")-1&"!B3")+1