I have validation tables in a few cells. I've unlocked them and password protected the sheet so that they couldn't be changed other than what is in the list.
What's happening is that the list is there, but the cell is also allowing an entry that is not in the drop down.
How can I protect the cell to only allow the validation list?
is it possible to put the validation tables on a second sheet? I have several cells that have drill down lists, is it possible to put these lists on a second sheet?
View 2 Replies View RelatedMy 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?
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
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).
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)
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 RelatedI 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
I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.
=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))
i need to add some more ifelse but the validation don't allows it.
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 RelatedI am trying to merge two different databases in the next week. The problem I am running into is the name from 1 database isn't always the same on the other database.
I want to merge Database 1 with Database 2 and create a relationship with them both by using the database 2 internal #. As you can see in my table database 1 has similar names to database 2 but they are not exact. I want to match these names and have the formula on match tell me what the database 2 internal # is. I have also uploaded a sample excel doc with a note in B2.
database1
Herb Chambers Ford of Braintree
Herb Chambers Honda of Seekonk
Dimmitt St. Petersburg
Database 2 matched internal #
Database 2
Herb Chambers Ford of Braintree (Dave Dinger Ford)
Herb Chambers Honda Of Seekonk (7-FI)
Dew Cadillac (Dimmitt of St. Petersburg)
Database 2 internal #
15360
15301
25413
I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.
I think this requires SUMPRODUCT, but can't work it out.
The attached spreadsheet should explain clearly.
I have:
- Table 1 has ~1,600 records (name of game, meta score, user score)
- Table 2 has ~3,000 records (name of game, genre, publisher)
I want:
- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)
Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.
I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).
Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that
I have a table with dates that have bonuses associated with them, some times the bonues change, so i have another table with the date the bonus is effective and then the range of bonuses based on the sales of the month....
View 12 Replies View Relatedi am trying to correspond 3 columns like:
Code:
________________________________________________________________________
points from / points to / note defined / points pupil got / his note
------------------------------------------------------------------------------------------------------------------------------
0 10 4 33 #
11 20 3 16 #
21 30 2 25 #
31 40 1 07 #
-------------------------------------------------------------------------------------------------------------------------------
Column 5 (his note) must be calculated looking at value on column 4 (points pupil got) and finding it on the intervals given by columns 1 and 2 (points from/to), and finally ascribing the value from column 3 (note defined).
I have a worksheet with data in named tables on several sheets. Each of these tables has a column called "filter" (this is not always the same column number between sheets). The values in this column are either 0 or 1, depending on if that particular row is relevant under the book's current selections.
I'm trying to figure out the code that will take a table and filter it to show only the rows which have a 1 under the "filter" column.
Need to sort data from one table to other tables in diferent worksheets so I can easy print report.
for example: (i got about 40 markets to add in table, this 5 is just for e.g.)
ABCDEFGHIJ1DATEMARKETEMPTY B.FULL B.E.B. - F.B.NETOPRICEM1M2M3215.6.2014market 11500145050145004260900062000011000315.6.2014market 21600160001600042672000670000-2000415.6.2014market
[Code].....
I am after some code that will hide tables based on whether they contain certain values.
If we take the following example: ....
i entered an item (ie, Aircard5740, cost = $99.99). This specific item must be compared from the 1st table (type of phone) with 7 different columns where does it fall the same. Once found, another item will be entered, (ie, America's Choice 450, cost = $59.99) and again will be compared to the 2nd table (commission) with 7 different columns, (while still based from the 1st item), and from this 2nd table, there's the commission amount be taken. what formula can i type to get the commission from the 2nd table?
View 2 Replies View RelatedI have 2 tables in excel that i need to merge into one, Table 1 has 10 colmns and Table 2 has 7. Both tables have as column 1 a heading of Material, this is the column i need to compare as table 1 does not contain a material description field, but table 2 does, the bold headings are the key ones, but other column information is required. I have attached a sample ss to show what i am trying to do.
Table 1 Col Headings
Material
MTyp
MS
R
Clt
L/O
Created on
Created by
how to create tables from XML with CSS.Below are the codes....
<?xml version="1.0"?>
<?xml-stylesheet type="text/css" href="feedrecipe.css"?>
<recipe_summary>
< percentages>Percentages</percentages>
<percentages>
<eggshells_heading>Eggshells</eggshells_heading>
<eggshells>5%</eggshells>
<wheat_heading>Wheat</wheat_heading>
<wheat>60%</wheat>
<sunflower_heading>Sunflower Seeds</sunflower_heading>
<sunflower>5%</sunflower>
<meat_heading>Meat and Bone Meal</meat_heading>
<meat>10%</meat>..................................
I am currently constructing a spreadsheet with an engineering application. There are standard size shafts that bearings must fit on. Once the design is run, i obtain a number which is usually inbetween the two standard sizes. I need the spreadsheet to select the larger one. for example, 184 must be looked up from
160
180
200
220
I need it to select 200, however and the moment using lookup function i am getting 180.
i'm building a spreadsheet where there is a list where users will input work shifts (those shift can be, for example, "M", "N", "T", "Me", "Ne", "Te"). i use two different columns to retrieve (using vlookup) values for M, N, T and Me, Ne, Te shifts. those values are in two different tables (one table for M, N, T shifts and another one for Me, Ne, Te)
for the M, N, T shifts column i use:
VLOOKUP(A1;$values.$A$1:$D$3;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2)))
for the Me, Ne, Te shifts column i have:
IF(ISNA(VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0));0;VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0))
this seems to works fine, but only for one shift per day. the problem is that workers may work more than one shift per day. is there a simple way of, in one column, vlooking up the values for every M, N, T shift, every day ignoring any Me, Ne, Te and the opposite in the other column?
Say I have 10 tables and want to copy them into one single table. Easy right? Just copy / paste.
The restraint with copy paste is, if I make an adjustment to one of the 10 tables, it will not be reflected in the master table. I would have to recopy the table every time I make a change.
I was wondering if this there was such a function that says: "Once done copying with X table, start copying Y table, and so forth."
I have a spreadsheet with two tables in it. Table 1 contains some data consisting of two columns both populated with data (where 1 = a, 2 = b, 3 = c and so on) and table 2 consists of two columns one column contains data and initially (numbers) column two is blank. Now I need a VLOOKUP that will look at the second column (red letters in the example) in table one, and where the same value (number) appears in table 2 populate the corresponding letter.
I do not wish to use filters as the table is extremely large. I have attached an example
I copied a worksheet with a pivot table as a new worksheet. I wanted to group each pivot table differently on each worksheet, but the changes I make on one worksheet is reflected on the other. Is there a way to break the "link" between these tables?
View 2 Replies View RelatedHow do you reference a table on another sheet so that when you sort that original table it doesn't change the reference you made on that other sheet.
i know how to do this when i am referencing a cell. for instance if i am referencing a cell on sheet 5 and that comes out to something like =Sheet5!B2, i know that when i sort the data differently in sheet 5, that reference will know to change to =Sheet5!B18 or where ever that original reference ends up in this new sorting. but if I do =Table5[2] or something, and i sort that table differently, the reference will change to the new data in that cell and will not follow old reference through the new sort.
Sync these two tables? the firs one is connected to a master sheet so when that master sheet is updated by entereing data, inserting rows or deleting them so is the table, the second table was created to hardcode data manually so it doesn't feed from the master. My problem is that if a row is inserted in the first table, the second table doesn't update that change. Is there any way to connect both?
View 2 Replies View RelatedI got a excel file which i use to add invoices on. But these invoices are numbered and in a book.
1 book has 50 pages, i have to write the amount of $$ on it. But i made this file to enter the amount of money on the invoice. And the sheet automatically gives me the total amount of money which was earned when the book is full.
Now i was wondering if its possible to let excel somehow add 50 new lines with the same formulas used in the first 50 lines. So once line 50 has info enter, excel will add 50 new ones with the correct info(book and page numbers).
Can excel do this with some sort of macro?