Splitting cell values

**Harrytheb** · 05-06-2013, 11:05 AM

Greetings, looking for help to split a cell value. I want to be able to separate value to use lower down the sheet. I arrive at the value using:

= Frequencies!E6&"/"&FrequenciesF6

This give me a cell value of:
3/90

I want to be able to access the separate values, in this case 3 and 90 for use in a conditional format lower down.

Thanks in advance.
H

**Marcol** · 05-06-2013, 11:50 AM

The two options in this workbook, dragged across, will give you what you are asking for any length of numbers, but why?

What are you ultimately trying to do?

**Harrytheb** · 05-06-2013, 12:59 PM

Hi, thanks for the quick response.

I am a have two formulas that I am trying to use. Below the 3/90 value aforementioned a have a formula:
=COUNTIFS(Range,"<="&TODAY(),Range,">="&TODAY()-30
I am trying to link the -30 value in the formula to the 90 value in the cell above.

I am also trying to add a conditional formatting to the countif cell value so that it is red if zero, and blue if below 75% of the 3 value in the cell above.

Hope this is explains the meanderings of my slow brain! Again thanks for taking the time to help out.
Rgds H

**FDibbins** · 05-06-2013, 12:59 PM

Why not just use the references on their own?

= Frequencies!E6
=Frequencies!F6

edit: oohh ok, then why not just use = Frequencies!E6&/Frequencies!F6 and format as fractions?

edit2: helps if you read properly lol...
=int(Frequencies!F6&/Frequencies!E6)
If F6 = 90 and E6 = 3, that will give you the 30 you want?

**Harrytheb** · 05-06-2013, 01:12 PM

Hi FDibbins, I have tried the simple option but the conditional formatting does not like using cells from other worksheets. Works fine in the other formula above.

**FDibbins** · 05-06-2013, 01:16 PM

No, you are correct, CF in 2007 wont ref other worksheets....unless you give the range a name and then ref the name instead

**Harrytheb** · 05-06-2013, 01:34 PM

Have used a row of hidden cells with the reference taken from the Frequencies tab, this seems to work best for now. for both formulas that I am trying to create. Sometimes look at the screen for too long removes my logical thought!! Thanks Marcol and FDibbins for your help.
Rgds H