Reference Data Validation Cell From Another Workbook
Sep 17, 2008
I'm trying to link a range of cells in one workbook to a range in another workbook, both in a network drive. Everything is working out well except for the cells that have a data validation list on them. the formula linking one cell is ='G:Destination To[Workbook2.xls]SheetX!B13 so I'm just trying to get the value in the cell. I don't need the validation list to transfer just the value of what was selected. If I go to that cell and type something in then it transfers over just fine. If the validation list was used in Workbook2 then I get a #N/A in Workbook1 that is linking to the Workbook2 cell.
How can I link the cell with a data validation list in it to a cell in another workbook so that I can get the data in the cell (whatever was selected from the validation list)?......
View 4 Replies
ADVERTISEMENT
Nov 23, 2007
I'd like to create a list via Data validation-list. Anyone knows how to reference a range on another sheet of the workbook?
The range is =$Q$5:$Q$13
Is it possible to reference another sheet? If yes, how is is done?
View 9 Replies
View Related
Nov 7, 2012
If I have two cells and a named range mexico
A1, A2.
A1 contains the string: "mexico"
A2 is the cells that has required data validation (drop down list).
I want A2's validation reference to be dynamic, in the sense that I can it reference it to A1; converting the string "mexico" to a named range mexico
I tried inputting = INDIRECT(A1) into A2
But I lack clarity in my understanding of referencing.
What is the correct procedure here.
View 2 Replies
View Related
Feb 13, 2008
I know it's possible to list option reference in another worksheet in relation to data validation, but is it also possible to reference to a complete different workbook. If so, what's the procedure? I have many workbooks that reference the same list options.
View 7 Replies
View Related
Apr 21, 2008
I am designing a spreadsheet at work, and need a little assitance. I can't find the answer I need online or here either. Here's the problem: I want to be able to enter data (A 5 digit job number, 80227 for example) into a cell, and then multiple other cells would auto fill with data from outside worksheets. The worksheets are in a seperate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227 into them.)
I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.
View 3 Replies
View Related
Jun 2, 2009
I m using Data/Validation function in Excel2003? Is it possible to have reference list of drop down input from another sheet? I recorded the macro for this function and had the following result
Sub Macro1()
Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$C$2:$C$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
How can I change reference List from the same sheet $C$2:$C$6 to "sheet2!$C$2:$C$6" or it is a limitation for this function?
View 3 Replies
View Related
Feb 16, 2010
I have a range named as follows in a formula:
BaseCase!$O:$O
I have a data validation from which the user can choose another tab.
So, how do I change the reference above to:
TabName!$O:$O
so that TabName refers to the text string in cell B2 (data val. box)
I tried to use TEXT to no avail. I imagine there is a simple solution, but I am at a loss.
INDIRECT perhaps?
View 9 Replies
View Related
Apr 22, 2008
I have a spreadsheet in Excel 2003 with many sheets all of which need to have for some columns the data entry restricted to a specific list.
To do this we have created lists defined and held in a separate sheet and then attempted to reference these from all the other sheets using Data -> Validation and then entering the relevant list in the Validation criteria.
The 1st sheet we set the Data Validation up in all is fine. The 2nd sheet however I get an error pop-up stating 'You may not use references to other worksheets or workbooks for Data Validation criteria'.
View 4 Replies
View Related
Aug 19, 2009
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies
View Related
Aug 17, 2009
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?
Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.
View 4 Replies
View Related
Jun 19, 2014
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
Example 06.19.14.xlsx
View 2 Replies
View Related
Apr 7, 2009
I have a problem in creating data validation. I want to make 3 drop lists which are connected to each other (file1.xls for example). The sample is like the tutorial from Contextures: http://www.contextures.com/xlDataVal02.html
The source of lists are from another excel file (file2.xls). The method is also from Contextures: http://www.contextures.com/xlDataVal05.html And then, when I tried this, the second drop list didn't show up according to the first drop list. But, if I put the source in the same workbook (in file1.xls), It could show up. I think the problem is in the name box, but I don't know how to fix it.
View 5 Replies
View Related
Sep 17, 2006
I've got a list of text files I need to import into different worksheets in xl. The list is of the file name and the column start points for the fixed width file. This will be a regular job so if can automate it would save alot of time. I've record a macro that does text to column and adapted it to create an array listing the start points for the particular file, but when I try to use an array name with TextFileFixedColumnWidths, it doesn't seem to work.
View 2 Replies
View Related
May 24, 2012
I have two workbooks.
Workbook1 contains validated list which is defined by a Range name and also its data is pulled from workbook2. I am unable to change the value of the validated list from drop down when i close workbook2, infact the validation drop down list do not drop at all so that can select values from it. But if i open the workbook2 i am able to change the values of the validation list.
I want the dropdown list to be active when i close the second workbook.
View 12 Replies
View Related
Jan 27, 2013
I have data validation on all the pages in my workbook. How do I edit all the data validation at once.
View 4 Replies
View Related
Jul 29, 2014
I am working on a resource management type workbook. In the first sheet,(Project Assignment) managers can enter staff, staff type and hours needed for the next three weeks. In a separate sheet,(Total Hrs per week) I have formulas set to total the hours entered from the first sheet for each resource - using the formula =SUMPRODUCT(--(staffassignments=$B4),--(Week1)) for each week, for each resource. That woks fine.
What I would like to do, if possible, is to present a type of data validation, dialog or popup when the resource's "total hrs per week" total = 40 hours. Different managers use the same resources, so one person may have time entered in multiple times for different projects in the Project Assignment sheet.
Is it possible to use the totaling formula (=SUMPRODUCT(--(staffassignments=$B4),--(Week1))) and an if statement in the data validation or conditional formatting to let managers know that the resource is fully utilized?
View 3 Replies
View Related
Aug 12, 2014
I have a question regarding linking the content for data validation lists stored in a different workbook.
The scenario is as follows: I will have say 50 users using a template which contains various dependent drop-down lists. These lists work by using the INDIRECT function which calls the correct drop-down based on the previous selection. The drop-down entries are currently stored in named ranges on a hidden worksheet in each user's file.
However, if I need to make a change to the content of these lists, I will have to open all 50 files individually and change each one. So I'd like to have a master file to which all the templates would be linked which would contain the entries for each drop-down. So when I update a list in the Master file, it would automatically update the drop-down lists in each user's template with any additions deletions.
My question is, how do I create the links between the user templates and the master and have it so that the list ranges will be dynamic. So if a drop-down list contains say 10 entries and I add 2 more in the master, that those 12 now appear in each user's template?
View 3 Replies
View Related
Jun 22, 2009
Is it possible to have a workbook (1) with a list on it and another workbook (2) which has a dropdown list on it that shows the list on the 1st workbook. But when a new entry is made on workbook (2) it is added to the list on workbook(1)
View 8 Replies
View Related
Apr 3, 2009
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies
View Related
Jul 18, 2014
I have a work book, it copies a cell reference from a work sheet in workbook2 to workbook1
It then selects next work sheet in each page and then triggers the code again.
My problem is i want the following piece of code to work.....what should i put in where it says "activeworksheet"
[Code] ......
View 7 Replies
View Related
Aug 7, 2009
I am trying to write a formula that will pick the data out of a closed workbook.
Indirect works but it doesnt help because the workbook needs to be open and there are literaly hundreds of workbooks to sort thru.
Is there a way other than find and replace to have a formula pull data from a specific cell in another workbook, based on say cell A1 or A110?
View 9 Replies
View Related
Nov 22, 2011
I have a workbook with various "in cell" validation selection boxes.
All worked well, but now the width of de dropdownbox is very big, stretching over several cells. The cell width is 70 pixels, but the dropdown box is 500 width.
I tried new validation, but no matter where I place a validation of what list I use in validation the width of the dropdown is always over 500 pixels? How to change that?
View 1 Replies
View Related
Jun 5, 2008
Following syntax is incorrect, what is wrong? This should happen: Select op a specific worksheet (named DATA) in the workbook Report.xls and I empty the contents
Then I try with a 'With' statement to read the contents of a file named "sourcedata.xls' with helds a worksheet named "source". The values in this sheet should be tranfered to the sheet DATA in the workbook "Report.xls"
Sub FetchDataFromClosedWorkbook()
Dim FileName As String
Dim SheetName As String
Dim cellRange As String
Const ReportPath = "C:ExcelReports"
Windows("Report.xls").Activate
Sheets("DATA").Select
Cells.Select....................
View 3 Replies
View Related
Jan 23, 2014
I have a master workbook that I reference data from hundreds of excels on.
I am trying to reference the cell "BU7" that has the workbook name (NT-13-001) in it, to reference sheet name "Purchase Order" at cell "H13".
Next line I would like to reference the cell "BU8" that has the workbook name (NT-13-002) in it, to reference sheet name "Purchase Order" at cell "H13".
The computer path is K:FRG TOOL REQ2013NT FormsNT-13-001
This is the formula I have tried... I have also attempted the INDIRECT function & Hyperlink GO TO.
=IFERROR('K:FRG TOOL REQ2013NT Forms["&DU7&".xlsx]Purchase Order'!$H$13,"")
View 1 Replies
View Related
Mar 19, 2014
So finally got my sumrpod working using this formula
=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C8:$C10000=A5))
Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.
e.g A1 = South Pac Load Tracker - MAR14.xlsm
Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!$C8:$C10000=A5))
View 1 Replies
View Related
Aug 19, 2009
Is it possible for the VBA code to capture the open workbook name and sheet name from a cell?
I've been trying to tweak this ...
View 6 Replies
View Related
Jul 9, 2012
i need a VBA code to activate workbook (which is already opened) with reference to name in cell A1
in Cell A1 is "masterworksheet.xls"
View 4 Replies
View Related
Aug 12, 2006
I have recently learned a little about Excel. Enough to be able to do our payroll for my company on it. My question is: On the 1st page of my workbook, is my quarterly report which shows all taxes, social security, and medicare we hold out each payday for each employee. The rest of the worksheets are for each employee. How do I have each employees withholdings automatically go to my quarterly report page?
View 5 Replies
View Related
Jun 11, 2008
I would like to know the proper syntax for recalling the value of a cell in another workbook. For example, I want a variable in my code to equal cell(1,1) in sheet1 of workbook1.xls.
View 5 Replies
View Related
Feb 20, 2014
I have the following formula/array:
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4
Now this works perfectly fine, but I am wondering if it is possible to replace 2013-004 so that it reads the text from a cell on that sheet to complete the file name.
Example:
A
B
1
2013-004
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4
I want to replace the 2013-004 in B1 with text from A1. I have tried multiple methods but it just always assumes it is part of the file name or breaks the formula.
View 2 Replies
View Related
