Data Validation Between Different Sheets Of Workbook With Formulas?
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?
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.
I am currently working with a hidden sheetname "hide_template" which contains formula linking to another sheetname "rawdata" of same workbook.
In this "rawdata", the contents is updated from time to time.
The problem is when I open this workbook and update the entries in "rawdata", the formulas/results in "hide_template" is not updated and invalid (ie. #N/A, #VALUE..)
This is the overview of process that Im working with:
===> open workbook ===> paste new entries on "rawdata" ==> run a macro to copy range in the "hidden_template" to paste to new sheet of same workbook
I already have a working macro to copy range from the "hidden_template" which works as follows:
===> set to visible the "hidden_template" ===> copy range from "hidden_template" ===> paste to new sheet ===> set to hidden the "hidden_template"
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted? Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed? ... “SATURDAY (ARRIVE)” = “USD 100.00” ... “TUESDAY (DEPART)” = “USD 85.00” ... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula: IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error.
What I have attached is spreadsheet I've built to quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.
For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.
What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.
Oddly enough, when I enter this formula under Data Validation:
My Table has a data validation list in one row of a table. And different formulas in different rows.
The Table is expanded only into columns. Which means, there will be no new rows...only columns will be added.
If we enter text into the header cell in a new column, the whole new column gets formatted, but the list and formulas do not auto fill into new columns.
Of course an easy way is to copy and paste a column...but is there a way to auto fill lists and formulas into columns of the table? Similar to how the rows get autofilled?
I have multiple sheets (Sheets "A", "B", "C", etc) with the same structure and formatting. For these sheets ("A", "B", "C", etc.), I want to create dropdown lists in column F (cells F2:F100) based on values from a different worksheet (Sheet "DropDown", Cells "B2:B130").
I saw a previous post [URL]... which had a single sheet example. I am trying to come up with vba code for multiple sheets and so far it's not working.
VB:
Dim wkst As Worksheet For Each wkst In ThisWorkbook.Sheets ThisWorkbook.Names.Add Name:="listdata", RefersTo:= _ "=dropdown!$B$2:$B$130" With wkst.Range("F2:F100").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=listdata" End With Next End Sub
I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
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.
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.
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.
I.E.
ATL ATL ATL ATL CEN CEN JCK JCK etc.
There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV
The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.
So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.
I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.
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.
if there was a way to disable mcros but I think now I should have pursued the option of saving data to new workbook without transfering the existing formulas or macros. I really think I should attach the sheet I have thus far so I can convey what I am trying to accomplish. Anyone interested, please let me know and I will forward the sheet. I am unsure how to do this any other way. I found some code that would claim to do this but I have so many things going on already in this sheet I can't figure out how to incorporate the new code to work properly. Here is the original thread for reference [url]
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?
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)
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)?......
I am in the process of transferring data from one workbook to another.
I am doing this simply by clicking on the cell where I want the data to be, typing "=", then clicking on the cell in the other workbook I want it to match. While there might be easier ways to do this, it isn't too terrible and my columns are not in the same order in the two workbooks.
The problem I'm running into, is when I click on the cell I want to copy over, the formula defaults to the fixed formula (puts $ around the cell name) and I want to be able to copy down the formula over multiple cells.
I can click on the cells and press F2 once then F4 three times to get the formula over to the desired non-fixed format, however, I have to do this for 12 different workbooks and on roughly 40 columns for each.
Is there a way to expedite the process or turn off this fixed-formula default?
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
I have a workbook with two sheets of sku numbers. One sheet has sku numbers and prices, the other just has sku numbers but some of the sku numbers on the second list have been broken out by sizes. I need to take the prices from the first list and transfer them to the matching sku numbers on the second including the ones with multiple of the same sku but with prices. Also, not all of the skus from the second list are on the first list so I only need to do the ones that occur on both lists. There are over 3000 on the first list and 14000 on the second. What is the fastest way to accomplish this.
I tried to sort so that all the sku's in both sheets are in the same order then copy and paste the price to the matching sku on the second sheet and then drag the price down through the sizes on the ones that have different sizes (not all of them do) but I know there must be a faster way.
I have a workbook (Sheet 1 contains - 6 columns and 1000's of rows). Column B has sensor type. Is there away to copy all data the deals with each sensor and paste it on a new sheet in the same workbook and name these new sheets by sensor type
Example Date Sensor IP Address DNS Error 1/1/2014 Unix 1.1.10.10 fatty clock error 1/1/2014 HP 1.1.2.3 slim power isues
I have workbook named "Distribution and Revenue" which is contained 14 sheets , which names are like "UB Distribution", "UB Revenue", "SB Distribution", "SB Revenue". I want only all Revenue Sheets Data (leaving Last Row) to be copied to another workbook "F:DataFinal Result.xls" in sheet named All Revenue.
I have a mastersheet with all the information and I want to be able to copy it to new sheets.
The four different sheets I want are: Male-Olympic, Male-Sprint, Women-Olympic, Women-Sprint. Is there anyway to go through the sheet, copy the rows, and paste them into their proper sheet.
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.
* I have a workbook, with different sheets (with sheet name as Home, RawData, Data1, Data2, Data3, ...), these sheets are not fixed * FYI... sheet (home) has a VBA code which on running creates a new sheet and pull data from some other source (So the sheet number is not fixed in the workbook, but the newly added sheet is named in a specific format as Data1, Data2, Data3, ...) * So we can say, that the sheet number and name are not fixed (It keeps on changing) * All these sheets have data in the same format starting from range A2:J2, except sheet(home)
Two things i am trying to do:- - Select sheets from the workbook (to be consolidated) - Consolidate selected sheets in a new sheet
Here is the sample file Select sheet then consolidate.xlsm
