Number format based on another cells format

[PeterBrazel]

I have a table of markets some of which trade in qtrs and some in $.
Within my current sheet a cell carries a lookup function that returns that format. Now depending on the type of that format I want various other cells within this sheet to carry the same format.

Can I do this without using VBA.

E.G If the format in cell F5=fraction [1/4] then format this cell in fraction 1/4.

 

[Seti]

Would something like this work for you? You need to have your base values stored and the format key stored somewhere. I have made all values consecutive cells, but it is not required. Then use the text function to get the formatting.

Book9
	A	B	C	D	E	F
3	1.25	Fraction[1/4]	11/4		Fraction[1/4]	#?/?
4	1.5	Fraction[1/4]	11/2		Dollar	$#,##0.00
5	1.75	Fraction[1/4]	13/4
6	2	Fraction[1/4]	2
7	2.25	Fraction[1/4]	21/4
8	2.5	Fraction[1/4]	21/2
9	1.25	Dollar	$1.25
10	1.5	Dollar	$1.50
11	1.75	Dollar	$1.75
12	2	Dollar	$2.00
13	2.25	Dollar	$2.25
14	2.5	Dollar	$2.50
Sheet1

 

[PeterBrazel]

Thks but this is not quite what I wanted.

The value and its format is accessed from the table of markets in a seperate sheet. I want this cells format only to be carried over to a number of different cells within the current sheet.

So on this sheet I have a cell that tells me the minimum tick value of a contract e.g. 1/4 or 0.10. Now I want this format to be applied to my entry price calculation, to my stop loss calculation and various other cells on this worksheet.

Maybe I can...maybe I cannot...help appreciated.

 

[Greg Truby]

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:

1. Put yourself in cell B1
2. Insert | Names | Define...
3. In the box for the name put CELL7
4. In the RefersTo box put =GET.CELL(7,A1) [Do not make the reference to A1 absolute.]
5. Click ADD

Go back to the worksheet, move to C1

1. Insert | Names | Define...
2. In the box for the name put CELL7_2
3. In the RefersTo box put =GET.CELL(7,A1)
4. 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.

Copy Formats without VBA.xls
	A	B	C	D	E	F	G
1	SourceFormat	CopyFormat	CopyFormat2		SourceFormat	CopyFormat	CopyFormat2
2	1	13/5	23/5		1.00	1.60	2.60
3	11/4	2	31/4		1.25	2.00	3.25
4	11/2	22/5	4		1.50	2.40	3.90
5	13/4	24/5	45/9		1.75	2.80	4.55
6	2	31/5	51/5		2.00	3.20	5.20
7	21/4	33/5	56/7		2.25	3.60	5.85
8	21/2	4	61/2		2.50	4.00	6.50
9	23/4	42/5	71/7		2.75	4.40	7.15
10	3	44/5	74/5		3.00	4.80	7.80
11		Firstcolumn'sformula					SecondColumn'sFormula
12		=TEXT(A11*1.6,CELL7)					=TEXT(E11*2.6,CELL7_2)
13
14	Cell7is:	=GET.CELL(7,A1)whenlocatedinB1
15	Cell7_2is:	=GET.CELL(7,A1)whenlocatedinC1
16
Sheet1

HTH

 

[PeterBrazel]

Thank you.

I am glad that you enjoyed it because you certainly have put some work into this.

I think I understand it but have been called away for now. Hope to get back to it later in the day.

How hard would the VBA be to an almost non VBA user.

Thks,

 

[Greg Truby]

Considering I didn't start using Excel until '97, finding an old Excel 4 command that would allow use of the "named formula"-based approach was a fun challenge for me, but then maybe I have an odd sense of "fun"? (But at least around here I'm not alone. )

As far as VB goes - a few problem / solution scoping questions:

1. What's your preferred trigger?
   • A "click" event, i.e. assign the macro to<ul>
   • a toolbar button, or
   • a button on the WS, or
   • a custom menu item
2. A change event - update number formats when you change the value of a specific cell or range of cells?
3. Movement-based - check when you select a certain cell or cells?

[*]What range comprises the "Format Leader"? One or a few specific cells? Or an entire column (or row) of cells are leaders?[*]What range comprises the "Format Followers"? Again one or a few specific cells? Or entire columns (or rows) of cells are followers?[/list]

 

[PeterBrazel]

Thank you.

I think the following would apply in this case.

"A change event - update number formats when you change the value of a specific cell or range of cells?"

Thks

Peter

 

[Greg Truby]

That answers question #1. How about #2 & #3?

 

[PeterBrazel]

Sorry.

#2 = A specific cell
#3 = A few cells located in differrent parts of the sheet i.e. different columns and different rows.

Thanks

 

[Greg Truby]

Note: the following assumes that <ul>[*]your trigger range is not the same as your format leader range - you can adjust everything accordingly if they are one and the same and[*]you know how to define named ranges[/list]

Here's the Process:

1. Set up the Trigger
   • Select the cell (or cells) that will be your trigger (a change in the value of this cell(s) kicks off the macro).
   • Name this range FormatTrigger (remember no spaces allowed in range names).
2. Set up the Leader
   • Select the cell (only need one cell here) that will be your format leader
   • Name it FormatLeader
3. Set up the Followers
   • Select all of the cells that will be your format followers (hold down your Ctrl Key or use Shift+F8 while you mouseketeer if non-contiguous cells involved)
   • Name it FormatFollowers
4. Add Code to the Worksheet
   • Right-Click on the tab for the Worksheet involved. (The worksheet involved in this case would be the sheet on which the range FormatTriggeris located.)
   • The last item on the popup menu should be View Code, pick it.
   • At the top of the code window you'll see two drop-down listboxes. The left list should say (General) click and select Worksheet
   • The VB Editor will automatically start you off with the SelectionChange event procedure. In the right listbox click and pick Change. You should now be in a procedure called Worksheet_Change
   • Copy and paste the code below so that your routine looks like the code below.
   • If you want to, you can highlight and delete the empty Worksheet_SelectionChange procedure that the VB Editor created for you.

Code for the Worksheet:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [FormatTrigger]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        [FormatFollowers].NumberFormat = [FormatLeader].NumberFormat
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>