Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?

Poke wrote:
Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?


If you could use a letter other than "r" or "c", then you could
Insert/Name/Define.... Then a letter (say, Q) and in the "refers to"
blank, put in =8.

then in your cells, you would put in =Q+1 or Q+2 and get the answer 9 or 10

Beege

Unfortunately the Text value must be R and be able to have a total drawn from
it at the end column for each row.

Thank you for your quick response though

"Beege" wrote:

Poke wrote:
Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?


If you could use a letter other than "r" or "c", then you could
Insert/Name/Define.... Then a letter (say, Q) and in the "refers to"
blank, put in =8.

then in your cells, you would put in =Q+1 or Q+2 and get the answer 9 or 10

Beege

if the R will always be the first character
and nothing would be entered without an R
if Mon were column B
in totals cell enter
=if(len(B2)0, if
len(B2)=1,8,8+value(right(b2,len(b2)-1))+if(len(C2......{for the week}
Similar equations can be used with more complex combinations

"Poke" wrote:

Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?

The following ugly array formula works. In this example, the data is in B9:H9

=SUM(IF(ISERROR(FIND("R",UPPER(B9:H9))),0,8+IF(LEN (B9:H9)1,INT(RIGHT(B9:H9,LEN(B9:H9)-1)),0)))

To enter an array formula, copy & paste the formula above into a cell. You
may have to adjust for the text wrapping above. Instead of Enter, you press
{Ctrl}{Shift}{Enter} together to enter the array formula. If you do it
correctly, Excel will display curly braces around the formula.

Hope this helps,

Hutch

"Poke" wrote:

Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?

Thanks to all that replied.

Tom you rock! This is exactly what I was looking for! It works perfectly. I
didn't know about the whole CTRL-SHIFT-ENTER aspect. I'm a little new to
excel. Gee can you tell? HA!

Buddy you're awesome!

Thanks

"Tom Hutchins" wrote:

The following ugly array formula works. In this example, the data is in B9:H9

=SUM(IF(ISERROR(FIND("R",UPPER(B9:H9))),0,8+IF(LEN (B9:H9)1,INT(RIGHT(B9:H9,LEN(B9:H9)-1)),0)))

To enter an array formula, copy & paste the formula above into a cell. You
may have to adjust for the text wrapping above. Instead of Enter, you press
{Ctrl}{Shift}{Enter} together to enter the array formula. If you do it
correctly, Excel will display curly braces around the formula.

Hope this helps,

Hutch

"Poke" wrote:

Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?