How to insert formula into selected cell range (with static value)

%3CLINGO-SUB%20id%3D%22lingo-sub-2694556%22%20slang%3D%22en-US%22%3EHow%20to%20insert%20formula%20into%20selected%20cell%20range%20(with%20static%20value)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2694556%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20is%20there%20any%20way%20to%20insert%20the%20same%20formula%20to%20a%20range%20of%20cells%20that%20already%20exist%20as%20static%20value%20(for%20example%2C%20insert%20the%20following%20%22%2F%40INDIRECT(ADDRESS(9%2CCOLUMN()))%22%20to%20selected%20cells%20that%20contains%20static%20numeric%20values%20like%20100%2C%20200%20etc.%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20do%20is%20to%20convert%20these%20static%20values%20using%20another%20FX%20rates%20looking%20up%20a%20table%20based%20on%20the%20formula%20above%20(e.g.%20row%209%2C%20and%20column%20number%20is%20whatever%20that%20the%20cell%20is%20referencing)%2C%20and%20at%20the%20same%20time%2C%20retain%20the%20original%20value%20inside%20the%20formula.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2694556%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-2694569%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20insert%20formula%20into%20selected%20cell%20range%20(with%20static%20value)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2694569%22%20slang%3D%22en-US%22%3ETo%20add%20on%2C%20what%20I%20am%20aiming%20to%20do%20is%20an%20efficient%20way%20to%20simply%20highlight%20the%20range%20of%20cells%20where%20the%20formula%20are%20to%20be%20automatically%20inserted%20to%20the%20back%20of%20the%20original%20static%20value%20(only%20for%20cells%20selected).%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2694649%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20insert%20formula%20into%20selected%20cell%20range%20(with%20static%20value)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2694649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1139947%22%20target%3D%22_blank%22%3E%40Paul_Y1225%3C%2FA%3E%26nbsp%3BWhat%20is%20the%20purpose%20of%20using%20the%20ADDRESS%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20entered%20in%2C%20for%20instance%2C%20cell%20B20%20the%20function%20%3CEM%3E%3CSTRONG%3EADDRESS(9%2CCOLUMN())%3C%2FSTRONG%3E%3C%2FEM%3E%20will%20return%20a%20text%20like%20%24B%249.%20Wrapping%20it%20in%20an%20INDIRECT%20function%20will%20return%20the%20value%20of%20cell%20B9.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECopying%20it%20down%20and%20across%20will%20return%20the%20values%20from%20row%209%20for%20each%20column.%20The%20same%20can%20be%20achieved%20by%20entering%20%3DB%249%20in%20B20%20and%20copy%20down%20and%20across.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, is there any way to insert the same formula to a range of cells that already exist as static value (for example, insert the following "/@INDIRECT(ADDRESS(9,COLUMN()))" to selected cells that contains static numeric values like 100, 200 etc.?

 

What I am trying to do is to convert these static values using another FX rates looking up a table based on the formula above (e.g. row 9, and column number is whatever that the cell is referencing), and at the same time, retain the original value inside the formula. Thanks!

2 Replies
To add on, what I am aiming to do is an efficient way to simply highlight the range of cells where the formula are to be automatically inserted to the back of the original static value (only for cells selected). Thanks!

@Paul_Y1225 What is the purpose of using the ADDRESS function?

 

When entered in, for instance, cell B20 the function ADDRESS(9,COLUMN()) will return a text like $B$9. Wrapping it in an INDIRECT function will return the value of cell B9.

 

Copying it down and across will return the values from row 9 for each column. The same can be achieved by entering =B$9 in B20 and copy down and across.