I have a excel sheet with values correcponsing few names. I have a list for a Data Validation drop down. How to get all the data row wise for the name selected in the drop down.
View 3 RepliesI 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 want to do couple of things here.
1. put an empty field in a drop down box. If possible the empty field should be at the top from the drop down menu.
2. Update names in drop down box when I add names to the list
I have a range in a worksheet named "Product3" that has data down to row 30 that is linked to drop down boxes on another sheet. I want to add data to rows 31-40 to also show up in the drop downs. How do I extend that range?
View 9 Replies View RelatedI have a list for the user to enter in creditor name, balance, monthly payment and interest rate. In the credior name portion, I have used a Data Validation List (drop down menu of bank names) for the user to choose from.
Later on... The user types in the customers ACTUAL bank acount that they will be making payments from.
If the user types in a bank that is the same as one from the drop down list earlier on, I would like a dialog box to come up with further instructions.
Would anyone know if it is possible to extend the size of the list presented when using the data validation drop down? At present, it seems to default to 8 choices (even though there is a scroll bar) but I was wondering could the list Automatically display, say 20 choices?
View 3 Replies View RelatedI was wondering if there is a way to see all the choices on a drop list. Currently the drop list only shows 8 choices but I am wanting to see all 32 choices eliminating the need to scroll the list. I have attached a picture.
View 4 Replies View RelatedI am doing an internship in Germany, and I have this crazy project with Excel. I am not very familiar with all the equations.
I want to select a company name and a type of container from a drop down menu, and have the information show up on a different sheet assigned for that specific company for the right size container. It involves quantities.
I will attach what I have sofar.
Ladungstraeger(1).xlsx
Ladungstraeger(2).xls
I am currently working on a spread sheet that has a lot of data validation drop down lists. They were all working fine but now none of the drop down arrows are showing up.
View 3 Replies View RelatedThe drop down validation feature on my sheet isn't working. I have treble checked that the boxes for data validation have been correctly done. In another sheet the same data validation feature is working? Can anyone suggest a reason for this?
If you are stumped I can post a snippet of the offending sheet.
i need some formula to data validation with this criteria :
allow only text "DROP" and "check" ...
Sheet1 is my database and looks like the following:
HTML ABCDEFGHIJK
1RefNoSetSubsetStatus
2101lambsheepINP
3102catpetCOM
4103chickenbirdINP
5104milkcowINP
6105turkeybirdINP
7106honeybeeCAN
8107dogpetINP
9108boybabyINP
Sheet2 is for the user input, in which Columns H and I would have a data validation drop-down list and looks like the following: ...
I have two fields that do the same thing [preset selection vs custom entry], and I need to restrict one from holding any data if the other already does. One field is a drop-down menu, and the other is a decimal entry. It was easy to restrict the decimal from being entered if a drop-down selection has already been made - Data Validation prevents any entry unless the drop-down cell is blank. It's not so easy to restrict the drop-down, however, as it already uses List-type Data Validation to allow only values from a specified range.
Does anyone have any ideas for allowing the drop-down to function as normal if a specific other field or range is blank, but not allowing a selection to be made if that specific field or range already has data in it? I've attached a simplified example worksheet.
I need to put together a report for work.
What I want to do is this:
Create a drop menu. The options within the drop menu to be links (either to another Excel worksheet within the same workbook, or a WORD document).
I know how to create a drop menu, that is simple, but am not sure how to do the hyperlink aspect.
It seems when I actually insert>hyperlink onto the actual text of the drop menu (on the other sheet where i created the drop menu data), the hyperlink is not active in the actual drop menu itself.
for some reason my data validation drop down lists have dissapeared. The relevent cells indicate that the data validation has been set-up, but the drop down list is not available for some reason.
View 9 Replies View Relateddata validation drop down list find
i have a data validation drop list of week starting dates. the list works great, but
i have to scroll through more and more as the year goes on...
?can we have a letter sensitive find (as in an access combobox)...?
I want a table to display data based on which month i select from a drop-down list, the data is of course extracted from a different table. For example, in my final table( highlighted in yellow), i want to display the revenues, cost of goods sold..etc of April in this table when i choose April from the drop-down list, the data of the entire year is located in another table that i plan to hide, as we only need to review one month.
View 1 Replies View RelatedI 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.
I know how to use the copy and paste special to copy a formula from one row down many rows in that same column. When I do this the formula will automatically change by 1 number {such as =IF(ISBLANK(C5),"",VLOOKUP($C5,Sheet1! $B$3:D4891,3,FALSE)) then when I click on copy and pasted special each following row will change by one number ---- to =IF(ISBLANK(C6),"",VLOOKUP($C6,Sheet1!$B$3:D4892,3,FALSE))
Can this also be done if the row you want to copy and paste is a row with data validation / a drop down menu? I simply tried using the same process for a row that has data validation the row with the validation has for the source =INDIRECT($B$5)
Is there a way to copy this down about 20 rows and have the ($B$5) change in each row by one number {such as ($B$6 THEN $B$7, ETC...) Or do I just have to do the whole data validation process for each row?
I would like to populate data from 1st File(Name:-Master Data) to the 2nd File(Name:-Calculator) using validation list in the 2nd file in such a way that once the Incentive Calculation is done for any store the same data should get populated to the 3rd File(Nameay Out) automatically without manual copying & pasting.
1)Master Data File(1st File):-Contains all the relevant raw data for incentive calculation.
2)Calculator File(2nd File):-Contains a drop-down validation in cell D4(in the sheet Named:-Hyper) and this drop-down contains the list of the stores starting from H001 to H032. I have got the Sumproduct formula to link Master Data File & Calculator File via this Drop-Down list which is yielding the Incentive Amount.
3)PayOut File(3rd File):-This is the where the storewise Incentive amount should finally be stored. I need help in keeping the respective storewise Incentive amount to respective locations even though different store is selected in the Drop Down Validation in the Calculator File.
I will share a small data pertaining to 2nd File(Name:-Calculator) & 3rd File(Nameay-Out) to further explain of what result is expected.
The following figure shows the results of H012 store(when selected via the Drop-Down validation):-
File Name:-Calculator.xls(Sheet Name:-Hyper)
Drop-Down List(=D4)Contains the Store names & links the Calculator File & Master Data File.
H012(D4)
Particulars(B7)Month1(C7)FMCG Sales Target12884356FMCG Sales Achievement17748294
C8=SUMPRODUCT(--('[Master Data.XLS]FMCG'!$D$6:$D$221=$D$4),'[Master Data.XLS]FMCG'!$H$6:$H$221)
C9=SUMPRODUCT(--('[Master Data.XLS]FMCG'!$D$6:$D$221=$D$4),'[Master Data.XLS]FMCG'!$I$6:$I$221)
The Above Sales & Target achievement for store H012(for FMCG Department) yields the Incentive amount in the same Calculator File(Sheet Name:-Hyper) as follows:-
Department(B17)Designation(C17)Incentive Details(D17)Month1(E17)FMCGFloor ManagerIncentive4641FMCGSupervisorIncentive3370FMCGStaffIncentive2276
Now finally the data from the above Incentive Calculation should get populated to the 3rd file named Payout(Sheet name:-Sheet1) as follows:-
Month1Store Name(A2)Staff Incentive(B2)Supervisor Incentive(C2)Floor Manager Incentive(D2)H012227633704641H013 H014
H015
Now suppose If I select H013 from the drop-down cell in Calculator file the incentive amount should get calculated and the same should get transferred to the Pay-Out Sheet below the H012 store incentive amount without changing or altering the H012 incentive calculation and so on for H014/H015 etc.
I have not found a way to control the width of a validation drop down menu. I have 3 drop down list. Two of them are in wide column cells and one a narrow column cell. For some reason my narrow column cell gives me a wide pull down even though the length of the data in this field is only 3 to four characters.
View 2 Replies View RelatedI have noticed that all the cell dropdown lists i have (created by using Data | Validation | Allow | List) are very difficult to read its as though the font is size 8 or 6 or even less, is there a way of displaying the dropdown contents in a larger format? i have tried changing the font size of the named range and of the cell holding the dropdown but to no avail.
View 4 Replies View RelatedI have a workbook with 7 sheets, the drop down list operates correctly on all but one sheet, it used to work on all sheets. The arrow on the right of the selected cell dose not appear onthis particulr sheet. I have rebooted the PC and double checked the in-cell option. I have search the forum for similar problem and found the same problem but no remedy.
View 2 Replies View Relatedis it possible to find the number of row for selected value in cell with data validation?
for example:
sheet2
cell G13 have data validation format
Allow = List
Source = 'sheet1'!E:E
Ignore blank = yes
sheet1
columnn E contents file names with hyperlink
FileName1.txt
FileName2.txt
FileName3.pdf
.....
FileNameN.txt
I choose FileName3.pdf
I am trying to do here is select a value from a drop down which is linked to a formula which triggers the macro shape..i have all that down already... but the problem is that I will have multiple values in the same drop down and I needed to figure a way to delete the previous macro shape in that range. And so i have created the delete all shapes code below but it seems whenever I use the code it deletes the data validation drop downs
here is what I have so far...
Function Macro()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _
Select
End Function
Function CIRCLES()
'
DELETEALLSHAPES
'
'
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
I have a query with regards to Counts
This is my below data.
No.Request NoDateSilverGold
1456661-Dec-20121
22018561-Dec-20121
32089561-Dec-20122
42099721-Dec-20128
52012121-Dec-20122
62068782-Dec-20122
72075952-Dec-20123
82045682-Dec-20123
92032252-Dec-20122
102015852-Dec-20121
in other sheet i have products Data by date wise
1-Dec-122-Dec-123-Dec-12
TotalRequest countTotalRequest countTotalRequest count
ABSilver2
Gold12
My query is how to get the total Request Count for Silver and Gold by date wise. Eg: Answer should be Silver has 2 (counts) & Gold has 3 (counts) How to arrive to that.. i have tried.. countif, countifs....
I need to filter data on criteria wise and to move on specific sheets accordingly.
Example: sheet 1 contains all the details such as starting with AAA - 10 rows, BBB - 12 rows, CCC- 15ROWS ,DDD-13 ROWS etc in column 1.
I have created separate sheets for AAA, BBB, CCC etc
now I need all the details of AAA (in sheet1) to be moved to specific sheet AAA which i have created and so on.
I tried the below coding but there are few dependencies found.
Selection.Insert Shift:=xlDown
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="AAA"
ActiveWindow.SmallScroll Down:=-6
Rows("169:184").Select
Selection.Copy
Sheets("AAA").Select
ActiveSheet.Paste
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