I'm using Excel 2010. I have developed a calculator (attached) to make life easier for my colleagues. It works perfectly fine. But one thing keeps bugging me.
In Cell C3 - You have the option to choose "Daily" or "Weekly" (Drop Down). This selection effects your selection for Cell C6.
If you choose Daily in Cell C3 then your options for Cell C6 are restricted to select/enter an amount between £0.00 and £6.00.
If you choose Weekly in Cell C3 then your options for Cell C6 are restricted to select/enter either £0.00 or £12.00.
This works perfect.
However, if I select "Weekly" and then select " £12.00", all my results are perfect which is exactly what I want BUT if I then go back up to C3 and change "Weekly" to "Daily", the £12.00 in C6 remains (though the data validation for "Daily" is restricted to £0.00- £6.00) and all the results are consequently wrong until C6 is changed.
The obvious thing is to delete the £12.00 from Cell C6 or introduce a "clear" button BUT I don't want my colleagues to do this as it leaves the possibility of error and since we are dealing with money, I can't have it.
So what I would love and what would complete this calculator and deem it ready is if every time Cell C3 is selected (i.e. from the drop down list you choose Weekly or Daily) it as a result resets Cell C6 to £0.00.
My Excel 2007 has "auto complete with a drop down list". When I type in the first character, a drop down list appears listing all the entries in that column tha start with that character. I click on one of the entries and the cell is "auto completed" with that entry. Sure is handy. I try to find that functionally in Excell at work, Office Professional 2010, and no bueno for kaki.
I'm trying to create a set of three dynamic/dependent drop down lists of date values, based on a set of imported data. Depending on whether the preferred choice is to list all dates in a range or just dates on the 15th, the available start and end drop down lists will change.
In addition, if possible I would then like to display a list of valid dates (not sure if this needs to be VBA).
The attached sheet shows/explains what I am attempting but here it is as well:
If B3 is set to show all dates in the month then:
the first dropdown option for C3 will be the first date of IMPORT (D2) the first dropdown option for C4 will be the C3 value, to the last date in IMPORT (D) column
If B3 is set to show 15th of the month then:
the dropdown for C3 will be the first date of IMPORT (D) column with a date of 15th the dropdown for C4 will be in the range of C3 to the last date of IMPORT (D) column with a date of 15th only dates of 15th will be listed in both C3 and C4 dropdownlists
General
the dates in IMPORT column D are taken from IMPORT column A, B & C, which will be imported and will always be date sequential the number of rows of date entries will change with every import but there will never be any blank rows between dates the values in IMPORT columns A, B & C will always be numerical, and can be referenced if required [ideally] the LIST OF DATES will be populated with the date range, based on the option selected in B3
I'm fairly familiar with Excel (2010) and I have also done some VBA but I'm new to dynamic, nested dropdown lists and I'm unsure what the most practical way is to achieve this.
I am using windows 7, excel 2010 I have a work book, my price list is sheet one and my list of customers and what discounts they get per item in sheet 2.
I choose customer on sheet 1, say i choose a & L sales, well on sheet 2 it shows they get a discount of 45 percent. so I want the 45 percent to show in cell 4r on sheet one, but if the dropdown box shows customer a & V it should show 35 percent because that is what it is on sheet two in cell 3c.
sheet two has 158 customers and each get a difference discount for that product
Creating a spreadsheet in Excel 2010 and am creating the dropdowns from a separate sheet in the workbook. There are no spaces and I don't know why I keep getting the "The Source currently evaluates to an error."
I am entering the Data Validation to reference the first cell I need "=INDIRECT(H3)"
I have put the spreadsheet on dropbox. When you select the "Sequencing Platform" drop down, it need to give just the dropdown for the platform selected.
How can I get the dropdown list to not include blank cells in the list reguardless if the sort filter is used or not.
Sheet 1 (STATS) is the dropdownlist Cell B12 Sheet 2 (Orders) is the data, I cant convert to tables because the cells are active and storted Column B2: is the data named.
I am looking to insert a drop down box in Excel 2010 which will allow users to select multiple items. At the moment I have data validation drop down lists in other columns, however I need one column to allow for multiple selections.
Basically I have a drop down which allows for a region to be selected, my next drop down (in the next column) I need to allow users to either select "ALL", or multiple countries which fall within the region selected previously. I need a check box so that users can select multiple countries, and deselect if an incorrect country is selected in error.
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output A | B Z 1| 10 | a 10,a,b,c 2| 10 | b 11,a 3| 10 | c 12,a,b 4| 11 | a 5| 12 | a 6| 12 | b
I am currently using a pop up calendar in Excel 2010 that with CTL+SHIFT+B that a calendar pops up, you select your date, and then the calendar closes. What I now need is to write a code or formula or something that when I click in any cell in column F lets say that the calendar opens automatically in that cell and then still close automatically after the date selection is made.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
I'm new to excel and was trying to make a workbook that will have a drop down list populate based on a user input in two separate cells. Basically, i have columns: name, pages, and books. I would like to have the drop down list match data by first finding the amount of books read, then finding the amount of pages read. Then any name that meets the criteria will be available in the drop down list. How can I do this?
I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.
So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.
Currently, it gives me the change to apply the following currencies:
- $ English (US) - £ English (UK) - € Euro (€ 123) - ¥ Chinese (PRC) - fr. French (Switzerland)
Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.
[URL]....When I select a name in column B, it populates column C's dropdown with that name's list of units. When I select a unity type in column C, I'd like it to automatically populate column E in the same row with the value associated with that unit. The values associated with a particular unit can be found to the right of the sheet. So if in row 2 I select Blake in column B, then Unit 5 in column C, I'd like column E to be populated with 14.
Edit: using Excel 2007.
I attached stripped down version of the sheet which is small enough to post. None of the things I removed should affect the topic at hand. The full sheet is available at rapidshare.
In Excel 2010 I am attempting to replace values in a cell, the right two values with "00", but am having difficulties with the correct '=replace' function.
I am using Excel 2010 .I have set up Data validation for a dropdown box so I can select from a list of items. In the old versions of Excel the actual drop down arrow used to appear in each cell. In the version I have, the drop down arrow only appears when you select the actual cell. When I did the validation I checked the " In-Cell Dropdown", but it still doesnt put the arrow in the cell. Is this functionality available in Excel 2010 ?
My second issue is a formula.
The last name is in a list of items and users have to select Yes or No to theitems on the list. I am wanting to create another spreadsheet that automatically populates based on their responses.
In short, I want to be able to set up a rule or formula that states if the answer in column A is "y" then I need the information in column B to be displayed.
The ultimate aim is to get a automatic sub set, (in another tab), of the orginal information based on users responses.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.
If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.
Using excel 2010, how do you insert a drop down calendar? Will this be able to display the date and then can I add a nuber to it and the result be a date
I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.
I'm trying to add a sorting filter dropdown on some columns in a sheet. If I select the column, then use Data -> Filter, it adds the filter. on that column. But then, if I select another column, the "Filter" button in the tool menu is already selected, even though there is no filter for that column. If I click it, the filter on the other column disappears. Then, clicking it again adds a filter for every single column. It seems I'm not able to select f.ex. 3 different columns and add a filter to only those. It's either all or none. Is this how it's supposed to work?
I have seen in a number of spreadsheets that you can create a drop down list using data validation that hides certain columns depending on what you select. this leads me on to my question...
I have created a Gantt chart for an entire year and what to put a drop down box in C1 that contains four selections (Q1, Q2, Q3, Q4) to represent the four quarters of the year. I want to work it so that when the user selects Q1 it hides the columns where the other Quarters of the year are kept so you can only see that quarter.
If that is possible I would then like a second drop down box that allows the user to select a specific week.
I need a formula (but most likely a VBA macro) that will search through a folder for a file than get data from that file. The files are named in systematic way, but I need the entire formula to work from inputting a mold number in one cell. E.g. I input 6291 in cell A2 the vba macro searches for file “6291 mold.xlsx” and returns a range of numbers as well as pictures in specified cells. Is this possible? If so how?
The closest thing I have found is VBA macro that retrieves a list of media files in a folder, I listed the code below.
I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:
[URL]
As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:
Code: 'Import from www.basketball-reference.com With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.basketball-reference.com/friv/dailyleaders.cgi?month=02&day=19&year=2014" _ , Destination:=Range("$A$1")) .Name = "dailyleaders.cgi?month=02&day=19&year=2014"
I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.
I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.
What I am trying to implement. I would have a drop down box in cell AD-4 with the current list
Select Week 1 Week 2 Week 3 Week 4
What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6
also
If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5
so basically hide the weeks that are after the current selected week.
In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).
(the first row is the current Row labels, I have a lot of current hidden columns).
A B J K O
Supervisor Name Agent Name Needs Complete Completed w/o Turn in Needs Turn In
