I am working on a formula by it is repeatedly failing.
Based on input 1 which is the month name, I am looking for a formula to identify the position across columns as the starting cell and then the value in input 2 needs to be repeated 12 times from there.
Example: Input 1 is Apr-14 Input 2 is 51.8
I have columns with month names starting Apr-13 to Mar-15
Since the input 1 is Apr-14, the formula should identify the appropriate column named as Apr-14 and from there it should copy the value (input 2 ) 51.8 for the next 12 cells which is until Mar-15. I have attaches the examples for your quick reference.
I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
I have never built or used a macro before and need very clear step by step instructions. I am just assuming that a macro is the answer to what I am looking for but if there is a simplier solution that would be fantastic.
I need to be able to repeat a row of data a specified amount of times.
Example:
3 Yellow James 2 Green Mark 5 Purple Sue 1 Orange David
Needs to look like:
3 Yellow James 3 Yellow James 3 Yellow James 2 Green Mark 2 Green Mark 5 Purple Sue 5 Purple Sue 5 Purple Sue 5 Purple Sue 5 Purple Sue 1 Orange David
I have few macros that I use to reconfigure a particular workbook into a sort of "developer mode". I keep them in a text file that I can paste them from when I'm working on it and then delete them before I send it out to other people.
I'd like to be able to script them so they only run if the workbook is open on a specific machine (namely mine). Is there any way to identify the specific computer a spreadsheet is being run on?
I am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:
Sheets("mySheet").Range("myRange").Name.Copy
but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?
I have a spreadsheet that contains over 3000 customers. On this there is much data...but what I am interested in is purchase date. The problem I am having is that customers have purchased multiple times and have multiple purchase dates. I was wondering if there was a way to get rid of all but the most recent purchase dates for each customer...thus having only one line per customer as opposed to 5 lines per customer?
In other words: Jon purchased on 1/2/14; 2/4/14 & 5/8/14 Bill purchased on 2/17/14; 3/19/14 & 4/21/14 Tom purchased on 4/2/14; 5/1/14 & 6/5/14
I need to get rid of the oldest dates and keep one line item by most recent purchase date for each customer...so instead of having 300o line spreadsheet I will only have 1000 lines.
I have a spreadsheet with columns and columns of data. What I'd like to do is identify which rows have blank/no data in all specified columns (H, K, O, S, U, W, and Y).
To clarify, I need to identify each row where all of these columns are blank (as opposed to each row where at least 1 column is blank).
I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:
[Code] .........
For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc): - In condition (row) 1: "x" is a specific value only found in Group 1 - In condition 1: "y" is a specific value only found in Group 2 - In condition 2: "x" is a value found in majority in Group 1 - In condition 3: no specific value can be associated to Group 1 or 2.
What I would like to get is a measure of whether: (1) there are values over-represented in one of the groups, or 100% specific to one group. (2) what are these values (3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)
I have a basic knowledge of spreadsheets, involving typical business needs (Average, Sum, And, Or, Logical Operators) but after roughly 35 minutes searching for a simple solution I'm stumped.
If I was to calculate the most popular (lets say fruit), I know it would be the MAX function, but what would the formula to show the Name of the most popular fruit and not just the amount of sales?
Column (B) has names of flowers Column (C) has price
I want to at the top of each hour have those two columns be moved to Col(D) and Col(E) . However if D and E are full I want them in F and G and so on. Essentially i want them to go to the closest empty column to the right.
How to do this automatically in a macro ?
This would allow me to keep track of my employees.
Also is there a way that I could automatically generate a graph of price vs. name for each hour when they are moved ?
How to identify the criteria (break or lunch) that he took first based on the time stamps. Here's the sample scenario: I would like to know which one is the 1st break or 2nd break they took based on the Date & Time criteria. See attached file for the sample data.
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code: A B C D E F
Month Category Occurrence Hours Minutes Seconds
2013/01 Transportation 48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.
However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB: ="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
I would like to create an average function that will take an average of the Column labeled "Gap Time (Hours/Min/Sec)". I only want it to take the average for this for each new start date. These values will change daily so I was hoping that someone may help me write a function that will work when data changes instead of manually taking the average every time data is entered.
to open a workbook at a specific time. I want to beable to flip my computer on at 8:50am, go grab some coffee and then at 9:00:00am have my workbook open and performing tasks. Besides turning on my computer, I dont want to have to open or activate anything prior to having my workbook open. However if there is code to turn on my computer from a sedative state that would be great too.
I have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.
The three tests comprise of the department.
Average of test 1 = 40.8 Average of test 2 = 39.9 Average of test 3 = 94.8
Average of dept using the averageifs is 41.3 Average of dept by taking the average of the values above is 58.8
Need to have a particular workbook open at a specific time...
I wrote this and placed it in a Personal.xls module, did not run automatically. I then placed the 2nd SUB() in the "This Workbook" and nothing. Where or how is the proper way to run this...
---------------------------- Sub Open_IndexAnalysis()
Workbooks.Open Filename:="e:Index Analysis.xls"
End Sub ------------------------------ Sub Run_OpenIndexAnalysis()
A vehicle has been assigned to collection, time of entry appears in Load cell coresponding with vehicle eg.e vehicle 1 'A1' = load 1 'B1'. I can get the exact time to appear in the cell currently but when I try to assign a second truck, the load 2 'B2' cell AND the load 1 'B1'. Im sure that there is an easy way to do this but i just cant figure it out. Can someone please help me out. Can i disable the cell that is updating?
I have a module which runs behind a command button which creates a table and outputs it to an excel workbook. Is it possible to cause this module to execute at a specified time, say 9.00 pm or when the database is closed. It is not necessarily the case that the database is closed every day.
I can open a file at specific time using TaskSchedule. How do I close it at specific time?
I am trying to write a simple one-line code for that but do not know how to combine two statements:
Application .OnTime TimeValue("17:01:00")
and
ActiveWorkbook.Close
Also, this workbook is updated every 1 minute using the same OnTime method and everything works perfectly. However, when I try to close file there is a message saying that "OnTime method object application failed" and I have to click "Debug" or "End".
I'm trying to extract some data from an online page, but I require a specific cell at an exact time, each day.
For example, I would like to acquire the main data from the following page: [URL]
into excel, but additionally, I require the ESU14 (Sep '14) Open cell at exactly 0700 BST. I am currently using the 'Data' - 'From Web' feature to scrape the necessary fields into Excel however I haven't found a way to acquire one of those cells, at exactly a certain time, every day, even when Excel et al aren't open.
