I have an excel report that is generated in another program every week. The report is not sorted by date and it may have 100 rows of data one week and 200 rows of data another week. My excel file has formulas that refer to this original report. I need those formulas to update first, then I need the information to automatically sort by date (column D in my excel file). I have tried to setup a script to run, but it's not working. I will attach the file and the script.
I need to find a way to autosort a series of numbers, inputted manually for example, in cells A1 through A15. The autosort function needs to put the numbers in descending or ascending (either way, doesn't matter) in cells B1 through B15.
In effect, if the numbers inputted into the first cells change daily, then the correct order of these be automatically sorted so that they are in a specific order.
The closest I can come WORKS, but I'm using about 300 filter formulas to do so.
I have a front page with buttons linking to other spreadsheets, e.g
{ button } Stock Sheet .xls (???)
{ button } Sales Sheet .xls (???)
In the brackets I wanted to display the last updated date of the said spreadsheets, so that a user can see if it has already been updated. Is there a way of retrieving the last updated date for a spreadsheet, and dispaying it (i.e is it held against a variable or named item)
Previous to a PC meltdown a few months ago, when I opened my monthly customer list file the account date would automatically update to the current date. After the PC repair, reinstall of MS Office 2002 (same version as before the PC death) and copying of documents back to their old locations, I now have to update this manually for all accounts each time I open it. I have searched through the options and customize menus but there is no mention of this setting. Before, it just did it from day one (5 years ago) without any setup.
How do I get back the old auto-date update preference?
I have a spreadsheet which is linked to several password protected spreadsheets. I have a macro which updates the links but I would like the macro to include the date that the links were last updated. Is there a code I can insert into the macro which will display the date the links were last updated?
I am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?
I have created a spreadsheet and saved it as a template. when the staff open it to enter information they use save as and give it a name. They only need open and enter information once. I have put into this spreadsheet in cell A1 a formula to enter the days date. This all works fine. A few days later I need to access the spreadsheet to check the figures entered and obviously the date changes to the date when I open it up. Is there any way of stopping this happening without using macros. i.e. keep the date as the day they entered the information.
I am trying to add some functionality to a spreadsheet that I manage. Basically i have a small team, and each week I ask them to update a spreadsheet to show what they have been working on in that week. In order to monitor who is going in and updating their entry I would like to add a column that shows me when the last update was for each team member. I have seen this example but it only returns a date based on one cell as opposed to the whole row (or range of cells) Auto populate the date and time a cell was updated. I have attached an example.
I have two tabs in a workbook named "Dashboard and Medicine details"
I need my dashboard to show what are the medicines will expiry in next month.
My Medicine details tab contails medicine details along with its expiry date. If the expiry date of a medicine falls in next month, then it should be displayed in my dashboard.
See the sample attached will give you a clear objective.
I want to autosort by date (column I - dd mmmm yyyy hh:mm AM/PM format) in a worksheet which contains data in columns A:I of 15000 rows.
I want to know how to do it.
When i update/enters a data in a new row or present row, It has to be go according to date automatically(ascending)
I had already googled it and tried all avialable codes with all modifications. If someone help me would be greatful to me. I was new to EXCEL with some basics.
I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.
Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.
A sheet containing all the daily tasks, and according to the tasks that I need my employees to work on a particular day I write YES in the column. The cells that have YES marked next to them are then reflected in a new sheet with the following formula:
=IF('PLANNING CELLS (P)'!C12="Yes",'JOBS LIST CELLS'!C12,"")
Obviously the formula is different for every cell.
The issue with this is that if i have a list of 50 items and i select only 10 (Marked yes) I have a lot of blank spaces in the new list. I need to remove these blank spaces from all columns automatically whenever updating. Is it possible?
I have a column of data that constantly changes in length and want this column to autosort the data alphabetically when the data changes. the list contains the names of worksheets
I then want to display in the next couple of columns data from the worksheets listed in the column. i know i can do this manualy by saying that that the cell equals sheet? cell etc but was wondering instead on putting in a formula of ='A sheet'!E20 i can put something where A Sheet is that uses the name in the column of data ?
I've created a formula that concatenates 3 parts in VBA. column A, B, C.
- colunm C is a general info column and can be general text OR a date value. - if its a date value it ends up being concatenated as the sytems value i.e. col A1, col B1 and col C1(16/01/2007) becomes A1+B1+C1(39098).
I need to enter a range of dates and have any date that falls within that period (regardless of year) highlight. I currently have it referncing two different years, but I need more range than that and I can't get it to highlight dates in July if they were dates that happened 1, 2, 3, etc. years ago.
I have two columns... 1 with a list of dates (ex. 22-Apr-07) and the other which states either a pass or fail. Ignoring the day and year, I'm trying to get the total # of references for each month, and only count those with a 'pass'ing grade.
I am having trouble entering a formula that measures how many times "Open" occurs in one column IF another column's entry is between 2 dates or within one particular month time frame. I've tried a bunch of formulas but can't seem to get anything to work!
I am trying to create a schedule that includes weekdays only. Is there a formula or set-up step to do that?
Currently I have 3 columns: Start date Action date Finish date
The Action date column has the formula: that cell (say C4) minus number of days that action requires (say 10), but when 10 days fall on a weekend, I need to change to the next work day instead.
I have data showing the month by month close of the Dow Jones Stock Index in column A and the date of the close in cell B1 ( Sep-06) etc, cell B2 (Aug-06). The data goes back 20 years.
I would like to use this month end data to construct a table that shows the close at the end of each completed year,so 2005 would be the last completed trading year and 2005 would appear in cell D1 with 2004 in cell a D2 etc.
Obviously the close for each year will be the closing price for the month of December in each relevant year.
How would I construct a formula to show the closing price for the end of each year in column E that gets the information from the raw data in columns A and B.
I have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this
this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way.
Sub FinishOU() Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range With Application . ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False Set wb1 = ActiveWorkbook With wb1 .Save For Each sh In Worksheets Set TestRange = sh.Range("A1:S4") For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
I have a macro that searches a column for a date and then selects the appropriate date. The next step is to then copy the row and insert a new one below the copied one and paste only the formulas and none of the data. I have tried several iterations but all of the seem to paste the data too.
I have a spreadsheet with two columns - column A contains dates and column B has the corresponding data (always numbers). The data is not uniform (i.e there may be lots of entries in any given day and none over the next few). The data is always sorted in chronological order.
I want to set up a summary table to show the min, mean, median, max for each week. I have set up an array to calculate the mean, but I am having trouble working out a good way of gettting the others short of having a set of columns that manually define each cell...for instance =min(b3:b27) or =min(b28:b30).
I am creating a time sheet for a supplier & when the supplier enters a date into column A I would like a new line to be inserted below & include the formatting, formulas & data validation from the row above.
I have just added two date and time picker controls to my spread sheet to allow the user to select the date range.
They are linked to cells C9 & C10.
The problem is that the formula I using to check whether the first selection pre-dates the second has stopped calculating correctly when using the date and time pricker controls.
The formula is:
=IF(C9>C10,"Error","OK")
I have checked it on the same sheet by just typing the dates in and it works fine...I have tried formatting cells C9 & C10 to short date, general, text, number but it still does not calculate correctly.
I have 2 dictionaries; both have a "location" and the "frequency of occurence". The 2nd dictionary locations are related to the first, but are named differently, so I have a range of cells I'm using to define the equivalent locations, ie:
Dict. 2 Location----Dict. 1 Location Wrapper------------Packaging Robot----------------Robot Arm Robot----------------Robot Base etc..
(It's not a direct 1 for 1)
The program is meant to take both dictionaries, convert the 2nd dictionary keys to the equivalent dictionary 1 keys (based on the cell range data provided) and then combine the values associated with that location and store them in D_Master. D_Master is a copy of my dictionary 1, in which I am also trying to add the values from dictionary 2.
I started by writing the location definitions within the program, i.e.:
[Code]....
which works, but there are several hundred definitions and it becomes less robust, whereas a user could type in a definition within the range of cells and the code below could take care of the rest.
I have several sheets named by months like 1213, 1113, 1013, 913, 813, 713, etc (month-year) and I'm doing one sheet as an index that needs to find a value across all the sheets.
All the months sheets have a list of names in column A and status in column B. When the business with the name is done, it doesn't appear in the next sheet.
For example "Client A" could appear in 713, 813, 913 (business done) and won't appear in 1013, 1113 and 1213, In the index sheet I want to lookup what is the status for a name from the latest sheet (last updated status).
So I have all the sheets names in a range called months and I want to make a small formula if possible.
I want to start looking in the latest month (sheet) and if that doesn't have any value it goes to the lower one and so on. If I use =VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0) it shows N/A since the name only appeared until 913 and its looking on 1213.
Right now this is working, but it's too big to understand.
I am wondering how to print only the rows that have information in them. I have a report that is super long. We only want the information that is updated to print. This information will change weekly.
Is selecting the area I want to print the only way to do this? Or is there a way I can tell excel to print only the rows that have been updated?
This report is one that we use every week, for ordering, there is a lot of information on the report that we will not need every week, so we are hoping to print only the rows that are updated for that week.
