The worksheet is basically a sales funnel and I want to use a drop down menu selection in ever row of column Z to update the relevant cell, based on the selection, in the same row, column Q to Y, with a date stamp which will then not change as the drop down is progressively changed to suit the relevant stage of the job.
View 2 RepliesCreate a dynamic chart which is based on multiple dropdown menus. I'm trying to design a chart which shows figures per city. In the first dropdown menu you can choose a city.
The other two dropdown menus will determine the date range of the chart.
How to create this chart ....
I am looking to create an index array. the conditions for the array are based on drop down menus. I want to have it that if the drop down menu has * in it, the array should calculate everything rather than a specific value.
={IF(ROWS(I$14:I14)>$H$7,"",
IFERROR(INDEX('Sheet1'!$C:$C,SMALL(IF(('Sheet1'!$T:$T=Sheet2!$H$2)*('Sheet1'!$U:$U=Sheet2!$I$2)*('Sheet1'!$L:$L>0),ROW('Sheet1'!$T:$T)),
ROWS(I$14:I14))),"")}
In the SMALL array I have three conditions:
1.'Sheet1'!$T:$T=Sheet2!$H$2
2. 'Sheet1'!$U:$U=Sheet2!$I$2
3. 'Sheet1'!$L:$L>0
The array works fine but only if I place an actual value in the drop down cells which are H2, I2. The third condition is just to ensure it only calculates the array for rows that have numbers above 0. My question is how can I amend it so that selecting "*" in the drop down cells will calculate for everything?
I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.
In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.
I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:
If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100.
If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80.
If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60.
If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95.
If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75.
If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55.
If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92.
If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72.
If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.
How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.
I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.
I want to put a permanent date & time stamp in cell A1 (date) and cell A2 (Time) when cell A3 is populated by any character which would be an inputters initials.
I'm thinking that it will be an "IF" statement but then I am getting confused about how I make it not update when the spreadsheet is opened at a later date and time.
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedHow can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies View RelatedI have created a User Form that has been tested and is working properly. Now my last step is to do the following:
I would like to Display the User Form (coded as CommRM) if in Column C (can be any cell as it is a large worksheet) someone selects "Retail News" from the drop down option.
The code that I have been able to create so far is below. The problem is that when I select Retail News from a drop down in Column C the user form does not appear.
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range (C14, C3333).Text = "Retail News" Then Call UserForm_Initialize
End Sub
Private Sub UserForm_Initialize()
Load CommRM
CommRM.Show
End Sub
I have a excel file to keep track of sales pipeline. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows once a task is completed and can be moved to another sheet. In Prospects sheet I have a column for " Stage" and here you have to select from a drop down menu, either "Prospect", "Contacted" "quoted" "WON" "LOST" What I would like, is that once you have selected one of the sales stage, the entire row or entry, will be automatically moved to the right tab from the "prospects" to a new sheet according to each stage.
SalesPipeline.xlsx
I am trying to create a sheet for identifying work hazards based on the job assigned. So what I have set up is a drop down list to select the job type. Once this job type is selected I want the pre written hazards to autopopulate in the hazard box. I would also like the required PPE to appear in the PPE box for the job selected.
I have 6 different choices in the drop down list. For each choice I have the hazards and PPE in a separate sheets in the worksheet.
Is there a way to get this information to autopopulate?
I am trying to develop a worksheet that will adjust dates based on a selection from a drop down box. I can do basic formulas in excel
I am trying to get the drop down box in "Revisit Type" to include three selections ("Sanitation", "Spark", and "High Five"). Once the dropdown box is created I want to be able to make a selection and enter an initial visit date under "Initial Visit Date" then have the "Earliest Return Visit Date" and the "Latest Return Visit Date" automatically populated based on this information below:
Sanitation or Spark = 10-30 day window
High Five = 10-21 day window
I am at a loss . I have attached a sample of the workbook for reference.
Sheet 1 has a table of prices. There are 7 columns labelled 2 thru 8 and two rows, labelled 1 and 2. Each combo has a separate price.
Sheet 2 has a weight specification (1 or 2 referring to the rows in sheet 1) and a drop down menu with options 2 thru 8.
When I choose a number from the drop down menu, let's say 5, I want to populate another cell with the value from sheet 1 of column 5 on row "whatever the weight spec is", let's say 2.
I'm wondering how I could display information based on the user input through dropdown selection. I know how to make a dropdown-list already.
Example: A1 has a dropdownlist with Apple and Banana in it. If I select banana then I want B1 to display "8" and if I select Banana I want B1 to display "4".
Changing the data based on a drop down selection. My drop down list is based on the months I need the data (percentages for each person) to change depending on which month is chosen. So If I select Jan, the percentage fills in for each person. I've attached an example.
View 3 Replies View RelatedI am trying to create a spreadsheet which allows me to select an antibiotic from a drop-down menu (refer to cell D1) on Sheet 1.jpg, which would then color the cells below based on the color-coded coverage based on another sheet (refer to D2-D17 of Sheet 2.jpg).
View 10 Replies View RelatedI have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies View RelatedThere's a table with all the raw data in it
camp name
session name
to put in the correct format I am thinking of usinf drop down boxes so that the user can selct the camp name from drop down and based on that the session name can be populated and alos the rest of infomration of that accput in to the specified fields
I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.
View 2 Replies View RelatedI am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible?
For each amount the user enters (for each Code Number) a date will be displayed below the amount.
I have a dynamic drop down list using OFFSET and want to change a selection of cell colors based on the drop down selection. My drop down list is located in cell G4 and the cells I want to change are
=$C$9,$C$10,$C$11,$B$11,$B$12,$C$12,$B$13,$C$13,$C$14,$C$15,$H$15,$H$14,$H$13.
These are random cells. There is no value in the cells, I just want to change the color from green to gray.
I have a dropdown list selection on the form for types of machines. The list of machines, their sizes and related noise level are in a chart on worksheet 2 and I'd like to automate the form so that when the machine is selected, the related noise level auto populates.
The dropdown box has been put in the form using the data validation function.
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
Need achieving the following:
I have 2 sheets in one workbook, the first sheet named D1 where I have dropdown menus and the second sheet called reference where I have fixed data
what im looking for is when the user select an option from the dropdown menu on cell b3 on "D1" sheet based on the selection an entire table (4 rows by 3 columns) called "Default" from reference sheet to be automatically pasted into the D1 sheet.
I have two sheets that I am using. sheet 2 has a list of experiments in column C with information regarding the experiment in columns F - J. On sheet 1 I have a dropdown box with a list of all the experiments. When I select an experiment from the dropdown box I would like to populate columns K - O on sheet 1 with the information from columns F - J for the selected experiment on sheet 2.
View 9 Replies View RelatedI 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
I am new to VB macros, I am trying to figure out how to color a tab Green based on a selection from a drop down list.
View 9 Replies View RelatedFor example, I have a master sheet that has a drop-down list of the months January and February, which have their own sheets. I am trying to populate the sales of the week 1, 2 ,3 ,4 in the master sheet based on the same data for the month chosen.
View 2 Replies View RelatedI have 5 cities and each city has a set of data in separate tabs. Right now i have data pulled for all 5 cities in a single sheet but its too cramped up..
I want to use a drop down list (data validation) and if any of the 5 cities are selected in the drop down, the data from that particular city (tab) should be pulled up. This way my sheet size will be reduced 5 time which will look good.
A common v lookup wont work because the data has to be pulled from 5 different tabs for 5 different cities.
I've got a drop-down menu with four options in it that match up with the cells D11:D14, and next to those I've got spots for counting the totals (E11:E14). Next to the drop-down I've got plus and minus buttons, and I need to make it so if I have the top option selected in the dropdown and press plus, it adds +1 to E11. Second option and the same plus button, +1 to E12, and so on.
The purpose is for a call tracker, the employee would select the reason for the call (maybe more added later) and hit the button to add the call to the report.
I've looked so I know you normally ask for what I've got so far, and that's nothing. I've got all the other buttons wired up but I don't even know where to start with this one. I can include a screenshot if necessary.
I am trying to limit the user from selecting only what has been selected from the first selector. The selection are based on a drop down (data Validation list)
Excel 2007BCDEFGHIJ1LOOKUP Table2Should only allow from 3only what availble from First SelectorBus/Cntry Level 2Bus/Cntry Level 14ALLALL5First SelectorSecond SelectorAPACALL6TerritoryAPACAPACAPAC Regional Office7APACAsia Licensees8APACAustralia9APACChina10APACHong
[Code] .........