I have a column with dates in each cell. I need the user to the able to pick a date from a list in the adjacent cell but it must be a date on the same weekday.eg if cell a1 is 23/10/2013 then cell b1 should display a list of Fridays in the future for the user to select from and then it should put the selected value in the cell (eg 30/10/2013).
View 1 RepliesI have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx‎
is it possible to have an dynamic list, so that when I add in a new entry in to the input column, the output column will automatically change to the same thing. Also then my dropdown will realise a new entry has been inputted (uses the output list) and the list will update to show this.
View 2 Replies View Relatedsorting list with date ranges with formula.
I have a list of dates and I want sort them by growth, also only to show the values after specific dates. I know how to do it with Macro, I just need list changing dynamically.
I need to create a 2-stage dependent drop down list.
Stage 1:
In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .
Stage 2:
In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.
Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)
Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column
D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.
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.
My objective is to automate the creation of a dynamic top-5 list by summing a number related to multiple entries of an item (text string). Data are added monthly and while the textual strings (imported) are constant (text1 always reads as text1) the text strings themselves may vary from "text1" to "text2", etc. What I'm trying to achieve is finding the top-5 five items and copy/paste them to a range on the same worksheet. The range into which they are pasted are source cells for DSUM criteria. The frequency with which a text string appears is not critical, but the associated count is.
View 2 Replies View RelatedI am trying to create a list which will summarise information from a dataset depending on two input cells in which dates are inputted by the user. I would really appreciate it if you could have a look at the file I have attached and give me some idea as to what formulae I should be using!
It is important that the position of the output list remains where it is (as ideally I want to draw graphs using the information summarised in the output list.
If your confused now when you look at the file it should make a bit more sense.
I have been asked to create a calendar which will display, on the applicable date of expiry, a contract name. Basically so someone can go and see what contracts are due to expire. And then if a new contract comes up it will automatically be added.
I have a list of Contract names in one column followed by the expiry date.
I have looked around and there are some things which could help but they are seriously complicated and I can't work out how to apply it to my situation.
Macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.
For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro do this for every row even if the word and/or the number changes.
I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.
View 7 Replies View RelatedExcel 2007 I have a spreadsheet of file boxes the keeps a log of boxes, contents, locations and shred dates. Currently figuring out when and which boxes can be shredded is a manual hunt, find, and then deal with it. I can use CF and Sorting but I have been looking at some search type functions to return a list meeting the requirements and trying to step my way towards that.
1st attempt. Was find out how many boxes meet the shred (before today) that have not already been detroyed. Column K is the Shred Date, D1 is Todays date, Column G is Status. Got this working good.
[QUOTE]]=SUMPRODUCT(--(Log!K2:K2136(lessthan)STATS!D1)*SIGN(LEN(Log!K2:K2136)))-(COUNTIF(Log!G2:G2136,"Destroyed"))[/QUOTE
My failed attempt came at trying to find the earliest shred date excluding those that have been destroyed. This is not really neeed but I was hoping it would get me closer to creating the list of boxes that meet shred that have not already been destroyed.
Note: Column A is the Box No. and the spreadsheet contains blanks as we continue to add file boxes to storage.
Below array formula find the highest box number used by each department.
Code:
=A4&"-"&TEXT(MAX(IF(Log!$A$2:$A$2136"",(LEFT(Log!$A$2:$A$2136,LEN(A4))=A4)*RIGHT(Log!$A$2:$A$2136,3))),"000")
I would like to create a list of boxes including Column A "Box no.", Column H "Location", Column K "Shred Date" for any box whose shred date is before today that has not aleady been labeled "Destroyed" in Column G.
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies View RelatedThe easiest way for me to explain it is to give an example: if the list contains Red,Blue,Green,Yellow. i need a formula for the adjacent cell that inputs £1 if red is chosen, £2 if blue is chosen, £3 if green is chosen, £4 if yellow is chosen
View 2 Replies View RelatedIf I have a list in sheet1, of say 300 names, I need some code, that for a table on sheet2, from A1 to F500 will delete the contents of cells in columns B C and D, IF the value for column A in that row is from the list in sheet1
List of names: sheet1, A1 to A300
Table that needs changing: sheet2, A1:F500
cells to be deleted in columns B C D IF value in A corresponds to the list in Sheet1
I have a phone list and I am trying to add a search box to it that when you type in the Employee name, it will return the value of the cell adjacent to the search result so the extension can show up.
I am a VBA Novice, but I have found plenty of code for the searching aspect of it. I am having trouble figuring out how to have the search occur and then return a different value then what was originally searched for.
Ideally I would like the msg box to show both the employee's full name and extension.
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
unique list.xlsx
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
I have a column with random values that are generated my my machinery.I should not sort them, because they provide me with the performance graph. Lets say i have column "A"
0
2
9
9.5
30
90
22
15
9
0
As the max value of this is 90, and its 10% of MAX is 9, which occurs at 3rd and 9 th positions.I need to find the average of its adjacent range, with zeros not considered.It has to calculate the max value in the column, find 10% of max and do average of adjacent column from the sl. no of first occurrence of 10% value in first column to the sl no of last occurrence of 10% value in first column.
the exact 10% value may not always exist. 10% of 90 is 9, but if the max value is 94, the 10% would be 9.4 which doesn't exist. then it has to take the nearest available predecessor in that case.i get values in decimals mostly and only some of them are perfect integers, thus this consideration goes with it,and also, my range actually is B14:B10013
i would most probably have the 10% value within the first 50 "non zero" entries and reoccurring somewhere probably within the last 50 "non zero" entries.
Today() provides a dynamic date which always equals the current date - not very useful when dating accounting documents. Is there a function that will allow the use of Today(), at the time of the document creation, but will then freeze the date to the date of creation?
View 5 Replies View RelatedI have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
way to create a group of folders in a specific location using a list of cells from an XLS file as the folder names.
For example, it would take whatever text is in Cell A1 and create a new folder in a specified location. It would take the text in Cell A2 and create a new folder in the same specified location. Etc.
I have a workbook with a main worksheet "template", from which other sheets are created. Each worksheet has a specific value found in cell D8.
I want to collect the values found on each worksheet (in cell D8) and list them on the first sheet.
I'm trying to filter a list between two dates that are selected using drop down lists in Cells CZ1 and CZ2.
The first part of the code works ok ie it filters the list by column 28 as "Shipped" only.
The second part is causing me trouble.
The third part sorts the list by date order based on column 48 and this also works ok.
Code:
Sub FilterTransactionDates()
With Sheet1
.AutoFilterMode = False
.Range("A1:CU1").AutoFilter
.Range("A1:CU1").AutoFilter Field:=28, Criteria1:="Shipped"
.Range("A1:CU1").AutoFilter Field:=48, Criteria1:>=Range("CZ1").Value, Operator:= _
xlAnd, Criteria2:
I have a yearly running log (attached). At the bottom in cell [B88] I would like to develop a formula that gives me the number of times I ran in that specific month. Dates are in Column A and running distances are in Column B. If a distance is zero, I don't want to count it. I have attempted to solve this using the =COUNTIFS formula, but I am not able to structure it properly. Maybe =COUNTIFS is not what I should be using.
View 3 Replies View RelatedI have built a macro that tests to see if a date in a range is before the Period Start Date, and if it is, then the labor rate in that same row (3 Columns Over) should be escalated by the appropriate AWI. My code below works, but I am wondering if there is a better way than using offset(0,3) to prevent a code breakdown if columns are inserted between the Date and Rate columns. The date range is names "DATES" and the rate column in names "RATES"
Sub CountTheCells()
Dim cell As Excel.Range
Dim i As Integer
For Each cell In Sheet2.Range("DATES")
If cell.Value < Sheet2.Range("POPS") Then
i = i + 1
End If
Next
MsgBox ("You have " & i & " Rates that will be escalated b4 the POP Begins")
yesno = MsgBox("Would you like to Escalate the Dates and Rates?", vbYesNo)
If yesno = vbNo Then
MsgBox ("Not Done")
Else..........................
I have a master list of names on sheet 1 column A. I want to create a list of names on sheet 2 column A of the people from Sheet 1 column A that have a specific value (Y) in column z sheet 1. I might also want to different numeric values for the Z column
View 7 Replies View RelatedI have ranges that are named Math, Reading, Science. I would like to create a drop down list in validation that chooses the range based on a value in a particular cell.
View 4 Replies View Related