Aug 27 2021 11:33 PM
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!
Aug 27 2021 11:36 PM
Aug 28 2021 12:18 AM
@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.