I have list of many addresses. Then I have different dates related to addresses witch I insert myself regarding colaboration with specific person on that address. Each week I have to make list of all addresses that have the same date. Since manualy this is quite hard to do, I was wondering if I could do it automaticly.
I am attaching sample of what I am talking about. exporting addresses template.xlsx
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
I am trying to create a calculator which will show start date and end date based on the specific date provided.
EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
I'm trying to match certain transactions between two bank accounts, where the only like values are going to be date and disbursement/deposit amount. Obviously vlookup will not work here because there are many duplicate values. In the attachment I put in a Vlookup in the Account 2 tab to try and demonstrate what I'm trying to do exactly, which is match deposits in Account 2 with Disbursements in Account 1.
After looking around I found that maybe a combination of INDEX/MATCH would work here but how to get it to work or even if it is appropriate in this situation.
I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:
=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.
So, keep cell A clear untill something is written in cell B. This works fine.
But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.
I have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?
View 9 Replies View RelatedI want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
I am trying to have cells display red for information that was typed after a certain date. I know that is easy except that I don't wan't the information that already exhists in the cells to be formatted differently, only changes after the date.
I used Excel to take inventory of the items in my store.
I have two columns that I would like to export into my point of sale program.
They are, "product code" and "inventory count". When I try to copy them into my pos system it won't work because I would have needed to hit the enter or tab key on the keyboard inbetween the two fields. So if I were going to enter it all by hand I would just type the product code, hit enter or tab and then type the quantity.
Is there anyway to make Excel export an Enter key press or tab key press inbetween the two columns? If I can't figure it out I will have to do it all by hand. (over 2000 items)
I was wondering if there is a way to export specific data from the workbook into a text document. I would want it to extract each worksheet name, and the contents of 2 specific cells.
So the text document would look something like this:
Sheet1name ContentsofCellM1 ContentsofCellN1
Sheet2name ContentsofCellM1 ContentsofCellN1
Sheet3name ContentsofCellM1 ContentsofCellN1
Sheet4name ContentsofCellM1 ContentsofCellN1
And so on for each worksheet.
Or if a text document can't be done, just create a new worksheet with the above extraction.
to write a macro to export 3 columns of information from excel into a table in access.
View 8 Replies View RelatedIs there a macro written or a way to write a macro that will take certain excel sheets and combine them to create one PDF? I have the excel add-in that allows a user to export either the entire workbook or a single sheet to a PDF file.
View 5 Replies View RelatedI have a spreadsheet containing, names, numbers, dates and scores of shooters. Is there a way of exporting the data held on the spreadsheet into a text file but in a specified format as follows:
The file is a fixed length text file as follows:-
>
>999042864NEWMNEWMAN W 060501060501 300 91 259
>
>999096292DORRDORRALL GM 060501060501 300 97 288
>
>First 3 bytes 999 - just historic
>
>Next 6 bytes - CPSA number leading zero filled
>
>Next 4 bytes - first 4 digits of surname - historic - used to double
>check CPSA No - Manual entries................
I have some data in Excel that I want to export to AutoCAD using VBA Code in Excel. But I don't want to have a table inserted in AutoCAD, but polylines and multitext.
Is it possible?
If someone could give me an example, how to draw simple polyline and insert mtext in AutoCAD (from Excel VBA Code) I should be able do do the rest.
I have multiple rows within a worksheet and need them to automatically save into a separate txt file. Example:
I have a workbook with rows b-z. Instead of taking each of the rows and copying and pasting it into another separate excel file, then saving in a txt format, is there a way to automate this? So that the following will occur:
Column B 950 line is one file
Column C 950 lines is another
column D lines in another txt file.....etc
I have some code listed below, however, it reads the first column cell by cell. It does not group column by column grouping all data within that column as on txt file
Sub notebook_save()
Set wkbk = Workbooks.Add
Sheets("Sheet1").Select
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To RowCount.......................
Is there a way to sum data up to a specific date?
I have a large data table, which includes various values throughout the year, if i want to look at the total value up until say 31/01/2014, but my data goes up to 28/02/2014, is there way to do this within the sumifs function?
I tried this (attached File) formula but can't make it work. Formula in F17
View 2 Replies View RelatedA1=10 July 2007
A2=12 (number of days)
A3=21 July 2007
I need a formula in A3 that will basically add the number of days in A2 to the given date in A1.
Note: Don't know how would this work with 30 and/or 31 days months??
I have run into another block wall.
Here's my problem:
I need to know if there is a formula that will allow me to not only calculate a date from two different cells -- but also to round the answer to THE NEXT 15TH OF THE MONTH beyond that date.
Ex: Cell F5 has a purchase date. (1/1/10)
Cell F25 indicates a specific number of months. (3)
Cell F5 calculates the totals of F5 plus the # of mos. in F25. (4/1/10)
But what I need to do - is to have Cell F5 show 4/15/10. (or, if Cell F5 were to come out to be 4/16/10 due to different figures in F5 and F25 -- it would need to indicate 5/15/10, and so on.)
Donwloaded a yearly calendar from the Vortex website. Wanted to know if you can have a list of dates highlighted on this calendar.
I have tried the countif function as well as various code from this forum but to no avail.
in the attached excel file, in cell D21, i have tried to join my SumIF/CountIF formula together with my 2 date range vlookups.
the first vlookup is for the start of november and the 2nd vlookup is for the end of november.
in essence, i want to the average price of all 6160 printers sold in November 07 period.
i cant get the formula working, so any help would be much appreciated by me.
I have 2 workbook named jhay and the other one is peng. In a jhay workbook in cell a1 there is a formula of sum(a2:a10).
In a workbook peng in cell a1. I want to copy the value (not a formula) of cell a1 in workbook jhay using command button.
I am trying to get some data to export from one worksheet to another, but it doesn't seem to be working.
I have put the code below:
VB:
Sub ExportData()
Sheets("Data Export").Select
Range("B3:K5").NumberFormat = "@"
[Code].....
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
How to use code tags
[code]
your code goes between these tags
[/code]
Or, just highlight all of the code and press the # button to add the code tags
A daily updated data are to be auto-transferred into a table in another workbook.
Each imported number is to be hosted in a specified cell in the table.
Is there a way to export Excel (Excel 2003) into a flat file that is comma separated and also includes text identifiers, specifically double quotes (" ") around each field in a row?
The Export Wizard in Access makes that a snap but we (more than just me) haven't been able to make it work out of Excel.
I have a a matrix table with Rows (A1:A65) and column (B1:F1) as headers containing events and the dates filling up the matrix.
So suppose A10 has "#1015"
and D1 has "Tax"
then D10 has the due date of Tax payment of #1015
How can i export this table to google calenders to let it remind me of all the coming due dates? All of these events/dates need to be recurring every year.
Want it to be specifically for google calender because i need to be able to forward those reminders to concerned people to take care of it. I only know google lets us import csv files of icalender but i dont have the format or the structure of the csv file...
I have a simple spreadsheet with 4 columns A-D and a varying number of text rows in each column. I need to be able to export it into a CSV so that it puts each column on one comma separated line (in a text file).
So it should look like this:
A1,A2,A3,A4...
B1,B2,B3,B4...
C1,C2,C3,C4...
D1,D2,D3,D4...
The problem is that when I select save as CSV it looks like this:
A1,B1,C1,D1
A2,B2,C2,D2
I know that I could rewrite the excel file in rows rather than columns but I would prefer the columns.
I have been presented with an excel document, with image names in one column (e.g. 832005_001), and hyperlinks in another column [URL]
My goal is to export the hyperlinks in the excel document into a folder as images with the corresponding image names. Is this possible to do without manually opening each link and saving it in a document, and manually renaming each picture in accordance with its image name? (as there are over 4,000).
I have this excel sheet which I want to export to a word document. The following code has been used:
[Code].....
It creates a word document but it only copies and pastes a picture of the excel sheet. I want for the user to edit the word document values that are pasted from the Excel sheet. I know I need to change somewhere in the:
[Code] .....
How can I paste values from excel to word that can be modified inside of word?
ALSO:
How can I change the layout of the word document, for example if I want to add a header and a footer?