OK - this one was fun!!! The short answer is - VBA might be better. But to answer the question you asked...
You can't quite pull this off 100%, meaning that my solution suffers the same weakness as Seti's - it uses the TEXT function to display the values formatted the way you want instead of actually setting the cells' number format. This also implies that there is some type of formula in this cell to begin with - it's not a hard-coded value. If it is a hard-coded value you might find it preferable to hide the hard-coded values or move them off to the right or something and reference them via formula rather than typing all of this every time you wanted to change the numbers.
OK - so my answer is:
- Put yourself in cell B1
- Insert | Names | Define...
- In the box for the name put CELL7
- In the RefersTo box put =GET.CELL(7,A1) [Do not make the reference to A1 absolute.]
- Click ADD
Go back to the worksheet, move to
C1
- Insert | Names | Define...
- In the box for the name put CELL7_2
- In the RefersTo box put =GET.CELL(7,A1)
- Click ADD
You would need to repeat this process for each "relationship" to the format you want to copy, i.e. if you had a case where you want to copy for format from the cell one row above, do the above process, starting while you are in cell A2.
Then items that are one column to the right you can use the formula:
=TEXT(<your formula here>,CELL7)
For items that are two columns to the right of your key column:
=TEXT(<your formula here>,CELL7_2)
Note that you can still calculate on cells that have Text - Excel will re-cast them as values if you try to add or subtract with them.
HTH