PDA

View Full Version : controlling cell entries



mwvirk
07-22-2011, 04:02 PM
how can i stop user to enter values in 1 cell before filling some other cells.
e.g. i don't want user to give end-time in B cell before giving start-time in A cell because it is disturbing my sheet when it calculate time difference in C cell.
i don't mind is problem can be solved by conditional formatting or VBA code.

just to add more:
i have sheet where i got lot of entries manually and some of them are getting filled up automatically based on other cells and also some cells are getting updated using VBA code.
in this situation, how can i use conditional formatting or vba code?

thanks.

Aussiebear
07-23-2011, 01:11 AM
Set up a function to check if Start time has been entered if the User tries to enter an end time. If the start time cell is blank then a message box prompts them to fill the start cell first

mikerickson
07-23-2011, 01:39 AM
The Ignore Blank checkbox should be un-checked.

mwvirk
07-23-2011, 02:43 AM
you know excel is free cell program. i mean you can type anything anywhere in the sheet.
i want to control this.
like, in my sheet, if i fill up time without giving the down time then it is acceptable. i want to control it and want user to fill all required field accordingly.
look at this example (screen shot) where lot of information is missing but my report is accepting most of the values.

here you see can what formulas i'm using in other cells:
cell 'I' = dropdown menu to select type of problem
cell 'J' = based on I , calculating the type of problem (Partial / Full) using this formula:


=IF(ISERROR(LOOKUP(I106,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full","Full","Partial","Full","Partial","Full","Full","Partial","Partial","F ull","Full","Partial","Partial","Full","Partial"})),"",LOOKUP(I106,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full","Full","Partial","Full","Partial","Full","Full","Partial","Partial","F ull","Full","Partial","Partial","Full","Partial"}))

Cell 'K'= is automatically giving current date based on macro
cell 'L' = using must enter the time when problem was started.
cell 'M' = manual entry to give case id #
cell 'N' = drop down menu to select vendor
cell 'O' = automatically filling current time using macro
cell 'P' 'Q' 'R' 'S' = manual entry
cell 'T' = dropdown menu to select vendor
cell 'U' = filling date automatically using macro
cell 'V' = manual entry to fill 'Up' time
cell 'W' = calculating downtime (this is still pending as i'm still updating the macro)
cell 'X' = giving status (Pending / Resolved) using formula:


=IF(AND(I109<>"",V109<>""),"Resolved",IF(I109<>"","Pending",""))

and also using conditional formatting to fill colour (Red=Pending & Green=Resolved)

====================================

dependencies are:
> cell A B C D E F G H are 1 time entry and these should not be deleted or changed or left blank. but rows could be duplicated or deleted.
> cell I is having a dropdown menu and once a item is selected, based on a macro cell J and K are getting updated. i want user not to delete J & K but give user option to change item from dropdown menu.
> only when J K L are having entry then only N should be filled up (cell M is also using macro to get current time)
> T is again having dropdown menu and it should be selected only when all previous entries and filled. (U is using macro to get current date)
> V is manual entry to fill time when problem is fixed. and this should also check that all previous entries and done.

>> simple words: you can divide sheet in 3 parts:
1:- WHAT IS THE PROBLEM AND WHEN IT HAPPENED.
2:- WHOM IS WAS REPORTED AND WHEN.
3:- WHO FIXED THE ISSUE AND WHEN.

Aussiebear
07-24-2011, 02:15 AM
You can set those cells you wish a User to fill and the order they are filled in.