I 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 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.
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 have created a color coded calendar on a spreadsheet for work that shows when a book will be published depending on when it comes into our warehouse, this is on sheet one.
I would like on sheet two to have some kind of filter or statement that would eliminate the need to look at the calendar, I.E. the user would just have to type in the date or select the date from a list and excel would show you what day the book would publish on using the data from sheet one.
I can imagine that this is hard to picture so i have attached the file as well.
I have a column with various drop down menu boxes and I need to add another selection to the menu list. My problem is, it's been so long since I did the drop downs, I have forgotten how to do it..
View 3 Replies View RelatedI have three lists in the first sheet 'Food', 'Quantity', 'Points Value'
Each food (i.e. tuna, pasta, anchovies) has a quantity (i.e. 40g) and a points value (i.e. '50'). The first list ('Food') has been copied as a drop down list in the second sheet. When selecting a food, I would like the cells 'quantity' and 'points value' to pre-fill - taking the data from the first sheet.
i.e. if say Anchovies has the quantity value '30g' and Points Value '2' on the first sheet, then in the second sheet if anchovies was selected from the drop-down menu, these values would add themselves. how to do this? I have attached a sample file shwoing what I want to do.
I am trying to create a drop down menu in Excel to display certain values but it's not working. I want to input certain data in the cells. eg, 019, Quebec, Agent, Direct etc. I want to create a drop down list where if 019 is selected, all 019 entries would show up hiding all others. When nothing is selected all values would show up. I created a list but if I have 10 "Agent" entries in the cells, the drop down list also shows 10 "Agent entries rather than letting me select "Agent" and then displaying all agent entries below. I don't know if it makes sense but I basically want to make it easier for people to look for certain entries that they need rather than going through the entire spreadsheet to find all 019's.
View 2 Replies View RelatedI have a Drop-Down Menu that looks like:
BOS
PIT
COL
NYR
using List with source =Lists!$B$2:$B$9, which is just the validation list
My Table:
Team Player
BOS Bergeron
BOS Krejci
NYR Nash
... ...
My goal is to limit the end user from choosing BOS more than two times from the drop down menu
I have a list of values (say a, b, c, d, e) and creating a drop down menu with these values in say A1. Say I chose 'c'. Now in cell A2 I want to be able to choose from the same list without being able to choose 'c' again. In cell A3 I want to be able to choose from the same list without being able to choose the values lalready chosen in A1 and A2. and so on.
View 2 Replies View RelatedI 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 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.
Is there a way to have a data validation drop down menu but at the same time allowing users to enter free text as well.
View 3 Replies View RelatedUsing Excel 2010, I set up a simple workbook with a spreadsheet on one page and the ranges for data validation on another. I successfully linked the fields in the "Category" and "Tender" columns with the data on the second sheet, making nice drop down menus. Everything works fine on my computer. However, when we open the workbook on a different station, the drop down menus disappear. If you highlight any of the "Category" or "Tender" fields, the little drop down prompt is missing; they behave like regular, un-formatted fields. It appears that the data validation became un-linked.
We tried opening versions with certain fields locked (Mainly the Total, Breakdown, and Summary areas, but the fields with data validation were still editable), and also completely unlocked documents. Every time, the drop down lists were lost when opened on different stations.
I've attached the completely unlocked version : 00_FLEX TRAVEL EXPENSE REPORT_v3.xlsx
Forget to check a box or something? This seems way harder for me to figure out that it should be!
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.
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 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 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 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 RelatedI 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
'
'
