is it possible to restrict a cell in such a way that it is impossible to type data into it, but rather select data from a drop down, through validation?
View 9 RepliesAmongst the several sheets contained in my workbook, there is one called 'Inspection Report'. Users fill in whatever data is required in the other sheets, and once they get to this one, they are supposed to enter a number from 1 to 3 into Cell X1 (which is currently selected) before they select anything else. Unfortunately, I am currently unable to stop them from doing what they should not be doing.
So, I would like to have a notification of some sort pop up into their face if they click or move the selection anywhere else while Cell X1 is still empty. Something like a validation would be nice.
I have a cell with data validation: list, that allows values contained in named range. I want to disallow to delete contents of this cell (with 'del' key, or when i edit cell, delete text and then press 'Enter'). Is it possible to do with formula or VBA
View 4 Replies View Relatedi have a pivot table that shows the customer names. when i select the customer drop down box i can all the customer i want to see. but in the table itself there are some customer not showing. this is the first time this has happened
I am looking for the code to keep certain cells from being rested on, they can be clicked on but in order for you to view the formula in the cell you have to hold down the mouse button otherwise it will move you over to the cell next to it. I was using another worksheet a while ago and was trying to view the formulas but when I clicked on the cells holding them it would move me to the next cell, the only way I could view them was to hold the mouse button down as soon as I would release it it would be gone! I do not want to protect the sheet, if there is another way to hide formulas I am open to all ideas!
View 4 Replies View RelatedI'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"
Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !
VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?
If it's a macro, it would do :
1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard
2 ) see if the cell contains formulas
3 ) if yes, make a box appears to say that is not possible to delete this cell
4 ) return on the Excel sheet without changes
I have a spreadsheet which has a list of properties with a list of survey dates. The complication is that every property has multiple surveys and these are all on separate lines with the spreadsheet. The number of surveys could also be different depending on the property in question.
What I would like to do is to compile a report which only shows me when the latest particular type of survey (there are five types of survey, I'll call them 1, 2, 3, 4 and 5).
The survey type is shown in column C, the property is shown in column A and the survey date is shown in column F.
I want to stop the user selecting an entire row by clicking on the row header.
How can I do this?
Leith was kind enough to to put a great combo box together for me (see attached). Is it possible for the drop down list to open on type or do you always have to click the arrow?
View 3 Replies View RelatedI've created a budgeting spreadsheet and it is available to users via the web. When I initially created this I was short sighted and set everything as $'s. Users have limited ability to set formatting on this sheet.
I was wondering if I could create a drop down list for users to select their currency type which in turn would then drive the formatting used in the cells. I'm not quite sure how complicated this would be. Is this something that could only be done usa VB code? I'm pretty basic in Excel (know a good amount of formulas, etc).
How can I stop drag and drop across a row but you can drag and drop down a column.
I have a worksheet report that counts question answers filtered by facility, date range and case manager. I use Data Validation "List" criteria to make the in-cell drop-down lists for the criteria of the variable values. For most of them the selected value works fine, but for the case manager drop-down I need a value in the list, other than the peoples names, to represent "All" or be a wildcard and include all values in the selection. Here is the formula:
View 4 Replies View RelatedI have a large list of data records, 600+, and would like to use List Validation but find EXCEL's default Validation not appropriate for such long lists. What would be perfect is similar to what you get in Access, when you type in part of the record string required, a drop-down list appears and shows you the record entries that have that string within them and not necessarily in character order, quickly wittling down to the few records that apply and then use your arrow keys to move and select the correct record, etc. Example, I own a salon, and many shampoo ranges have many similar products:
Superstar
Superstar Blow Dry Lotion
Superstar Leave In Conditioner
Superstar Shampoo
Superstar Conditioner...........
I use Excel mainly for creating & updating simple logs, and the problem I keep running into is that when I click in a cell, it will automically select & highlight multiple cells below the one I am trying to work in. It will select anywhere from 4-5 cells up to 13. I don't know what this function is, and I don't know how to fix it. But it makes it very difficult to navigate and cut/paste or change a formula.
View 5 Replies View RelatedHaving the cell protected after entering the data. From the below table, i need when a date is entered and moved to next cell, the cell which contains the data should be protected. I need to this for the comments column as well.
Number
DateStart timeEnd TimeActual Time takenComments
123459132546132156135469654814846543
I've got a large selection of lists on drop down menu's the second drop down is based on the selection of the first but when you select the second drop down it starts at the bottom with lots of blanks you have to scroll up to get to the top of the list how do I make this start at the top and ignore the blanks?
I've got the ignore blanks box ticked on the data validation but it doesn't make any difference?
I am trying to create a macro that will run autofilter on another sheet when I select a number from a drop down menu, with that number being the autofilter criteria. I have tried the following code but it changes sheets before I can select the entry from the drop down menu.
View 2 Replies View RelatedI currently have a huge data sheet with multiple columns of information. It contains a list of projects organized by columns with information pertaining to each project. I've named this worksheet data.
On another sheet I've named Present, I'm trying to find a way to reference a single project at a time using a drop down list. The Present Sheet has a list of characteristics fields that need to be filled with information from "data" worksheet. I want the characteristics to change whenever I choose another project name from the drop down list.
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
How can I identify a cell type at excel sheet (w/ VBA command) ?
I find this command (from Access forum):
http://www.thescripts.com/forum/thread601180.html
but its working only with Access...
Is there a code that changes any numbers stored as text into regular numbers?
View 3 Replies View RelatedI have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)
When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".
If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.
I am trying to write some VBA code to select a range in a column going down to the last item in the column.
So I want to write a macro that will copy cells B4 - B17 into cells A4 - A17 but am having trouble with the code that will select B4 - B17. When I use the usual code which is as follows:
ActiveSheet.Range("b4", ActiveSheet.Range("b1").End(xlDown)).Select
it selects down to A75, and I know why this is but not how to fix it.
This is raw data extracted from a third-party system. It has an option to export to Excel, but where it appears to have blank cells Excel actually believes there is data in there.
So is a way to identify what data Excel thinks is in there and perhaps use this to be able to select the range I want?
Different extractions will need a different range moved over - however it will always start at B4 and go down to B-whatever.
I have a workbook with many tabs and I am writing a macro that works on the active sheet but needs to pick up data from another tab, which is labelled on the active sheet.
As I want to write this macro only once but different tabs have different tabs they look to, VBA code that slects the sheet named in a fixed cell on the active sheet.
This is the code that currently looks up to the tab labelled 'Cork, FA'. However, if this tab name is stored in cell AA2, I would like the code to choose the tab based on the information in AA2?
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTA('Cork, FA'!C)"
CountClients = Selection
Selection.ClearContents
I have created a color coded calendar on a spreadsheet for work that shows when a book will be published depending on when it comes into our warehouse, this is on sheet one.
I would like on sheet two to have some kind of filter or statement that would eliminate the need to look at the calendar, I.E. the user would just have to type in the date or select the date from a list and excel would show you what day the book would publish on using the data from sheet one.
I can imagine that this is hard to picture so i have attached the file as well.
I am trying to create a drop down list so that when the user choose Option A, Outcome A populates the cell.
So for example, I am looking to show a list of all Grades within the company, when the user selects a grade, the salary is populated into the cell.
I have a list of 688 codes in a column (lets say column A). e,g 23, AB, FX, NV, D8. Each code corresponds to a text In the next column (lets say column B) for example AA is the code for "Transmission". Using a clean spreadsheet and using a drop down list, I would like the cell to the right of the selected dropdown code, to automatically fill in the text of what the code corresponds to.
View 5 Replies View RelatedI have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.
There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.
I recieve an error :
Run-time error '50289':
Can't perform operation since the project is protected.
When i try and run my code.
The code does add parts to modules and workbook events, but I need the code to be protected.
grouping the protected/locked data.I am using excel 2010.I have data in sheet and have inserted group. I have protected this sheet and hence not able to use data group in this sheet.Is there a way i can operate data group option even if the sheet is protected.
View 1 Replies View Related