Error In Data Validation - Unable To Load Spreadsheet?
Sep 4, 2013
Error in data validation: "A named range you specified cannot be found." However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.
Full description:
I created a spreadsheet model (no VBA, just formulas, etc) in a workbook that another coworker took and added VBA to. However, he was not working with the most updated version. So last night I checked the sheets which had the same names, made sure there were no differences except the changes I had made since that version, and then moved the ones from my current workbook into his workbook.
On the sheet where the user was to input details of the report, there are 4 cells that each contain different lists (non-dependent). I want the user to only be able to choose one of the 4 drop down menus - that is, if any of the 4 cells are filled in, I would like the other 3 to default to N/A (or somehow not be allowed to be chosen). This was solved in a previous thread:
Using A1: =IF(COUNTA(A2:A4),NA(),G2:G6) from the other thread solution, I used a named range instead of G2:G6 and it worked well.
When I merged the files together, the 4 cells no longer worked at all. It was suggested that I look at the Name Manager, and some of te named ranges I had were in there multiple times. I cleaned them up.
I tried to enter the same formula again, and it gave me the specific error: "A named range you specified cannot be found." However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.
I am unable to load the spreadsheet due to sensitive data, and removing the data would make the workbook make no sense.
View 6 Replies
ADVERTISEMENT
Dec 12, 2013
On the Control Panel sheet of the attached book, I need to load only the non blank cells from BQ3:BQ9 into a data validation drop down.
View 8 Replies
View Related
May 28, 2014
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
View 13 Replies
View Related
Jun 4, 2013
I have a pretty simple spreadsheet (because I'm not an expert on excel) to track temp staffing requirements each week over a year. It only has about 150 rows and a bunch of columns but when I try to make it bigger (add more rows so I can put about 70 staffing positions instead of 19) it will not save (cannot save all of the data and formatting). I have read on the microsoft site that there are limits to data formatting, but it describes over 2000 rows?
View 3 Replies
View Related
Apr 19, 2008
Very simple Excel 2007 spreadsheet. It consist of four columns with either numeric or alpha data in the. No formulas, no macros etc...
She emailed the spreadsheet to me and it works fine, I can enter data without problems. Whenever she tries to use it she just can't enter any data. She clicks in a cell and it highlights but she doesn't get a cursor and can't enter data. If she tries to click in the area underneath the toolbar to enter data she also does not get a cursor!! It used to work just fine for her but has suddenly stopped!
View 5 Replies
View Related
Dec 16, 2002
I need to do a macro that will open a search window, the user would select a folder and it will search for a .csv file within it. Then after locating the file, it would automatically load it into a specific sheet in the workbook.
View 9 Replies
View Related
Apr 17, 2008
Using some custom VB code to put the filename of the document into a cell.
I save the spreadsheet and load it up on another computer and all I get is #VALUE.
How do I fix this so its loaded on every machine without problems? (Multiple people will be using these sheets)
View 9 Replies
View Related
Jan 5, 2010
In my spreadsheet I load 26 CSV files. Each CSV file has 3 columns and 7 rows. It uses the connection query in Excel. It is only relatively slow to refresh the data. To refresh the data, I can press the refresh data button in Excel.
But in practice I use this code every 5 seconds:
View 9 Replies
View Related
Aug 23, 2013
I add data validation into 4 columns and when i re-open the spread sheet after saving it, it clears all validation.
Although it clears the validation, the place where it gets the data from is still there. i have also unfroze panes and is still clearing data validation
Attached is the spread sheet. The data validation is in columns A, G, H and I on the 'unsourced' page and is getting the data frfom the 'Sheet1' Page.
View 2 Replies
View Related
Sep 15, 2009
I've sent out an excel spreadsheet to a whole load of people and some have received emails back saying that it won't open properly. It's got a form which pops up and populates two data sheets behind it.
One of the issues I've had so far is that the Microsoft Common Controls library isn't installed on some computers so the form won't work. Are there any other controls that are likely to not be installed and thus prevent the form from opening?
View 9 Replies
View Related
Aug 12, 2014
I am trying to build a spreadsheet containing data validation (drop-downs). So far, so good.
My goal:
* I want to have various cells show dropdowns based on the value of a cell (per row) which itself can be changed by the user using a drop down.
* I want to have the same various cells show dropdowns that are also based on the value of a cell which has a fixed value per column (in other words, a header).
I have tried a lot of different approaches, including using the INDIRECT function, named ranges, dynamic ranges using a table, INDEX and MATCH. All have failed.
The main issues I see have to do with the fact that my data has several possible values for each given header and classification. Also all values are not unique.
The best result I have ever got returned a dropdown list based on the header bot returned the entire column rather than limited to the classification values. So for example I had all instances of 'Main Colour' to choose from, not just those of the classification value.
View 2 Replies
View Related
Feb 17, 2009
This is the code where you click on a cell that has been input from Data Validation, it gets a pop up textbox showing the text input. One drawback I see here is that when the sheet is protected, one needs to tick 'Edit Objects' in 'Allow all users of this worksheet to:' window to get the textbox to pop-up otherwise no textbox is shown.
So this leads to the accessed user to have the ability of deleting and moving the textbox in the protected spreadsheet. Do you have any idea to Protect the sheet, Get the textbox to pop up,
Disallow user to delete/move the textbox.
View 10 Replies
View Related
Oct 22, 2008
I included a couple screen prints showing that the form exists, and that it is not misspelled in the code. What should I look for?
View 4 Replies
View Related
Dec 11, 2013
I'm trying to delete images from an excel spreadsheet. She had imported several Red "X" symbols and placed them at the bottom of the spreadsheet so that she could click and drag them to various spots on the spreadsheet when needed.
Well, now wants several of the "X" symbols to be erased...the problem is that they won't simply delete and when I click to drag them they stay in the same place and give me a duplicate to drag.
EXCEL Example.xlsx
View 3 Replies
View Related
Apr 28, 2014
I am currently creating a form to hold 2 drop down lists that change depending on the selection in the first one. I had never done this before and taught myself and got it to work minus one problem. As soon as I input the formula under the Data Validation the error: "The Source currently evaluates to an error. Do you want to continue?" pops up. Right now the formula works except for the last 6 options in my first drop down. When I select them nothing pops up in the 2nd drop down menu. I have double checked and triple checked that I have no spaces or anything in my LIST on worksheet 2 or any of the names are input correctly.
A7 is the first drop down - PROPOSED
B7 the second. - ITEM #
Lists and data to populate the lists are on worksheet 2 with the "List" at the bottom of worksheet 2...
View 1 Replies
View Related
May 15, 2007
I'm working on a function that gets its values from a Data Validation List.
My problem is that I want the function to return a value when an item in the list is selected and another if the user did not make a selection... just a basic if....then....else ... but the cell where I call this function shows the #VALUE! error while no item in the list is selected.
I call the function like this:
=myFunction(RC[-5], RC[-2])
where RC[-5] is the Data Validation List and RC[-2] is a cell where the user should enter a number
why I'm getting the #VALUE! error instead of my message
The function basically looks like this:
View 9 Replies
View Related
Jun 19, 2009
I have a excel document that is very heavy with Data Validation List Boxes. Below is just one example. Error handling is what I am trying to figure out.
Description:
I have 3 List Boxes.
One list box data is based on data selected in the previous list box.
1st List Box uses this formula: =Series
2nd List Box uses this formula: =INDIRECT($C$2)
3rd List Box uses this formula: =INDIRECT($C$3)
It all works! HOWEVER....
If user decides to go back and change selection in first or second list box, and forgets to change the data in the second or third box, then data will be incorrect. How do I solve this problem?
Is there anyway to write something so it would give them an error message and instructing them to correct the data?
View 9 Replies
View Related
Jul 18, 2014
Excel spreadsheet, that contains several columns. Three of these columns contain text data that is selected by using a simple drop-down menu sourced from a list. If an attempt is made to enter text data not in the associated list an error message is displayed. Easy so far.
I need need to extend the text data validation across all three columns so that only specific combinations of the text in the three columns can be used which is reflected in a further list. These combinations will be counted elsewhere and so needed. Therefore, if i attempt to enter a combination of text columns that is not reflected in the list, this too will present an error message stating 'combination not valid', or words to this effect.
View 2 Replies
View Related
Oct 13, 2007
I have a spreadsheet which I have formatted with validation rules for each cell. I have protected the sheet so that only certain cells can have info entered.
I also have a second spreadsheet with a bunch of data. If I copy this data and Paste Special it into my specially formatted sheet (Values only), all of the data copies over regardless of my preset validation rules.
I would like error messages to pop up for every incorrect value though.
View 9 Replies
View Related
Jun 23, 2014
I'm using Data Validation for various fields in a form and I'm trying to make it user friendly by adding Error Alert conditions and text explaining why the error has occurred. I also want the form to be translatable into other languages.
For some VBA message boxes that get displayed I can put the English text in some hidden cells and that can be translated easily by someone without access to the code. But I cannot figure out how to make the Data Validation Error Alert messages point to a cell so that they are also translatable without forcing the translator to open each Data Validation dialog.
View 1 Replies
View Related
Nov 13, 2013
Code:
For Each src In Worksheets
If src.Name Like "EF Spend*" Then
Sheets(src.Name).Select
Else
Next src
End If
This is an extract of a larger code, but this section is producing a Compile Error: Next without For but I cannot workout how to resolve it.
View 9 Replies
View Related
May 11, 2014
I am new to VBA. I have been getting compile error on the below code:
Public Sub hourCalculation()
Dim rcount As Integer
Dim rindex As Integer
Dim logintime As Date
Dim logoutime As Date
[Code] ..........
View 9 Replies
View Related
Mar 28, 2014
I have a folder with multiples excel sheets
Destination : C: Project CustomerExcel
I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.
New sheet would contain the information of each sheet eg. Column A2= file name
And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.
VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
[Code] .....
View 5 Replies
View Related
Mar 30, 2014
I am executing code in a workbook that has been working successfully previously.
Suddenly I get a run time 1004 error in any macro in the workbook that tries to execute a activeworkbook.save command?
View 1 Replies
View Related
Nov 25, 2008
I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.
Upon executing the formula I then want it to copy and paste special- values the result.
I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.
The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.
View 9 Replies
View Related
Oct 27, 2011
I have a sub that I have copied out of a previous workbook where it still works fine. In the previous workbook it was assigned to a button. Now I am trying just to do it as an automatic action when the workbook is closed
It will lock any cell within the specified range that is not blank then protect the worksheet, save the workbook then save a dated archive copy
When I pasted it in to a new workbook I changed the range to A1:AP49 and strFileName
Now when I run it now I get an Error 1004 - Unable to set the Locked property of the Range class.
On Debug this line is highlighted:-
c.Locked = c.Value ""
The full code is:-
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Lock_cells Macro
' Macro compiled 10/26/2011 by G CARNCROSS
' Locks used cells, saves the workbook then saves a password protected copy with the days date in the file name then closes the workbook
'
'
ActiveSheet.Unprotect Password:="SHES"[code].....
View 2 Replies
View Related
Jul 2, 2006
the if stattement works perfectly and does exactly what i want except when it comes to the else part. if there is no error the statements are run perfectly but if there is an error (in this case the error is generated when a match cannot be found in the spreadsheet) the else statement doesnt kick in and post the msgbox.
the code just crashes. and returns an error 1004 on the line i have highlighted in yellow
res = WorksheetFunction.Match(invvar, Columns(1), 0)
If Not IsError(res) Then
Me.txtClientID.Value = ws13. Cells(res, 7)
Me.txtNumber.Value = ws13.Cells(res, 7)
Me.txtDate.Value = ws13.Cells(res, 8)
'save client id as a variable
'Print to invoice------------------------------------------------------------------.....................
View 6 Replies
View Related
Jul 10, 2014
I use Excel 2010 andexample.xlsx cannot make my code working because of the so famous errore above.
I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly.
Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line:
[Code]....
View 1 Replies
View Related
Feb 24, 2010
I am getting the following error when i am clicking a Button on sheet1 which is generating duplicate of sheet1.I am trying to delete the button when duplicate is created..Error is:" run-time error '1004' Unable to get the buttons property of the worksheet class" at this line in code"ActiveSheet.Buttons("CmdHistory").Delete"
View 2 Replies
View Related
Jun 25, 2013
I am using this code (below) to hide certain rows or columns depending on what number is entered. Everything is working fine and I am at the point where I would like to share this excel sheet with others but I would like to lock certain cells so that others cannot alter the formulas. As soon as I lock the cells and then enter values into the unlocked cells, I get the "run-time error '1004': Unable to set the Hidden property of the Range class".
I am using excel 2010 x64.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B3")) Is Nothing Then
If Range("B3").Value = 0 Then
Columns("G:P").EntireColumn.Hidden = True
Else
If Range("B3").Value = 1 Then
Columns("H:P").EntireColumn.Hidden = True
[code]....
View 3 Replies
View Related
