Number format based on another cells format

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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
ABCDEF
31.25Fraction[1/4]11/4Fraction[1/4]#?/?
41.5Fraction[1/4]11/2Dollar$#,##0.00
51.75Fraction[1/4]13/4
62Fraction[1/4]2
72.25Fraction[1/4]21/4
82.5Fraction[1/4]21/2
91.25Dollar$1.25
101.5Dollar$1.50
111.75Dollar$1.75
122Dollar$2.00
132.25Dollar$2.25
142.5Dollar$2.50
Sheet1
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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
ABCDEFG
1SourceFormatCopyFormatCopyFormat2SourceFormatCopyFormatCopyFormat2
2113/523/51.001.602.60
311/4231/41.252.003.25
411/222/541.502.403.90
513/424/545/91.752.804.55
6231/551/52.003.205.20
721/433/556/72.253.605.85
821/2461/22.504.006.50
923/442/571/72.754.407.15
10344/574/53.004.807.80
11Firstcolumn'sformulaSecondColumn'sFormula
12=TEXT(A11*1.6,CELL7)=TEXT(E11*2.6,CELL7_2)
13
14Cell7is:=GET.CELL(7,A1)whenlocatedinB1
15Cell7_2is:=GET.CELL(7,A1)whenlocatedinC1
16
Sheet1



HTH
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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. :biggrin: )

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

New Member
Joined
May 9, 2002
Messages
35

ADVERTISEMENT

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
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,327
Messages
5,769,460
Members
425,550
Latest member
Bonebeast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top