This sheet is going to be the death of me!

[Mechroneal]

I have a column that contains reference numbers. They are always ten digits long, but may begin with a zero. I want excel to not accept anything not exactly ten digits. The closest thing I could think of was validation requiring a numeric value greater than 999,999,999. This kind of works, but if the number starts with zero, it won't. Any suggestions?

Also, I have a drop down list with 4 choices. Each choice has a set cost, and I need a cell to show the total value for this column. For instance choice 1=$, choice 2=$10. I think I somehow need to store the value of each cell in this column as a variable, convert the variables to prices, then total. Is this the right way, and if so, how do I do it?

And last but not least, I want to be able to enter time as a 4 digit number, for instance "1430", and have it convert automatically to "2:30", with no AM/PM. Can this be done?From what I can tell, Excel requires a colon in order to understand time. If you use the format "hh:mm" and enter "2:30" it will take it. However, I can't find a way for it to translate from military to standard without the AM/PM tag. I even tried to strip it by doing a text to columns and that didn't work.[/img]

 

[just_jon]

Welcome to MrExcel - I *hate* date/time, but here goes - for #3, you could use this macro to monitor your cell [ assumes entering the 1430, etc into A1 ]

<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> Intersect([A1], Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Value > 1200 <SPAN style="color:#00007F">Then</SPAN>
    Target.Value = Target.Value - 1200
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Target.NumberFormat = "##"":""00"
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

For #2, since you know you have a set and limited number of choices in the range, maybe:

=COUNTIF(A1:A10,1)*10+COUNTIF(A1:A10,2)*15+COUNTIF(A1:A10,3)*20+COUNTIF(A1:A10,4)*25

Would sum range A1:A10 with 1=10, 2=15, 3=20 and 4's = 25.

For #1, assuming you have the reference numbers formatted as Text, use this in Data Validation:

=LEN(B1)=10

Assumes B1 is to be validated for exactly 10 characters. If also must be numeric only --

=ISNUMBER(B1)*(LEN(B1)=10)

 

[Mechroneal]

?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect([A1], Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value > 1200 Then
Target.Value = Target.Value - 1200
End If
Target.NumberFormat = "##"":""00"
Application.EnableEvents = True
End Sub


For #2, since you know you have a set and limited number of choices in the range, maybe:

=COUNTIF(A1:A10,1)*10+COUNTIF(A1:A10,2)*15+COUNTIF(A1:A10,3)*20+COUNTIF(A1:A10,4)*25

Would sum range A1:A10 with 1=10, 2=15, 3=20 and 4's = 25.

For #1, assuming you have the reference numbers formatted as Text, use this in Data Validation:

=LEN(B1)=10

Assumes B1 is to be validated for exactly 10 characters. If also must be numeric only --

=ISNUMBER(B1)*(LEN(B1)=10)

Wow. I really hate to admit it, but I have no idea what you are talking about. Can this be done without macros?

 

[Norie]

Mechroneal

I think your first 2 questions can be solved without macros but as far as I can see your 3rd definitely can't.

For question 1, why not check the length of the entry and also check it's a number (if that's the requirement) using LEN and ISNUMBER?

For question 2, not 100% sure what you want here. I think it could be done without macros but I'm not sure.

 

[tactps]

Firstly - John is a hero with VBA.

Secondly - non VBA solutions are available

Issue 1:
Format the cell as text, then use data validation:

Allow:
text length
equal to
10

Issue 2:
It sounds like the "choose" or "vlookup" functions can be used but I'd need more detail (preferable use the THML maker and explanation)

Issue 3:
=IF(B5>999,TIME(LEFT(B5,2),RIGHT(B5,2),0),TIME(LEFT(B5,1),RIGHT(B5,2),0))
Format the cell as h:mm
[EDIT]: This must be done in a different cell, so it is not your stated requirement. It must be done this way if you want to avoid VBA.

 

[Mechroneal]

Book1
	A	B	C	D	E	F	G
1	Name	Item	Qty	TotalPrice		Burger$1.99
2	Bob	Burger$1.99	1			DoubleBurger$2.99
3	Bill	DoubleBurger$2.99	3			TripleBurger$3.99
Sheet1

Here is an example of what I am working with. Column B has choices in the drop-down from column F. I need the user to choose the item in B, enter the quantity in C, and have the spreadsheet calculate BxC and put it in D.

I hope no one was offended, in fact I meant quite the obvious. I am pretty new to excel, and what he said was totally beyond my comprehension.

 

[tactps]

Assuming your drop-downs are from a validation and always contain a "$":

In cell D2:
=MID(B2,SEARCH("$",B2)+1,LEN(B2))*C2

A better idea is to have your items and prices in separate columns, and then look up the prices when the description is entered.

[EDIT]: No offence taken - I only meant that if a VBA solution is available,, John's your man!!!! It was not a stab at you - just paying homage to John.

 

[Mechroneal]

A better idea is to have your items and prices in separate columns, and then look up the prices when the description is entered.

My goal is to keep data condensed as much as possible. Can you explain this a little more, I think I am misunderstanding.

 

[tactps]

In your example, I would put this into column F:
Burger
DoubleBurger
Triple Burger

and this in column G:
$1.99
$2.99
$3.99

Then use your drop-down in column B on column F (description only)

Insert a column after column B. In this new column insert:
=vlookup(B2,g:h,2,false)
which will look up the price for you

In column D you will have the prices.

Therefore column E will use the formula:
=c2*d2.

To update prices, you can easily do this by changing column H.