I'm currently developing a calendar that has a list in it with lets say 4 options. What I want the calendar to do is calculate at a specific 'cell' the number of entries that are selected during the month.
The idea is to have a drop down on each 'day' and a counter that calculates the number of times one specific options has been selected. Once the option has been selected the 'day' will change to the corresponding color.
I 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 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
'
'
I have a sheet that has drop-downs with totals affiliated with each option. This is a time recording sheet and I would like for each drop-down type to be totaled accordingly. Any help with an equation would be greatly appreciated.
If you have any suggestions, I can send you the document for review.
Is it possible for data like "ABC /sdfsd/sdfs/sdf/sdf/sdf/" to be viewable in a drop down but when selected to appear only as "ABC" in the spreadsheet? I need to be able to see the full line of data when selecting my choice from the drop-down but for readability-sake in a printable spreadsheet only want "ABC" to be displayed.
View 9 Replies View Relatedcan you have col A contain a drop down and then column B include an if then statement that would result in another drop down? IE. Column A has drop down with options of "A", "B", and "C", if the user selects "A", then a drop down would appear in column B containing options "1", "2", and "3". But if the user selects "B" in column A, then a different drop down appears in Column B with options "Yes", "No", and "Maybe"...etc....
View 2 Replies View RelatedFirst post so please excuse me if this has been covered before but I am trying to get the results of a dropdown menu in one worksheet to become the criteria for a filter in another worksheet (both in teh same workbook).
I thought I could just add a reference to the dropdown cell into the autofilter (in VBA editor) but it doe't seem to be that simple.
Any ideas?
I'm working with 2 drop down lists created through Validation.
I'm trying to make this scenario work.
If A is entered in B1, then the drop down list in C1 would appear in D1.
If B is entered in B1, then the drop down list in C2 would appear in D1.
List C1 has 12 choices.
List C2 has 8 choices.
I'm making a data entry tab so for the sake of simplicity I only want one list to show up at a time depending on the variable A or B
trying to shred the last bits of fat for the summer and thought I'd try a Keto diet, this depends heavily on not eating very many carbs, as well as keeping your total Kcal intake below maintenance.
This makes for a fairly tricky diet, as most things you generally eat contains alot of carbs, so I thought I'd make planning my meals easy, by creating an excel sheet, with drop down menues for different foods/drinks as well as their nutritional content.
I''d like it to look something like this, with the dropdown list under "Food" ....
I have an analytics sheet where I have a client name chosen from a drop down via validation. The problem I am running into is that while I want information from a data table to populate a cell based on the selection from the validation, I also want the cell to populate based on a second drop down box (basically selecting a time frame). Take the example data table below:
Client Jan 2009 Jan 2009 Target etc etc
Client A 1111 1290
Client B 900 750
I would like the user to be able to select Client B and then, from a separate drop down, also choose, say, Jan 2009 Target and then another cell would populate with, in this case, 750.
I have a sheet in a work book which will act as the "Purchase Order" sheet. I would like to have the sheet include a drop down which would allow me to choose from a list of vendors which would print on the purchase order.
View 3 Replies View RelatedI'm having a difficult time figuring out out to coordinate two cells together.
If a "1" or "2" is entered into cell A1, I would like cell A2 to have a drop down menu show a "0". If a "3" is entered into A1, I would like B1 to have a drop down menu show "1" or "2". If a "4" is entered into A1, I would like B1 to have a drop down menu show "3" or "4". Lastly, if a "5" is entered into A1, I would like B1 to show "5" or "6".
I don't know if this is possible without using VBA.
I am building a template in Excel and I want an error message (or any message) to appear to the user if they select from a static drop-down the same option in column I as in column J (i.e. they have to pick something different in each case).
Issue is I can't exclude any options from the second validation because, essentially, it's the same list. So if, on one row, a person picks "Bill" in column I, they can't pick "Bill" in column J. However, if they have picked "James" in column I, they can pick "Bill" in column J.
Don't know if this is well explained enough but would appreciate any tips on this - I've tried messing about with validation, conditional formatting, putting an IF statement in the next column and so forth but I haven't found a method that isn't convaluted and easy for the user of the template...
I've created a spreadsheet that has two drop down lists. Based on what the user selects from the first list, the second list is populated. It's working great except for the items in the first list that use special character, such as a -, or &. If necessary I can upload a small example of how I have my sheet set up, but I think I found it on this site!
View 4 Replies View RelatedI have two columns, Account Number and Account Type. I already use Data Validation on Account Number to make sure it is numeric and on Account Type to make sure the user picks from a drop-down list. I want it so that if a user inputs an account number, it forces the user to automatically pick from the drop-down menu in the account type column, otherwise post an error.
View 3 Replies View RelatedNow he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense.
Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips.
So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones.
Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.
to make the user experience better by inserting the " Stock Search" sheet to speed up the process. The spreadsheet attached is a simplified version of the current version, where it would normally have many many more sites, areas and models of cars. The existing process means the user has to trawl through the sheet where they are only interested in what sites in their area has stock. I can not change the format of the "Red" and "Yellow" sheets so hoping to insert another sheet to do the work.
I am hoping to have on the "Stock Search" sheet a few drop down boxes.
The user will select
1. Area
2. Type of Colour (the type of colour selected should link with the sheet name in effect, so only stock within the "Red" sheet will be shown if the "Red" colour is selected from the drop down box)
3. Model of Car
This will then give the resulting Store/Stores with stock (value 1 or above) with those conditions met which is showing in the relevant sheet
So as per the attached sheet, if a user selected Area "South West", "Red", "Renault Megane"
then SO10 Clifton 1 and SO15 Yeovil 3 would be shown in a list.
I have a fantasy baseball spreadsheet I've built. It includes player projections and people can input their league details. It then creates custom player cheatsheets for the user. There are a number of form options including buttons and drop-downs.
I've created this spreadsheet for a few years. For some unknown reason, since I finished version 2.0 of 2007 a few people have emailed me indicating they cannot see the form options. I still see them in my version of Excel. I definitely would have received more emails if this was impacting everyone, so it appears to only hit a few people. I asked about the version of Excel these people have, and that does not seem consistent. Plus, version 1.0 works for them. I only made some formula changes between 1.0 and 2.0.
Has anyone ever seen an issue like this before? Any idea why this might be happening?
The file download is available at the following address (download link on the right):
[url]
There are 2 spreadsheets in the download file. The Compiler is the one with the reported problem.
I am creating a pivot table from region, which some of the rows are blank.
Is there any possibility that the pivot table not show the blank in its dropdown combobox?
I'm trying to format the data area of a pivot table, but I keep getting an error with this formula I was trying to manipulate:
View 4 Replies View RelatedI am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER
Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))
How do I force a dependent validated cell list to go blank (erase previous entry) if the origin cell is changed.
A1 = Fruit B1 selected as APPLE from validated list
A1 changed to = Vegetable but B1 stays as APPLE unless changed as well.
Can I force a B1 blank when A1 is changed to ensure B1 is correct?
I'd like to create a data validation with 1 dropdown list in the second worksheet in B4, relating to 3 cell ranges on the first worksheet (COS, Expenses & Capital). If 3 can't work, I've created 1 called 'dropdown' which incorporates all 3.
which formula I need to write into the validation, or what else I need to do in order to find a solution to this.
I'm building a form comprising some text boxes and drop down lists. I'd like for data (once input into the form by the user) to input, upon click of a submit button, into an excel spreadsheet, row by row.
Here's where i'm struggling: I need the form to validate data before submitting. Namley, the form must not allow null values to be submitted and will show a message box telling the user what is needed.
Below is what i've got so far. I've tried playing around with this but am struggling to implement the above functionality:
[Code] ..........
Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
View 2 Replies View RelatedI am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
I have a three sheet workbook that I created
The first sheet is virtually blank it allows the user to cut and paste dispatch info, and customer info from Lotus Notes Dispatching which is then referenced to auto populate appropriate cells in Service Report(third sheet)
The second sheet uses significant conditional formatting to aid the user in entering data in a very easy to use straight down vertical manner.
There are 74 rows of data entered followed by several comment areas which all also auto populate to the third sheet(service report).
Issues I am having, I only want the third sheet (service report) to print, but I want all conditional formatting to be visible to the user.
At one point I put all three portions on a single sheet and set print range to exclude that which I don't want printed but then all formatting is masked and cells are just gray.
I would prefer to have the three sheets remain separate and to only print the third sheet, but need conditional formatting to remain functional and visible.
I am trying to use a drop-down list to alleviate errors in a worksheet but was wondering if it was possible to remove an item from the list once it was selected... Here is what I mean:
The drop down list shows:
Resume
Transcript
SAT
ACT
GPA
and there are five columns aside one another with these same options. What I want to know is if I choose resume for the first list, is there a way that I can make that option unavailable in the following four columns and show this:
Transcript
SAT
ACT
GPA
Or if I use any selection from the drop-down list, it would be removed from the other 4 columns.
I'm trying to create a drop down control for each cell that will present the user with values Red, Green, Yellow. Once selected the cell will turn that color.
View 9 Replies View RelatedI'm building a Excel 2003 workbook that needs more than 3 conditional formats. I've tried the code given in the FAQ: Get Around Excels 3 Criteria Limit in Conditional Formatting but this only works on cells being modified directly. I've also tried the code in the post: VBA Conditional Formatting - Refresh when data changes but neither does the job I need.
I'm using a Format Control to present the user with a list of choices. When the control changes the target cell I want to apply a conditional format to that cell. This image shows the form. When the Format Control selection (in B2) is changed I want to apply conditional formating to the target cell (A2).
There will be up to 10 choices and therefore conditions.