Automatically Make New Tables?
Nov 10, 2009
I got a excel file which i use to add invoices on. But these invoices are numbered and in a book.
1 book has 50 pages, i have to write the amount of $$ on it. But i made this file to enter the amount of money on the invoice. And the sheet automatically gives me the total amount of money which was earned when the book is full.
Now i was wondering if its possible to let excel somehow add 50 new lines with the same formulas used in the first 50 lines. So once line 50 has info enter, excel will add 50 new ones with the correct info(book and page numbers).
Can excel do this with some sort of macro?
View 14 Replies
ADVERTISEMENT
Nov 28, 2013
I need to make a Combined Name List from several tables by formula or Macro code.
Attached file defined detail and what is exact wanted.
Unit&ValuesList.xls‎
View 3 Replies
View Related
Oct 22, 2013
I want to take the values in Column U, attach the names in Column A, and rank from Highest to Lowest
Then I want to do the same with Column V/Column A
And then again with Column W/Column A
And then create 3 tables within the document that display the top 5 in each category.
It's already done, but by hand. Is there a way to automatically set this up?
See A27-A32 for what I want done automatically
View 14 Replies
View Related
Sep 22, 2009
I have 3 status sheets (about 300+ ea.) that I was given to sort out.
Information:
1) Column A: Number of items (i.e. 1 )
2)Columbe B: Rec'd Date + initials + no. of copies received, followed by notes (i.e. 021709,akb,01)
Since there is only one column with all the information together, is there a way to sort the attached sheet by initials? I don't know how to create a formula to pull all the date,mjg's; date,jac's; date,akb's; etc... into a separate table.
A: No. of items
B: Date,mjg... = Total no. of items
C: Date, abk... = Total no. of items
D: Date, akb... = Total no. of items
View 9 Replies
View Related
Jan 15, 2014
how to make a % of running total in pivot tables in excel 2007. the running total in is only available not the "%".
View 1 Replies
View Related
Oct 11, 2013
I have a table. I give this table to 3 different people. Each one fills information in their corresponding table.
How do I do it, so their information gets feeded into my table, without overwriting each other.
View 3 Replies
View Related
Feb 17, 2014
trying to get pivot tables to automatically update every 4 minutes.
I have 3 worksheets:
worksheet 1: SCANNER, this sheet pulls in data externally using an =RTD links
worksheet 2: RAW, this manipulates the data above, and this is the data used to create the pivot tables
worksheet 3: SUMMARY, the pivot tables are here, there are 9 of them.
Worksheet RAW, changes every so often, and every 4 minutes I would like to have the pivot tables AUTO REFRESHto make sure its using the latest data from RAW.
I see options to autorefresh on the opening of the workbook, but cant see anything to REFRESH EVERY X MINUTES.
Is there any way to do this with VBA ?
View 3 Replies
View Related
Dec 5, 2011
Sheet one has seperate logs for each item received in January and the reasons they were returned - so if you can imagine ( note / demonstrates column seperation) the following column headings: Date received / item description / approved within 1 day / returned for high risk.
On sheet 2: i've got a simplified data table which tells me that in January, there were 10 occurances of items approved within 1 day; and 8 occurances of items returned for high risk.
So sheet 2 looks like this:
01 Jan 2012 / 31 Jan 2012 / approved within 1 day / returned for high risk
I'm trying to get it so that any data input into sheet 1 for January will automatically update these columns in sheet 2.
This is what i've attempted to do:
what is the sum of the "1's" demonstrating occurances of "approved within 1 day" in sheet 1: Sheet1!J15:J179
if the date received in sheet 1 is between 1 Jan 2012 and 31 jan 2012 sheet 2: TODAY(Sheet1!D15:D179>Sheet2!A14
So at the moment i've got:
=SUMPRODUCT(Sheet1!J15:J179)*TODAY(Sheet1!D15:D179>Sheet2!A14
View 1 Replies
View Related
Dec 3, 2009
I've written a program to read data into a custom table from ASCII
files. The ASCII files are created from a VB script I've added to an Excel
spreadsheet. The VB script in Excel is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim str_fileName As String
Dim int_fileNum As Integer
Dim str_fileName2 As String
Dim int_fileNum2 As Integer
MsgBox "Testing"
str_fileName = "\EPCOR02COLLECTSinto.txt"
str_fileName2 = "\EPCOR02COLLECTDisa.txt"
Application.EnableEvents = False
If (Target.Address = "$D$2") Then
'Write to the Sinto file
On Error Goto Err_CreateOutputFile
int_fileNum = FreeFile...............................
View 2 Replies
View Related
Mar 20, 2009
This is the first time I’ve tried to use VBA. I’m using Excel 2000. In my excel workbook have 1 sheet called “Cards” in which I Change data in one cell $B$2 (enter a part number) and formulas in this sheet return many values from another sheet “Card Data”. In the “Card Data” sheet, formulas that look through a sheet “Sales Book” and return the data that pertains to the Part Number I entered in the sheet “Cards”. In the sheet “Card Data” there are 3 pivot tables (“PH CALC”, “PH QTY”, and “Pivot Table 3”) that use the Part Number data to show 3 different sets of information. The pivot tables are the source for 3 charts in the “Cards” Sheet.
Problem: Pivot tables don’t automatically refresh. I would like to use VBA code to automatically refresh the pivot tables in the “Card Data” sheet when I change the Part Number in cell $B$2 of the “Cards” sheet, which, in turn, would then update my charts in the “Cards” sheet.
View 3 Replies
View Related
Sep 23, 2008
I am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel.
I am wanting my information to enter the tables and then automatically move into the next available cells below.
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("details of cars in stock")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.txtreg.Value) = "" Then
Me.txtreg.SetFocus
MsgBox "please enter a registraion number"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.txtreg.Value
Me.txtreg.Value = ""
View 9 Replies
View Related
May 13, 2014
Capture155654.PNG
this is the set of data i have
My problem is now i have to add data of adap.tv in such a way that if a entry comes in with adap.tv and a role already present there in month of jan like happened there with adap.tv in the month of jan and role sr.development engineer it should automatically add data from past month and give value.
I already tried some if and funtions and combination of vlookup.
View 1 Replies
View Related
Jun 18, 2009
i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.
View 9 Replies
View Related
Jul 8, 2014
I am trying to take the tables you will see in the dummy page on the second and third sheet and have them output onto the first sheet based on the parameters of the drop down menus. Is there a way to have a button on the first page that controls a macro to do this?
View 4 Replies
View Related
Mar 30, 2013
I receive a large file every month with 30K+ rows of data that need to be manipulated manually before I can send it off. Within the data are "chunks" of data that are anywhere from 1 to 90 rows each. Each chunk needs to total 100 or less, and while they do for the most part, some end up over 100 due to rounding (usually 100.02 or so). The rows above and below each chunk are blank, and I currently have a calc to add each chunk, and flag it if it's over 100. Then, I have to go through each of the flagged chunks and manually decrease one of the lines to make the total 100.
Is there any way to automate any or all of this procedure?
I have attached a sample file with two chunks of data. The actual files contains 14 more columns to the left that aren't shown.
Sample.xlsx
View 5 Replies
View Related
Jan 6, 2014
In column B I will be entering a number and I would like it to automatically enter gallons in column c when i do so. So if i enter 356 in b2 i want it to say gallons in c2.
View 1 Replies
View Related
Aug 1, 2013
I want to make a spreadsheet that has codes that make items show up on another sheet but how to do this.
For example I would start on sheet 1 and on cell A2 I would type the amount $100. Then in cell B2 I would type in F (I will call this a code).
Now on sheet two I want the amount on cell A2 to show up in a column of items designated for F items. If I had typed in G instead I would have wanted it to go to a column for G items. All the values under the given code will show up in the column that is represented by them.
View 1 Replies
View Related
May 9, 2014
I have attached an example sheet : LATEST.xlsm
To sum up - need a drop down box - that when "flange" is selected - It opens a file automatically - no clicking etc. just when that option in the drop down box is chosen.
[Code] .....
But doesn't work, plus means I have to click on something.. etc
View 7 Replies
View Related
Dec 11, 2011
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.
View 2 Replies
View Related
Sep 11, 2007
I have created an attendance tracker for a team of people at work. It simply involves a grid of the days of the month and on each 'day' (or cell) I have set up the Validation command to prompt the the user to select whether they were at work, on holiday, off sick etc. etc from a drop down box. If they do not change the cell, its defaulted value is that they are in work.
When a user selects the days that they are planning a holiday in the future, they are meant to write a comment on the cells they have changed, to advise when they had 'requested' the holiday.
What I would like is (a macro?) which makes the comment box automatically 'pop up' when they change a cell from its default value prompting them to fill in the details rather than relying on them to 'add comments' manually.
In addition, what would be the best way to 'restrict' them from booking holiday within the next 7 days - we have a problem with people booking holiday on 'lastminute.com' for 2 days time, booking off the holiday on the spreadsheet and saying that they had it planned for weeks!
View 9 Replies
View Related
Nov 27, 2012
If you look at the file I attached you will see the tabs at the bottom are months of the year. I know how copy a sheet but every time I make a new month tab I have to retype the month in the top left corner of the table, in the tab, and other places. This gets really repetitive when I have to do this for literally around 20 other spreadsheets like this.
What I'd like is a way to create new month tabs that automatically rename the month and year in chronological order on the sheet and tab if possible. 2 months it's because I'm constantly having to tweak spreadsheets for my boss. It'd be pointless to go ahead and make a template because then I'd have to go back and change the code for every month. So I just create tabs and edit code month by month.
View 4 Replies
View Related
Mar 13, 2013
I have a sheet named PE which is the main source of handling a contents in other sheet.
How to make a selected contents in PE sheet appears automatically according to weeks in sheet1?
How do i use data validation, offset or combo box to solve this problem?
View 7 Replies
View Related
Mar 14, 2014
I want a macro. when i run this macro pop up a input box and ask for copy of master copy and asked for rename the file...
E.g. I have a master file in "z:42766decmasterfile.xlsm
When I click macro then macro do a copy of masterfile and rename it according to choice...
View 4 Replies
View Related
Feb 9, 2014
I have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
View 6 Replies
View Related
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Jan 24, 2014
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
View 5 Replies
View Related
Jun 29, 2009
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09
View 2 Replies
View Related
Feb 19, 2014
I am trying to merge two different databases in the next week. The problem I am running into is the name from 1 database isn't always the same on the other database.
I want to merge Database 1 with Database 2 and create a relationship with them both by using the database 2 internal #. As you can see in my table database 1 has similar names to database 2 but they are not exact. I want to match these names and have the formula on match tell me what the database 2 internal # is. I have also uploaded a sample excel doc with a note in B2.
database1
Herb Chambers Ford of Braintree
Herb Chambers Honda of Seekonk
Dimmitt St. Petersburg
Database 2 matched internal #
Database 2
Herb Chambers Ford of Braintree (Dave Dinger Ford)
Herb Chambers Honda Of Seekonk (7-FI)
Dew Cadillac (Dimmitt of St. Petersburg)
Database 2 internal #
15360
15301
25413
View 2 Replies
View Related
Jun 10, 2008
I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.
View 12 Replies
View Related
Feb 22, 2010
I think this requires SUMPRODUCT, but can't work it out.
The attached spreadsheet should explain clearly.
View 14 Replies
View Related
