I have set up a sheet recording an each individual's turnover for my business i.e., hours worked, etc and have set up a data validation table for drop down options of Jan, Feb, Mar,etc....
Rather than creating more work for each month on the worksheet, how can I enable the drop down to contain the data specific to each month?
How do you sum data from several cells that derive there answer from a lookup formula that is base on a selection from a drop down menu in another cell. The 6 drop down cells have the same ten items to choose from. Once those items have been chosen I am trying to summarize those six cells to ge a total for each of the item chosen.
View 9 Replies View RelatedI am trying to make a drop down list and based on what I select from the drop down needs to populate a different drop down list with information based on what I select.
For example: if I have Sheet1, Sheet2, Sheet3 and Sheet4.
Inside of Sheet4 (My Main Sheet) I have a Data Validation (Drop Down list) in Cell A5 and Cell B5
I would like to choose Sheet1 from Cell A5 and in that same sheet (Sheet4) in Cell B5 I want to have a drop down with a list of names that are located in Sheet1 in Column A:A.
Or If I would've picked Sheet2 in Cell A5 of (Sheet4), I would like to see a drop down with a list of names in Cell B5 (Sheet4) that are on Sheet2 in Column A:A . Is this possible?
So far I could achive it if information I want is located on the same Sheet. But I need it to work if info is located on different Sheets
I have a spreadsheet I use to track reprimands for our employees. Reprimand Counseling Spreadsheet Example.xlsm. Previously, I had to read the entire reprimand in order to determine the policy violation(s) and then manually enter those reasons into the cell. We have a new standardized form, where the manger selects the reason(s) for the reprimand. I wanted a drop down box that I could select multiple reasons for the reprimand and have all the reasons appear on the same line, as well as being able to remove a reason from the line by selecting it again (so I wouldn't have to start over, in case I selected the wrong reason). I was able to find this code, on [URL] .... to accomplish that.
Same Cell Add & Remove
I also need to be able to manual edit some of the reasons. For example "Missed Meal Period(s) #___". I will need to be able to edit that item to read "Missed Meal Period(s) #2". I was able to find this code, on the same website as the previous code, to accomplish that. However, this code includes a line break, which I do not want.
Edit Same Cell
[Code] .....
Since I need the code to work on each worksheet (CIVI - SOCR), will I have to enter the code on each worksheet or is there a better way to accomplish this? Is there a way to combine these two codes, while simultaneously removing the line break? I need to select multiple reprimand reasons in the same cell and edit the reasons, if necessary. I've read and reread that article for hours, but I can't get it to work.
I have dropdown boxes in cells B11,B14,B17,B20,B26,B29,B32,B41,B44 of my worksheet. I would like to use a command button that can reset all dropdown values to blank.
View 9 Replies View RelatedI trying to do a sheet with one combo box (dropdown list) where I want the user to be able to choose "add rows" to make more boxes appear. My idea to solve this was to put all the boxes in the sheet, and then create a macro that either hides or shows the rows with the extra boxes. Now I have a problem that hiding the rows just does that, and only that. The rows disappear, but the combo boxes stay visible (but ends up on top of eachother).
View 2 Replies View RelatedI'm trying to incorporate multiple conditional (Dynamic?) drop down boxes to enable me to get an appropriate code at the end of the process. This will be used to product data entry and the code will tell Amazon where to post the product. I have many thousands of codes that i need to access and the easiest and most accurate way is to break down everything in to category and sub categories until I eventually get the code I want for that specific product.
for Example:
1730708031Clothing/Baby/Baby Girls 0-24m/Hoodies & Tracksuits/Tracksuit Bottoms
To return this code I would want to select Clothing, which returns a list which includes baby which in turn will give me a list that includes baby girls and so on and so on until I can select Tracksuit bottoms that will return the code.
Each list is dependent on the previous choice
As I have thousand and thousand of potential codes creating range list for each potential category and sub categories is not really feasible and would be very difficult to maintain when changes occur.
With this in mind I have created a code list that breaks the above example so that each category is now in it's own cell and there can be upto 8 (as I've seen so far) potential conditional categories before you return the code and ideally I would like to use this as it very easy to create and update. (see attached)
I might be able to use filters to return the appropriate list each time but don't know where to begin.
ClothingCodes.xlsx
I'm working with Excel 2002 on Windows XP.
I want to select more than one drop down box at the same time, and copy them as well as rename the cells they refer to.
For example: in A1 there will be a box with LinkedCell set to A1 and ListFillRange set to B1:B5. I now want a similar box in A2, with A2 as LinkedCell. However, when I copy past the box in A1, it still refers to A1 and B1:B5 (the last part is ok). I can easily change A1 to A2 by just changing it in the properties, but the thing is that I also need these boxes in A3:A100. Is there any faster way of changing this?
Also, the ListFillRange might expand in the future. Therefor I would like to know if its possible to select the boxes in A1:A100 and change the ListFillRange to B1:B6 for all at once.
I have 3 dropdown boxes I created using the control toolbox which pull corresponding information from 3 different columns. I would like to be able to format them so that upon data entry in the first dropdown box, the subsequent two boxes auto populate with the corresponding data. Currently I have to select the information for each dropdown box manually.
View 6 Replies View RelatedI am trying to create a price calculator for a particular product line which has multiple sizes and configurations. I would like to use drop down boxes so the user can select the size, type and attachments which will be narrowed down by the previous selections.
I would like the formula to be able to calculate the cost of the components based on the selections made. Based on some research I believe this can be achieved using data validation, indirect function and the vlookup function, however; I'm not quite sure how to go about this.
I have attached the document I have started for reference.
Chain Sling Price Calculator-4.xlsx
Using Excel 2007
I have six colums (Date; Skill; ST Hrs; OT hrs: Area; Type). I copied and pasted below a small portion of info from the spreadsheet. I know when I copy and paste into a post the information under the headers don't line up exactly...it appears offset, so basically "Date" is of course the date you see..."Skill"'s columns has the first item as nb-mech b..."ST Hrs" first item will be 0.00..."OT Hrs" will be 1.50...."Area"'s first item is "Log FM NO"....and "Type"'s first item is MX07
And there are 17,000 rows of this info and forever growing...a lot of repeated items in each column such as you can see "blaster" has multiple entries on 4/1/2012.
What I am attempting to do which I have not firgured out yet (i have tried array formulas but they bog down the pc forver and couldn't figure out exactly how to make it give me the results I needed...and I also tried sumproducts but that didnt get me what I wanted....and it is being requested of me to NOT make a pivot table but a chart.
What I am trying to create is is the following:
1 - cells to plug in a date range for example cell A1 I plug 4/1/2012 and cell A2 plug 4/30/2012
2 - drop down boxes for columns Skill; Area: Type, which show only unique records in each column
Once I have that, I could then filter all 3 itmes and have only items show in the date ranges I picked and present ONLY the SUMS of columns ST Hrs and OT hrs.
Once the SUMS show up, I can then have cells off to the side that will give a percent break down of what percent of the total hours (for the chose criteria) are ST Hrs and what percent of the total are OT Hrs....and this percentage break down will show up in a Bar Chart, per day chosen in my date ranges....I'm thinking one simple stacked bar per day in the chart.
So say, based on the info below, I wanted to see the total ST Hrs and OT Hrs from 4-1-12 to 4-2-12 for a nb-blaster in LOG FM NO and type MX06.....my result would be 0.00 ST Hrs...23 OT Hrs.....and my bar chart would be one simple bar peaked out at 23 on day 4-1-12.....and within the bar it would be 0.00% ST Hrs and 100% OT Hrs and maybe the total hours for each showing also..don't need that but it may be useful.. ..and no bar for 4-2-12 (because the criteria picked had no hours for 4-2-12)
I have been messign witht is all week and just can't nail it.. I do remember a while ago I would have been able to load the spreadsheet into the post and people could see it in excel...is that option to do that no longer on this site?
DateSkillST HrsOT HrsAreaType
4/1/2012nb - mech b0.00 1.50 Log FM NOMX07
4/1/2012nb - scaffolding0.00 8.50 Bogs NOMX02
4/1/2012nb - coatings mech b0.00 3.00 Log FM NOMX07
[Code].....
I am currently trying to make a digital time card for my place of employment. I has an odd set up and odd date ranges. The pay period starts on the 21st of each month and ends on the 20th of the next month.
The time cards have 5 columns for each week starting on Mondays going to Sunday (also weird its not Saturday to Sunday) What I was hoping to accomplish was being able to select the starting month of your choice for pay period and have multiple cells update the week date range.
Date
Date
Date
Date
Date
**Here would be the drop down month select.
Sept 21 - Sept 22
Sept 23 - Sept 29
Sept 30 - Oct 6
Oct 7 - Oct 13
Oct 14 -Oct 20
My data is in colunm set #1. I want to create from the data set in column set 1 the data set shown in colunm set #2. I want to automate the process. The program needs to regognize if a name appears only once or more than once. If it appears more than once the data needs to be merged as I highlighted from yellow to green.
View 4 Replies View RelatedI have a file that another person created and instead of traditionally saving, the only way to save file is by hitting X in top right corner and then a series of boxes pop up directing the user on what to do. I looked at macros within file and couldn't find anything really relating to the saving functions of document. This is a complex document and I'm starting to think that it isn't a macro. Is there any area in excel that allows you to adjust save settings? All I'm trying to do is change the wording in these pop up boxes but cannot find the source. Attached is screen shot of first box that pops up.
View 4 Replies View RelatedI made up a form of 30 text boxes to simplify entering data, but seem to be having a problem inserting it easily back into my excel file.
I would like to use a simple Do While loop to shorten my code but can't seem to change my text box correctly.
My text boxes all have names like this txtap1run11, txtap1run12, txtap1run13...
As you can see all I need to do is change the last number of the text box name.
I can't figure out how to do that though. I came up with this, I don't have the do while loop in.
I'm writing an IF statement referring to a cell that contains the character "C" and comparing it to a blank cell, and it's returning True, while it should be returning false because they're not the same.
View 6 Replies View RelatedI want to change value if i change products from drop down list.....
For more information please find attached file: Book.xlsx
The comment boxes keep changing shape and location in my spreadsheet. Some are completely smashed together, while others are extraodinarily wide. I had them all to the right of the cell they applied to; but now they are all over the place.
View 1 Replies View RelatedI need some code to use text boxes to change the data in certain cells. Basically I want 3 text boxes to appear one after another. The first box should add text to cell T1, then 2nd to cell T2 and the 3rd will add text to cell T3. All these values should be added at numbers.
View 2 Replies View RelatedI want to do is use a userform to automatically create a graph from the data that is shown in the worksheet. The data in the work sheet will grow and shrink all the time. Am i right in thinking I am meant to create a dynamic range? I know how to do it but am not too sure what I am meant to be naming. And secondly I am not too sure how I set up the button to produce the graph.
View 4 Replies View RelatedI am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:
First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses
if I chose Hats it should not show up in the second drop down
second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?
I have come accross this calendar online and it suits my requirements, however, the month s currently static and the year is dynamically controlled by a spin button.
The formulas in the relevent days are as follows
Day 1 =IF(AND(YEAR(JanOffset+1)=calendarYear,MONTH(JanOffset+1)=1),JanOffset+1,"")
Day 2 =IF(AND(YEAR(JanOffset+2)=calendarYear,MONTH(JanOffset+2)=1),JanOffset+2,"")
and so on, I need the formula and the spin button the also change the month as well as the year.
Please see attached file for details.
Just wondering if there is a formula where I can change the date an employee started working into the number of months employed??
Example, John Smith started here in January 2011. I would like the formula to return 8 months.
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View 2 Replies View RelatedI am trying to get the brown boxes on the master page to change colour brown for occupied and green for vacant. I would like this to happen when I change the tennancy type in each tab B1,B2,B3 ect. I was trying to use name ranges to get this to work, But I seem to have stuffed it up.
View 5 Replies View RelatedI have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
Please see the attached workbook.
I would like to create a bar chart which is linked to a drop down box (from data validation) that allows me to choose of the 3 sets of data to show in the x axis.
So if i choose "HALF YEAR", the bar chart will have two bars (one is "jan-jul" the other is "jul-dec") with the green section data if i choose "QUARTER YEAR", the bar chart will have four bars ("jan-mar", "apr-jun", "jul-sep", "oct-dec") with the blue section data if i choose "INDIVIDUAL MONTHS" i have twelve bars....with the red section data.
I have a dropdown list (it is tied to a sumif function and it works perfectly) but I also have some images and when a different selection from the dropdown list is selected I want the images to change. Is this possible using sumif or is it possible at all?
View 2 Replies View RelatedI am trying to do 2 things.
1) I want to make a drop down box with 3 differnent sort orders options in it.
- Sort by date
- Sort by Customer
- Sort by priority level
Once they select a view, it will then sort the rows to that view.
2) I want to do a filter box, so if I type a cutomers name, it will filter to only show the rows related ot that customer.