SOLVED

[SOLVED]Using another sheet like function

%3CLINGO-SUB%20id%3D%22lingo-sub-1262125%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20another%20sheet%20like%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F599463%22%20target%3D%22_blank%22%3E%40Phobosd%3C%2FA%3E%26nbsp%3BWhat%20you%20describe%20is%20achieved%20by%20User%20Defined%20Functions%20(requires%20VBA%20programming)%20or%20by%20what%20is%20often%20referred%20to%20as%20%22helper%22%20columns%2C%20where%20each%20column%20contains%20a%20formula%20that%20solves%20part%20of%20the%20problem.%20The%20attached%20workbook%20contains%20a%20%3CSTRONG%3E%3CEM%3Every%20simple%3C%2FEM%3E%3C%2FSTRONG%3E%20example%20of%20both.%20You%20only%20need%20to%20imagine%20that%20the%20three%20calculations%20in%20this%20example%20are%20in%20fact%20the%20%22hard%20calculations%20too%20complicated%20to%20be%20described%20with%20one%20formula%22%20that%20you%20mentioned.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262171%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20another%20sheet%20like%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F599463%22%20target%3D%22_blank%22%3E%40Phobosd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20perform%20calculations%20column%20by%20column%20for%20each%20row%20in%20source%20sheet.%20Thus%20each%20row%20in%20calculation%20sheet%20will%20be%20mapped%20on%20each%20row%20in%20source%20sheet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262172%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20another%20sheet%20like%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20confirmed%20my%20suggestion%20about%20the%20UserDefinedFunction%20to%20solve%20this%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262175%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20another%20sheet%20like%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262175%22%20slang%3D%22en-US%22%3EThanks!%20I'll%20think%20about%20it%20more...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1261890%22%20slang%3D%22en-US%22%3E%5BSOLVED%5DUsing%20another%20sheet%20like%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1261890%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20general%20question%20about%20Excel.%20No%20certain%20examples%20i%20can%20attach.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%202%20sheets.%20%22Sheet%201%22%20contains%20input%20data%20columns%20and%20result%20column.%20%22Sheet%202%22%20calculates%20result%20using%20many%20operations%20with%20input%20data.%20Is%20there%20any%20way%20to%20put%20data%26nbsp%3Brow%20by%20row%20from%20sheet%201%20to%20sheet%202%20and%20return%20results%20to%20sheet%201%3F%20See%20pic%20for%20more%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3EP.S.%20Add%20example%20of%20what%20i%20am%20talking%20about.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180214i5DE739CBF1AD69A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Excel.png%22%20alt%3D%22Excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1261890%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
Highlighted
New Contributor

Hi!

 

It is general question about Excel. No certain examples i can attach.

 

I have 2 sheets. "Sheet 1" contains input data columns and result column. "Sheet 2" calculates result using many operations with input data. Is there any way to put data row by row from sheet 1 to sheet 2 and return results to sheet 1? See pic for more information.

 

Thanks.

P.S. Add example of what i am talking about.
Excel.png

4 Replies
Highlighted
Best Response confirmed by Phobosd (New Contributor)
Solution

@Phobosd What you describe is achieved by User Defined Functions (requires VBA programming) or by what is often referred to as "helper" columns, where each column contains a formula that solves part of the problem. The attached workbook contains a very simple example of both. You only need to imagine that the three calculations in this example are in fact the "hard calculations too complicated to be described with one formula" that you mentioned.

@Phobosd 

As variant you may perform calculations column by column for each row in source sheet. Thus each row in calculation sheet will be mapped on each row in source sheet.

 

Highlighted

@Riny_van_Eekelen 

 

Thank you!

 

You confirmed my suggestion about the UserDefinedFunction to solve this problem.

Highlighted
Thanks! I'll think about it more...