Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compulsory Cells based on Condition
Hi,
I have an Excel spread sheet with 10 columns as follow: Date Start Time (default value is 00:00) End Time (default value is 00:00) Account Type Department Time (the time it has taken to complete the task) Additional Information Total ours for the week. Based on these columns I have various other functions and formulas pulling different reports and data. However I need help with the following. If a user enters a Start time none of the other fields in the same row should be left blank (i.e. Date, End Time, Account, Type and Department). I am not sure how to prompt the user to do that and when to prompt them. However these prompts should be relevant only to the row where the Start Time is different than 00:00. There should not be any prompts for the rows where the Start Time is 00:00. Also it would be good if the cursor can position itself on the next empty value the prompt is relating to. Many thanks in advance. Andy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compulsory Cells based on Condition
Personally, I'd use an extra column and put some sort of warning message in big,
bold, red letters the =if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values") And you may want to look at using Data|Form to let the users enter the data. axissm wrote: Hi, I have an Excel spread sheet with 10 columns as follow: Date Start Time (default value is 00:00) End Time (default value is 00:00) Account Type Department Time (the time it has taken to complete the task) Additional Information Total ours for the week. Based on these columns I have various other functions and formulas pulling different reports and data. However I need help with the following. If a user enters a Start time none of the other fields in the same row should be left blank (i.e. Date, End Time, Account, Type and Department). I am not sure how to prompt the user to do that and when to prompt them. However these prompts should be relevant only to the row where the Start Time is different than 00:00. There should not be any prompts for the rows where the Start Time is 00:00. Also it would be good if the cursor can position itself on the next empty value the prompt is relating to. Many thanks in advance. Andy -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compulsory Cells based on Condition
Hi Dave,
Thanks for your email. I have tried it but it doesnt not work. Can you please elaborate on this a little more. All I want to do is to check the value in cell B. If it is greater the 00:00 then I want when the user saves the form if cell D is empty a message to pop up and notify the user to enter some data. For various reasons I cannot user forms. Many thanks. Anguel "Dave Peterson" wrote: Personally, I'd use an extra column and put some sort of warning message in big, bold, red letters the =if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values") And you may want to look at using Data|Form to let the users enter the data. axissm wrote: Hi, I have an Excel spread sheet with 10 columns as follow: Date Start Time (default value is 00:00) End Time (default value is 00:00) Account Type Department Time (the time it has taken to complete the task) Additional Information Total ours for the week. Based on these columns I have various other functions and formulas pulling different reports and data. However I need help with the following. If a user enters a Start time none of the other fields in the same row should be left blank (i.e. Date, End Time, Account, Type and Department). I am not sure how to prompt the user to do that and when to prompt them. However these prompts should be relevant only to the row where the Start Time is different than 00:00. There should not be any prompts for the rows where the Start Time is 00:00. Also it would be good if the cursor can position itself on the next empty value the prompt is relating to. Many thanks in advance. Andy -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compulsory Cells based on Condition
My suggestion was not to use a macro--it just uses an adjacent cell and gives an
immediate warning that only disappears when all the cells in the range that are counted (with =counta()) have something in them. I don't understand what you mean by "it doesn't work", though. What formula did you use? And what cell got that formula? adi wrote: Hi Dave, Thanks for your email. I have tried it but it doesnt not work. Can you please elaborate on this a little more. All I want to do is to check the value in cell B. If it is greater the 00:00 then I want when the user saves the form if cell D is empty a message to pop up and notify the user to enter some data. For various reasons I cannot user forms. Many thanks. Anguel "Dave Peterson" wrote: Personally, I'd use an extra column and put some sort of warning message in big, bold, red letters the =if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values") And you may want to look at using Data|Form to let the users enter the data. axissm wrote: Hi, I have an Excel spread sheet with 10 columns as follow: Date Start Time (default value is 00:00) End Time (default value is 00:00) Account Type Department Time (the time it has taken to complete the task) Additional Information Total ours for the week. Based on these columns I have various other functions and formulas pulling different reports and data. However I need help with the following. If a user enters a Start time none of the other fields in the same row should be left blank (i.e. Date, End Time, Account, Type and Department). I am not sure how to prompt the user to do that and when to prompt them. However these prompts should be relevant only to the row where the Start Time is different than 00:00. There should not be any prompts for the rows where the Start Time is 00:00. Also it would be good if the cursor can position itself on the next empty value the prompt is relating to. Many thanks in advance. Andy -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compulsory Cells based on Condition
ps. Data|Form isn't a userform.
It's built into excel--just like Data|Text to columns or Edit|Replace or Format|Cells. Dave Peterson wrote: My suggestion was not to use a macro--it just uses an adjacent cell and gives an immediate warning that only disappears when all the cells in the range that are counted (with =counta()) have something in them. I don't understand what you mean by "it doesn't work", though. What formula did you use? And what cell got that formula? adi wrote: Hi Dave, Thanks for your email. I have tried it but it doesnt not work. Can you please elaborate on this a little more. All I want to do is to check the value in cell B. If it is greater the 00:00 then I want when the user saves the form if cell D is empty a message to pop up and notify the user to enter some data. For various reasons I cannot user forms. Many thanks. Anguel "Dave Peterson" wrote: Personally, I'd use an extra column and put some sort of warning message in big, bold, red letters the =if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values") And you may want to look at using Data|Form to let the users enter the data. axissm wrote: Hi, I have an Excel spread sheet with 10 columns as follow: Date Start Time (default value is 00:00) End Time (default value is 00:00) Account Type Department Time (the time it has taken to complete the task) Additional Information Total ours for the week. Based on these columns I have various other functions and formulas pulling different reports and data. However I need help with the following. If a user enters a Start time none of the other fields in the same row should be left blank (i.e. Date, End Time, Account, Type and Department). I am not sure how to prompt the user to do that and when to prompt them. However these prompts should be relevant only to the row where the Start Time is different than 00:00. There should not be any prompts for the rows where the Start Time is 00:00. Also it would be good if the cursor can position itself on the next empty value the prompt is relating to. Many thanks in advance. Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compulsory Cells based on Condition
How about something like this in the Thisworkbook module.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) With Sheets("Sheet1") If Range("B1") 0 And Range("D1") = "" Then Cancel = True MsgBox "Please complete entering data in D1" End If End With End Sub Gord Dibben MS Excel MVP On Thu, 7 Dec 2006 14:57:00 -0800, adi wrote: Hi Dave, Thanks for your email. I have tried it but it doesn’t not work. Can you please elaborate on this a little more. All I want to do is to check the value in cell B. If it is greater the 00:00 then I want when the user saves the form if cell D is empty a message to pop up and notify the user to enter some data. For various reasons I cannot user forms. Many thanks. Anguel "Dave Peterson" wrote: Personally, I'd use an extra column and put some sort of warning message in big, bold, red letters the =if(b2=0,"",if(counta(c2:i2)=8,"","<-- Please enter more values") And you may want to look at using Data|Form to let the users enter the data. axissm wrote: Hi, I have an Excel spread sheet with 10 columns as follow: Date Start Time (default value is 00:00) End Time (default value is 00:00) Account Type Department Time (the time it has taken to complete the task) Additional Information Total ours for the week. Based on these columns I have various other functions and formulas pulling different reports and data. However I need help with the following. If a user enters a Start time none of the other fields in the same row should be left blank (i.e. Date, End Time, Account, Type and Department). I am not sure how to prompt the user to do that and when to prompt them. However these prompts should be relevant only to the row where the Start Time is different than 00:00. There should not be any prompts for the rows where the Start Time is 00:00. Also it would be good if the cursor can position itself on the next empty value the prompt is relating to. Many thanks in advance. Andy -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
is it possible to make certain cells required based on criteria | Excel Worksheet Functions | |||
Conditional Format based on 3 other cells. | Excel Discussion (Misc queries) | |||
Macro to highlight cells based on content | Excel Worksheet Functions | |||
how do i add in numbers automatically based on adjacent cells cont | Excel Discussion (Misc queries) |