Validation And Linking
May 27, 2008
I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...
Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?
View 10 Replies
ADVERTISEMENT
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
May 15, 2014
I got the attached dashboard example from a source online.
I was wondering how they linked their pivot chart with a drop down (data validation). This is the pivot chart on the bottom right that is labeled "Brick Styles".
Dashboard_Your_Scorecard_Complete_2010-1.xlsx
View 1 Replies
View Related
Nov 11, 2008
A2's validation is dynamic as it's selectables varies based on the selection made by the A1 Validation. (=indirect(a1)).
My problem is this....once if have selected from both validations...if I go back and change the A1 validation to a blank (or empty value) or clear the contents on that cell....validation A2's value remains as it was. I would like it to recognize that A1 is blank and also become blank (or goto an empty value).
View 2 Replies
View Related
Sep 26, 2007
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
View 9 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
Jun 20, 2014
It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
View 1 Replies
View Related
Aug 5, 2008
I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.
On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.
I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.
The workbooks are attached below.
View 12 Replies
View Related
Dec 1, 2008
I have a problem and wanted to know if there was an easier way to create links to various worksheets. I have a folder with 59 worksheets. The names of the Worksheets are the weeks of the year(example LA-01-01-09) I have another workbook with 13 worksheets one for every month and a sheet that totals the whole year up.
What I want to know is how can I create a link to everyone of those weekly sheets to go to my monthly sheets,so when data is entered into the weekly sheet the monthly sheet will update automatically? The weekly sheets are setup exactly the same. I'm looking only to pull row 3 columns c:d:f:g:h:i over to my monthly sheet.
I have an example of my weekly sheet and monthly sheet.
View 6 Replies
View Related
Jun 9, 2009
I have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.
PROBLEM:
The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.
Here is the pertinent code on the general code sheet: ....
View 13 Replies
View Related
Oct 13, 2005
I want to pull data from about 100 different Excel files. They are located in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something like
S:FolderJanuaryCaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula.
View 11 Replies
View Related
Mar 20, 2009
in each cell I have formatted a Workday formula which is for example:
=WORKDAY(D16,3)
Now I want to add to that function
"If D16 is blank return blank"
What I am getting at the moment is that the if D16 is blank I am getting #VALUE!
THis makes the sheet very hard to read.
View 14 Replies
View Related
Nov 23, 2011
If possible I would like to create using vba a button at the end of my rows of data allowing a user to copy and insert the row next to the button, i.e. duplicate.
I am using the following code to create the button but am not clear how to link a button to a row or cell. The button would be within a cell in a row at the end of the data.
Range("a2").Offset(rowoffset, 10).Select
ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
rowoffset increments within a loop and a button is placed at the end of each row of data.
I would also like to know how to name the button.
View 5 Replies
View Related
Apr 4, 2007
I run a number of spreadsheets each 4 week period and these are stored in a folders named after the period. ie Period 10 will contain workbookA10wk1, workbookA10wk2, workbookA10wk3 and workbookA10wk4,
Period 11 will contain the same workbooks but named workbookA11wk1 etc.
The data from the workbooks is then used in graphs / tables for each year.
I always pick up the same cell ref in each workbook to compare each week and my table is set out with heading of period and wk.
Is it possible to use the table headings to produce the names of the workbooks that you wish to reference, ie: ='Period 13[workbookA13wk1.xls]Sheet1!$D$17 to appear under Period 13, wk1
View 9 Replies
View Related
Jun 27, 2007
I am working on a project and if i hit button one in my VB program i need it to pull up a program under c:programfiles/user/name.xls
View 9 Replies
View Related
Sep 9, 2007
how do I go about linking cells?
Example:
Lets say I have a workbook with 6 sheets on it and the sheets names are:
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Status
And we will just use cell A1 through sheet1 to sheet5 and the status sheet to grab the numbers from sheet 1 through sheet5
Sheet1 through sheet5 will have numbers in cells A1
Sheet1 : 586
Sheet2 : 436
Sheet3 : 621
Sheet4 : 610
Sheet5: 561
And in the status sheet I would like to find the highest number from these 5 sheets and put it in cell A1 status sheet and have it linkable like when it finds the highest number it will put it in cell A1 on the status sheet and when I click on the A1 cell in the status sheet I would like it to goto that sheet that has the highest number it found and maybe highlight it or something.
View 9 Replies
View Related
Nov 16, 2009
I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.
View 9 Replies
View Related
Jan 4, 2010
Is it possible for me to link to other spreasheet data, and have the links update (retrieve the data) without those files being opened?
View 9 Replies
View Related
Sep 18, 2006
I have a workbook Book1 that has 4 sheets, one of those sheets needs to be an exact replica of a sheet from another workbook Book2. I tried to directly link the data and use Vlookup's but the problem is that when someone opens Book2 and inserts more data (Inserting rows) it does not show up on Book1.
View 3 Replies
View Related
Jan 14, 2007
I have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:
I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.
when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.
View 3 Replies
View Related
Mar 1, 2007
I compile a report each month. I have a program/macro built to do this so based on the month I select it runs the code below. The code below would run if i selected January. I run this report on a spreadhseet and its for a ton of people, and my code is really long. I know there is someway to shorten it, but i cant figure out how. Below is an example of one persons, and the next persons would begin on the row below the example.
Sub January_NB()
'Week1
'Christie
Sheets("New Business").Activate
With Sheets("New Business")
Range("B8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R73C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R6C4"
Range("E8").Select...........................
View 4 Replies
View Related
Apr 15, 2007
i have created a worksheet in excel that is used to moniter employees. i want to export the data in to an access database when a button is clicked so that i am able to track the employees over a period of time. how can i do this using vba?
View 5 Replies
View Related
Feb 15, 2014
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
View 1 Replies
View Related
Dec 5, 2013
I'm trying to work out how to allow a formula to be changed by pointing part of the code at a drop down list containing worksheet tab names from another workbook.
View 2 Replies
View Related
Mar 6, 2014
I'm trying to link objects and pricing to groups via a combo box. If the user selects Group A from the combo box and enters Object A on the line item, I want the price associated with Object A and Group A to populate.
Group A
Group B
Group C
Group D
Object A
$1
$3
$5
$7
Object B
$2
$4
$6
$8
Object C
$3
$5
$7
$9
Object D
$4
$6
$8
$10
View 9 Replies
View Related
Mar 10, 2014
I create the billing data for some key accounts and I was asked to build a summary sheet that brought all of them into one place so that the billing department didn't have to open each individual spreadsheet each month to verify their info to mine. What I would like to accomplish is to stay current according to today's date.
So if it's March, 10, 2014, I need to update the spreadsheet data according to the February 2014 spreadsheet a Key Account #1. Then in April, I need it to look for the new March 2014 spreadsheet. I'm assuming I can use an =IF(OR(... formula for this, but I'm just not sure how to get the date thing sorted out.
As far as naming of the the Key Accounts billing spreadsheets, they go something like 0214 "KeyAcct #1".xlsx. Then the new one will be 0314 "KeyAcct #1".xlsx. Even tho the next months billing file isn't created, the formula should be able to check against the date and know when to look for the new one.
View 1 Replies
View Related
Jul 16, 2014
You have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
View 4 Replies
View Related
Jul 18, 2014
I have a list of countries in excel and a list of partner countries, the countries all have individual country codes.
Example:
country partner country exporter country code importer country code year value
Australia..........Belgium........................AUS.........................BEL..1999..1000
Canada............Belgium........................CAN.........................BEL..1999..2000
This for a long list of countries, I have filled in the country codes for the first year manually but the list goes on to 2008 and is a lot of work to do manually, (thousands of cells). is there a way to complete the list of exporter and importer codes linked to the country and partner country column and then complete the whole list?
View 1 Replies
View Related
Jul 28, 2014
I have a database sheet with a ticket list which has various columns like business area, ticket number, location, status etc. I am creating a new sheet where i want to highlight only the tickets that are in pending status and I dont want all the columns from the main sheet but just a few. How can I link it so its automatic.
View 4 Replies
View Related
Apr 18, 2007
For example, Column D, Rows 2 - 500 contain different number codes:451
461
593
675
I want to match up the number code and replace it with the Course Name.451 = Course A
461 = Course B
593=Course C
675=Course D
I am looking to have the number code, i.e., 451 replaced in Column D with the Course Name, Course A or the Course Name appear in Column E pulling from the number code.
In the end I will probably have 100 unique number codes to match up with course names.
View 9 Replies
View Related
