I need to group some invoicing values by date, but can't even sort the first column by date, so am going nowhere fast. I have the following type of data -
01/07/2013 25
02/07/2013 50
09/07/2013 62
08/09/2013 95
All dates are within 2013, and I need to be able to give weekly totals, on 18000 lines of data similar to above, but have no idea where to start - and can't even sort by date as it stands!
I am trying to sort information on my worksheets by date, oldest to newest however this does seem to be working on the workbook i have attached.
View 3 Replies View RelatedWe have a software program that we use to takeoff HVAC equipment on drawings. It will export all of the items to an Excel worksheet. Once it is into Excel I need to sort the data by two or more different columns, then sum three to four columns for each unique item for transfer to the equipment sheet for pricing. I've searched this site and others, but have not found a way to do this with VBA in Excel.
Floor
Rank
Name
Description
Qty
Cost
Labor
Seismic
[Code]....
The sheet is a simplistic view of what I'm trying to explain, obviously in high rise there will be many floors and hundreds of items. The actual spreadsheet has approx 12 columns, but only 3 or 4 need to be summed per similar item. Most times I sort by Range & Name. On occassionwe need to sort by Floor, Range & Name. Used to do this with a Database & RR Report Writer, but the new software does it onscreen with digitized drawings.
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 Replies View RelatedI am wondering if Excel can consolidate data from 6 worksheets found in 6 different workbooks and consolidate them into a "master" workbook. Here is a sample of 2 worksheets, but again, there will be 6 in total:
Workbook Name = CC 0038
Worksheet Name = Monthly Results
Column A = Acct #
Column B = Acct Description
Column C = Actual
Column D = Budget
Acct # Acct Description Actual Budget
589800Salary-Related Benefits4,808 5,498 589803HO Employee Benefits2,055 2,055 589632Recognition Award0 83 561605Furn/Equip/Alterations0 308 568010Telecom (Local Access)0 250 568041Telecom (Telephone & Fax)0 42 568042Telecom (Wireless)546 473 583402Printing/Stationery IDB0 250 584200Office Stationery/Supplies28 129 560000Travel Costs0 440 ..................
I have I1 as 1, J1 as 2014, i need the formula to give E1 = Jan14
If I1 is 2, j1 is 2014, then E1 = Feb14
I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.
Here is what I have:
Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.
I have 25 users with 25 seperate workbooks each with individual information on each workbook.
I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.
The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.
I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
I've created a workbook that I use daily. The workbook has two sheets. One for entering information. The other for storing information.
On the first sheet, I enter health information about myself. I also enter the date.
On the second sheet, I have rows dated from January 1, 2009 to December 31, 2009. If the date entered on the first sheet matches January 1, 2009, then the information is copied to that respective row. If the date entered on the first sheet matches January 2, 2009, then the information is copied to the corresponding January 2, 2009 row, etc, etc.
So far so good. I enter the information, the date, and instantly the information is copied over. I save the workbook and go to sleep happy................
I am setting up a VLookup system in which hundreds of excel worksheets are refering back to one master price list. How can I keep everything up to date if the master list is changed? My ideas: Is there a way to sync multiple worksheets (at the end of the workday, maybe)? Is there a way to keep the master sheet open in the backround so that each new worksheet I open gets the updated information? Or perhaps there is an easier way?
I don't know code or anything fancy like that so a simple solution (even if it's not the best method) will do for now. *Another note: these worksheets will be saved on a network available to up to 3 computers at a time.
I've got this spread sheet with an overview of my companies leased assets and i would like a formula to break the information into monthly periods so we know what we require to return to the leasing company before getting charged excess rent.
The overview sheet is from A - J, and would like for when i make changes to the overview sheet it also affects the sheet for the month, similar to a vlookup, but unsure how to do the that depended on the date (date starts from 01/09/2013).
I have dates formatted as dd/mm/yyyy and I need just the year, but with either text or standard format. (so no easy yyyy solution which would work)
I just seem to be too stupid, can't copy and paste as value, as that doesn't change a bit. If I format it as text it turns up sth like 34875 ofc.
I need to do an if then statement that takes the date (which is in the form of "3/31/2009" and only use the "3/31" info. Only its slightly more complicated than that, its a formula that links to another program. Basically, the current quarter end date is "curdate()" so if i want a cell to show that, i enter "=curdate()" and i would like to build an if then statement using that date but only using the month/date combo (only possibly dates are 3/31, 6/30, 9/30, 12/31) so I guess I could even use just the month, the problem would be to extract that information in a formula and build the if then statement around it.
So:
if its 3/31, show 1
if its 6/30, show 2
if its 9/30, show 3
if its 12/31, show 4
I have a Sheet1 that contains 900 rows of information 14 columns wide (C7:P7 are headers). Column M has an important date. The information is constantly changing.
On Sheet2, I want to display the upcoming dates, in numerical order for each row that's within 5 days of today's date. After the date has passed, it no longer needs to display on Sheet2.
I have a worksheet, with a "cost" column, and a date column. I have another sheet with dates pre-written, and a cost column as well. I'd like to know how to make the cost from worksheet 1 go to worksheet 2 under cost, but also add up any costs that are on the same date.
View 9 Replies View RelatedI have the following formula:
=CONCATENATE("EXP: ",TEXT(AK2," MM/DD/YY")," ","VP DATE ",TEXT(AL2," MM/DD/YY"))
But if both cells are empty I get : EXP: VP DATE
If only one is empty, i get EXP: 08/26/19 VP DATE or EXP: VP DATE 06/27/14
But I do not want EXP: or VP Date text to show if the content of the AK# or AL# is empty. I figured I will place something like If(ak#="","",
But it give me a "Too many arguments" error.
I am currently using:
=TODAY() and
=NOW()
to enter date/time on a sheet that is used to create a printed form. I want to ensure that the date/time are current when the form is printed, but they will actually only reflect when the workbook was opened. So, if the employee opens the workbook, then takes a break or answers the phone, when he prints the sheet the date/time may be wrong.
I need to have the worksheet either:
1. actively update the date/time information constantly
2. update it before printing,
3. or possibly have the workbook "time out" and close if there is no activity for 60 seconds or so.
I already have code entered to prevent a "do you want to save changes" promt when closing. And I am using a command button for print which closes the workbook automatically once the employee prints the form (to ensure that all other data is entered fresh everytime the workbook is used).
I have a table which I am trying to sort by date, however at the end of each of the dates there are 2 spaces represented (I haven't a clue why or how) which means the data doesn't sort correctly. Other than going cell by cell and deleting the 2 spaces can anybody offer any recommendations?
View 2 Replies View RelatedWhat I have is an output of about 30 worksheets of individual stock data in a workbook, all with the same column headings, laid out like the following (so that the Column Headings are stock data as recorded on the Date in Column A):
<Stock Name>
<Date> <Price> <Price to Book> <etc.>
with the stock titles also as the names of the worksheet.
What I want to do is create a macro that outputs to a single worksheet where I can input the stock name and a certain date for the stock, and then have it insert the appropriate row from the stock's worksheet underneath.
So, if I say I want the data for Stock A on March 24, 2007, I can input those two and have it output the appropriate rows from the worksheet.
Or, more generally, I can create a list of stock names and dates, and have the macro output that data underneath.
I want cell C2 data in Number format or (some options) not in date (Format cell) so that I can sorting perfectly bcoz I am having some problem in sorting. If I want data age less than 25 that is not sorting.
View 2 Replies View RelatedHere is what I want to accomplish:
I have a spreadsheet with data in cells A36 to G52. In The D column (D36 to D52) I have a date in there. I would like to have this column sort in ascending order automatically, but also have it sort the other data within the same row with it.
I have tried a couple of VBScripts that I have found in other posts that are similar, but I just get errors when trying it so I assume that I am not putting something in correctly.
Can anyone help me write this functionality? I have other data in a similar setup on the page that I also want to sort automatically but the script for this first part should be just a copy and paste, and change a couple of values....at least I am assuming so.
I am looking for a way to auto sort the info in column A based on the numerical account number. The auto sort also should move the rest of the info in the row along with column a. I want this auto sort to happen once a new entry has been entered with all the information in columns A -L.
Basically the goal is someone with very little knowledge about excel or computers in general can enter information in all 12 columns and then the table will auto sort itself based on the account number.
I have worksheet of data that I need to sort by year then month. Currently when I try to sort the data by data excel sorts by the month then year. how to reverse this so the dates are sorted by year then month?
View 1 Replies View RelatedColumn A = Product code
Column E = Expiry Date
How to make macro code automatic sort by expiry date (oldest to newest) then product code (A to Z)
After i put new item in product code & the expiry date...
I like to auto sort every time i put new item in next row.. and the result will be ber25 1/20/2015 will be on top of ber25 1/21/2015
Code
Description
Beginning
U/M
Expiry Date
Act10
Activo 10K
1
Bag
1/20/2015
[Code] ..........
I have a very simple excel with three columns Name (column A), Date (column B), Hours (column C). I want the excel to autosort upon opening so the data is sorted by date. I right-clicked on Sheet 1 and put this in (I found it online):
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Range("B1").CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
End Sub
I have a column which has dates in format: dd/mm/yyyy - there are no headers in this worksheet.
I need to sort the worksheet according to the row date field ... but it's causing issues.
It is assuming the dd is mm ... and when i reverse the format to yyyy/mm/dd before sorting - then it assumes mm is mm BUT it keep sorting with dd, regardless of the month. with both ways, the assumptions are interchangeable ...
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
how to soft vaue of date in excel 2007. only arrange by date (not including month).
for examples:
it like that:
13/07/2012
15/01/2012
18/04/2012
How do we sort an alpha numeric date from recent to oldest.
from this:-
HGH010112
HGH231211
HGH020212
to this:-
HGH231211
HGH010112
HGH020212
Starting at E8 and going down the E column there is a list of non sequential dates that could potentially be very small or extremely large. These dates must be sorted in ascending order together with all the data in their respective rows so for example
John Ball 20/2/07
Mark Dunn 19/2/07
Tim Jones 18/2/07
should be sorted as
Tim Jones 18/2/07
Mark Dunn 19/2/07
John Ball 20/2/07
If anyone can write such a piece of code it'd be very time saving.