Create Functional Dropdown Menu To Update Expiry Dates?
Jan 13, 2013
I am creating a very simple spreadsheet to manage my gym memberships. It basically has membership number, first name, last name, membership type (drop down box) start date and expiry date. I have put in conditional formatting so that the expiry date goes red when expired but i want to try and automate the inputting of the dates so for example.
If i select '1 week membership) from the drop down box in the membership type box it will firstly change the start date to the current date (i think this is using the NOW() function) and secodnly changes the expriry date to todays date plus 6 days. Ofcourse i want the expiry date to increase depending on the selection so if i select 1 month membership it would be now date plus 28 days.
I am stuck as to how i can do this and from internet tutorials have been told it requires macro as it cant be done any other way?
I am trying to create a job data sheet as per below. Under 'Sales Item' I have a drop down menu to select the item sold. I am trying to set up an autofill formula to fill the 'Stock Cost' cell once we have selected the relevant product from the drop down menu. The data is on another worksheet on another tab at the bottom of the sheet.
Sales Item Sale Price Stock Cost Profit Per Item Installation Commission
I want to create a drop-down menu to choose dates and if I choose certain date the data of numbers (23 sets of number (4 digits)) will comes out in the next column beside the drop down menu of dates, then if the 23 numbers already comes out it will highlight how many numbers are the same in the previous date to the current date. So, each time I choose different date the data for that specific date (23 numbers) will comes out.. I try to do it but I get error when I change to different date the data for that specific date doesn't comes out.
Need to create a drop down menu for a cell that allows selection of 1 of the 5 to 10 alternatives by clicking on an item in the drop down. Have seen this done by attaching a down arrow button to the cell.
What is this feature called, and how do I go about setting this up?
I need cell d4 to display the number in column b next to the applicable insurance company when the insurance company is selected from the drop down menu in cell c4. How do I do this?
I've written a ton of VB macros that do various things to a raw data sheet. I want now to create a custom menu (in the the menu ribbon bar at the top of the screen). I'd like this menu to be used to activate the various macros for whoever has my add-in.
I've found several examples on how to create an add-in, and creating custom menus, however they all for when someone opens a workbook with the code, and then they remove the custom menu when the workbook is closed. I would like my custom menu to stay on the users ribbon bar no matter what workbook they have open. The only time the custom menu should not appear is if the user removes the add-in.
I have a list of expiry dates in excel, to save me manually looking through the list to spot up-coming expiry dates, is there a way excel can flag them for me i.e.: giving me 2 months notice of an expiry date? Someone even told me you can get excel to generate an email, not sure if this is true.
I have a training sheet where the training could expire in either 1 2 or 3 years, i am trying to get the conditional formatting for each to change color when the training is due to expire in 6 months 3 months and 1 month also to show red when expired
I have an excel sheet which contains user details and date of expiry of a particular service. I would like to send an email to user when the date of expiry is met.
I have created a database at work which collates various information on the sale of products. Part of this information is the date that each particular product is up for review (In DD,MM,YYY) format. Using the internal clock function I have produced a formula that calculates the numeber of days until expiry.
However, using this information I want to be able to run a macro that looks at accounts that have for instance 5 days left until expiary and populate a new worksheet with various information extracted from my 'main' database. For example, search all the porducts that are due to expire tomorrow and then populate my new workseet with data from cells such as
I am trying to change the colour of a cell based on the expiry date. I know I need to use conditional formatting but Im having trouble.
Colomn E from row 5 to 1200 have dates in them from as far back as 08/2013. I need to change the cells so they turn yellow after 28 days and red after 45.
E.g. 1. E953 has a date 07/09/2013, I need it to change colour as above, 28 days and 45 days. 2. E961 has a date 04/10/2013, I need it to change as above, 28 days and 45 days.
Can I formulate the entire E column so when I put a date in, the formula will automatically start.
All the other formula's I've seen involve starting from "today" or "now" .
I have a custom menu bar. Here is a part of the menu code. The SubMenu Items are various pipelines that am analysing. In a year time the user may add a pipeline. Is there any way to have a code to update this menu bar based on a changed list of ipelines?
Sub SetMenu() ZapMenu Set NewMenuBar = CommandBars.Add(MenuBar:=True) With NewMenuBar . Name = "QRA" .Visible = True End With ' Copy the File menu from Worksheet Menu bar CommandBars("Worksheet Menu Bar").Visible = False ' Add a new menu Set NewMenu = NewMenuBar.Controls.Add(Type:=msoControlPopup, Before:= _ 2)............................
I've got a workbook with multiple worksheets related to several subsidiary companies for each state & territory, resulting in 450+ worksheets in the workbook. I created a summary sheet with a dropdown menu for each state/territory that pulls in the relevant data using "=indirect". For example, selecting CA from the dropdown menu populates the combined revenue, expenses, etc., for all subsidiaries in CA. However, I've got 50 states plus some territories. I'd like the revenue, expenses, etc. for every dropdown option. Ideally, a separate sheet (or whatever) that lists all 50 states and the revenue, expenses, etc., for each one below (or across).
Is there any way to do this automatically so I don't have to select every single option and copy and paste the numbers into a different worksheet?
How to create a dropdown menu to pick from two selections?
Basically, I have a 'Status' column and the status will always be 'Open' or 'Closed' and I'd like you just to be able to click a cell in that column and select one or the other.
How can I write my code so that the drop down box always is visible ( like a combobox)? This code is correct and makes a drop-down list but I want it visible at all the time.
I'm looking for a way for excel to output data to a cell dependent on what I choose from a drop down menu.
For example. I have a drop down menu with 3 options. 1,2 and 3. What I want is that if I choose option 2, then in cell A2 (or whatever) would output the value of 2, say "2222". Or if I choose 1, then A2 would output the value of 1, say "12345".
In Column A I have product names. Each product name in column A is followed by several rows of data related to that product directly below it. The number of rows following any given product varies and is also dynamic as rows of data are added to other products above it.
I want a simple downdown menu in A1 that is populated with every product listed in Column A. When a product is selected, that area of the worksheet showing that product is shown.
I am building a workbook which will be used to quote cleaning jobs and i want to add a drop down selection menu with hidden values which will calculate timings for jobs - for example
if you select DOOR WITH VISION PANEL i want that to have a hidden value that is the time it takes to clean e.g. 1 minute the auditor then enters the number of doors lets say 3 and the spreadsheet can then calcutale that 3 doors with vision panels will take 3 minutes to clean
I have a successful dropdown menu that removes items that have already been selected through the dropdown list, however, I need it to be able to handle IDENTICAL items. At the moment, it can only handle unique items.
I'm trying to create a Rota Planner. I have a selection of shifts that need to be selected for each day. Some shifts are repeated (meaning that there may be 4 members of staff on the same shift). As ONE of each of the 4 shifts are selected, I then want only 3 to be displayed in the dropdown menu to be selected, until all of them have been selected.
I've tried to break apart the code that provides dynamic dropdown list for UNIQUE values but I'm tearing my hair out and getting nowhere.
I have a worksheet that have a dropdown menu with more than 60 names / project when I select a name / project all my sheet update per the project. My question is how to run a macro to print all records in the drop menu one time instead of doing it sheet by sheet!!!! and I want to print them pdf.
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.
Most of the information I need I am getting using standard web queries. But pulling data directly from drop down menus seems beyond the capabilities of a regular web query.
I only need the information that shows as the menu options. I don't need any information from the pages at those links. Nor do I need the links.
Actually all I really need is the option value information, and the date. So for the first item, I would be looking for, 7|2014|u 06/18/2014
I have a spreadsheet which is made up of 4 worksheets. I have a drop down menu on the front page (created with data validation). I want to be able to choose one of the 7 geographical areas from the drop down menu, then have a formula search through the other worksheets for rows of data with this geographical area in it. Any ideas on how to do this, I have tried with VLOOKUP but cannot get the formula to refer to the drop down menu for the first input, i.e. the geographical area that it should be looking to match.
I am developing a spreadsheet at work that is very similar to one that I made at a previous job. I am using the same formula in each spread sheet, but the one I am developing now does not work, where the previous one works perfectly.
I have a drop down menu created with data validation. Each possible selection is one or two letters of text, or the selection can be left blank. The drop down is cell A12, and an example of the if function that is used many times in this spreadsheet is as follows:
=IF($A$12="L",1,0)
In the previous spreadsheet, the function returns true only when the appropriate selection is made. In the latest version, it will return true if the appropriate selection is made, or if drop down selection is blank. I have been able to work around this with the following:
At the moment if a pupil has done foundation listening then I write their numerical result in, for example C3 or if they have done higher listening I write it in F3 (and it works out the UMS and then the grade), but I wondered if there would be any way to just put a dropdown menu in a column for each name, or change it so that I can type in H or F that will change which data is used? Because the UMS and grades are different depending on whether the pupil has done higher or foundation I am unsure how to format it
I am building a sheet that facilitates managers at our company decipher information that is given to them on a daily workbook I created.
I want to have a drop down menu in Col J, that refers to the major questions they will ask.
Time started (Before) Time Finished (After) Number of Cards (More than) Stops Per hour (more than) Amount of Time Taken (More Than)
I have set up in Col (K) a secondary drop-down list which based on the question selected in (J) will offer options for the answer E.G Time questions will make (K) refer to options based on time and selections based on amounts will offer numbers as an answer.
The elements in brackets are the parts of the formula that create the search option. E.G If select 'Time Started' it will search for all records that started before the time selected in the next col (K)
Right if that makes sense.. I have completed this. so I have the following: A question drop down list in (J), and an answer drop down in (K) which is dependant on the type of question asked. (I have attached an example)
I want to now have a totals box after the question where it displays a total amount of cols that are true to the details in the question.
After that (if possible) I will want to use conditional formatting to highlight the ones that reflect to the question.
Realistically if possible i wouldnt mind creating a question box, a box that allows them to select 'more than', 'exact' or 'less than' and then the specific value. Basically a conditional formatting box for dummies within the worksheet. Attached is a demonstration.
