Automatically Create New Row With New Entry From Different Tab / Spreadsheet?
Mar 24, 2014
I've got a master spreadsheet grabbing data from 12-13 different tabs/spreadsheets.
One of those has a table, and I was wondering:
Is it possible for the master spreadsheet to automatically add a new line every time someone adds a new line and creates a new entry in a different spreadsheet?
In other words,
If there is a table in the master spreadsheet, and someone adds a new line, is there a formula which allows the master spreadsheet to add a new line in order to autopopulate?
View 7 Replies
ADVERTISEMENT
Jun 20, 2014
I am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
View 11 Replies
View Related
May 28, 2006
I have an Excel VBA interface, with buttons and list boxes. I have been trying, with little joy to create the code to copy the content of the text box into a field on an Excel spreadsheet.
View 4 Replies
View Related
Oct 4, 2013
I have a user form and in my code I am using the Erow function
EROW = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
the form works fine and is loading the data to the spreadsheet and inserting it into the first empty row. I need to reverse the order of entry into the spreadsheet. So instead of the data from the form going into the first empty row down the spreadsheet, I need to insert the data in a common fixed row at the top of the sheet, every time and then shift the older data out of the row and down the sheet. Essentially putting the oldest data at the bottom and the newest at the top, which is reverse of the EROW entry process, Last row command would essentially do the same thing and I don't know what command would do what I want.
View 6 Replies
View Related
Oct 28, 2013
I am currently producing a work number tracking spreadsheet for the people I work with and wanted to know if there was a way to generate a pop up window that created fields to enter the data for the job being inputted.
I currently have it just a simple macro (below) that enters a new line, adds a sequential number and adds both the date entered, as well as an expected finishing date. It does what I want it to do, however I am looking for it to be fool-proof as the last system was broken as people didn't fill it in fully.
Code:
Sub New_Line()
Range("A4").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Range("A4").Value = Range("A5").Value + 1
Range("C4").Value = Now()
Range("D4").Value = DateSerial(Year(Now()), Month(Now()) + 1, Day(Now() - 10))
End Sub
On the previous system it used a worksheet that did similar, with dropdown menus that auto-filled the sheet as well as inputted into the spreadsheet. That was produced by an old employee that isn't working on it any more.
View 7 Replies
View Related
Sep 23, 2009
I am building Food Stand Orders Record Table, which I would like to automate and reduce the data entry process as much as possible so that it will take as long to enter all 400 orders into the spreadsheet and mitigate errors and calculate totals. I am looking for ideas as to how to make this easier to update. Also, I am not set on the concept of the spreadsheet, if there is a better way to capture this data.
Below is a list of columns in my Order Record Spreadsheet: My spreadsheet start on A5. The Header row is A5:N5
No. = record input number
Order # = The number of the order taken (i.e. 35306 – 11, 35306 – 12)
Item = The Items sold from each order #
Item Category = Was the item sold a special order or standard order
Date = The date the items and orders were sold
Time = The time the order was taken
Quantity = how many of the item was sold for that order #
Order Taker = record of who (order taker) took the order #
Item Price = What is the price of one unit x the quantity of the item sold
Total Order = What is the total cost of each item price associated with the same order #
Cash Received = We only accept cash, what was the cash tendered with the order#
Change = What is the change amount given to back to the customer associated with the order #
Donation Received = Is the change amount, When customer associated with order# did not accept the Change.
Notes = special notes related to the order#
View 15 Replies
View Related
Jul 14, 2014
We have a spreadsheet on a server.Without using code is there a way to show who saved the workbook last?
using 2010
View 1 Replies
View Related
Aug 9, 2012
I have created an example of a "Waiting List" for the college, everything I had done seemed to be working before I left for my holiday (apart from the search facility as I was still doing some problem solving, compiling and coding for it).
The Idea is to : Open Excel Document and be greeted by the "Hello" Userform
[Problem #1: This does not automatically show since I have returned from holiday, though there seemed to be no problem with it prior my escape.]Choose from three options;"Save and Exit" saves any changes on/in the database and closes the document"Insert Data" opens "Userform1" to allow users to enter a new row under the exsisting rows of data.
[Problem #2: The data in the sheet was all entered using this feature, but yet again since I have returned from holiday it keeps adding the data only on row 2]
[Problem #3: How to do Auto Formatting i.e. Postcode in Capital Letters]"Search" will open the "Search" userform which I hope to allow users to choose up to three column headings and enter the value to search that column with the parallel textbox value.
[Problem #4: The data which meets the criteria the most is listed into the listbox, if you select a piece of data (double click or "selected") it opens a new userformThe "Edit/Enrol" Userform allows user to Edit, Enrol or archive the row of data.the last Userform I created "Course" is for an admin of the workbook to choose a course and it be auto populated by the information in the worksheet "Code" and create a Mail Merge.
View 9 Replies
View Related
Nov 10, 2008
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account.
If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
View 4 Replies
View Related
Jul 6, 2006
Is there a way you can copy formulas to the next line when a new entry is automatically added to a spreadsheet via a form?
View 1 Replies
View Related
Aug 1, 2014
Is there a way to automatically move cells down after I entered data in it? This way I do not have to scroll all the way to the bottom of the list to add new data.
View 5 Replies
View Related
Nov 28, 2006
I'm trying to figure out how to create a database on Sheet 2 by filling in the info in a template created on Sheet 1. I have seen it done with the addition of a button within the Excel sheet itself, and you simply click it to send your information to the database page. I am thinking it is along the lines of OLAP or recording macros
View 8 Replies
View Related
Dec 5, 2007
How do I get data validation to automatically add an entry into its database.
Ex.
If a cell if formated for data validation and I make an entry thats not currently in its database, it enters it into its database.
View 9 Replies
View Related
Nov 19, 2009
I already have various dependent validation lists established using the INDIRECT function.
Now I want to figure out how I can automatically change the selected values given the selected list. For example:
Cell D11 is a validation list with options: Boat/Car
Cell D15 is a validation list where if D11 = Boat the options are Catamaran/Sailboat/Tugboat. If D11=Car the options are Convertible/Sedan/Midsize.
The problem is that if D15 is preselected as Sailboat, for example, and you go back and change D11 to equal Car, cell D15 remains an option from the Boat validation list. I would like it to automatically update as the 1st item on the car validation list.
View 9 Replies
View Related
Apr 17, 2008
I have multiple worksheets of computer equipment, each worksheet is a group/department. Column H is the serial number column and the entries have to be unique. I have managed to create the code below which does find duplicates across worksheets.
When error message pops up about which sheet the duplicate already exists on, the duplicate entry is deleted and the cell is blank but the error checks again and reports the blank existing on another worksheet and then it is stuck in a loop. How can I ignore the blank or null.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer
I = Sheets.Count
If Not Intersect(Target, Range("H2:H200")) Is Nothing Then
Do Until I = 0
If Application.IsError(Application.Match(Target, Sheets(I).Range("H2:H200"), 0)) Then
Else
MsgBox "That entry already exists in the " + Sheets(I).Name + " sheet"
Target.ClearContents
End If
I = I - 1
Loop
End If
End Sub
View 6 Replies
View Related
Aug 18, 2014
I have a workbook which tracks the current status of jobs and then records the changes on a separate worksheet in the workbook. Right now it is a time consuming process to enter the changes in the status log worksheet and then go enter them again in the change history worksheet.
I am wondering if there is a code that will allow the worksheet to automatically fill in an entry when something comes due. In my attached sample workbook on the 'status log' the Job number 372 in the CA # column is set up to be due today. We see in the 'what is due' column that corrective action is due.
I would like excel to automatically make an entry in the change history worksheet whenever a job becomes due. The change history worksheet has a highlighted row showing how the output would look ideally.
I know nothing about code or vba
Sample Workbook.xlsm
View 1 Replies
View Related
Aug 18, 2008
Basically i have a list of 24 invoice #'s with cost/pounds/etc.
I need a macro that would look at a list from a sheet ( Temp Sheet ).
count the number of invoice
create a tab named the invoice numbers.
So basically, if there are 24 invoice #'s. I would have 24 tabs created that are named the invoice numbers.
View 9 Replies
View Related
Apr 26, 2014
I have a customer data base going across each row about payments/address/DOB etc. When i go to alter some information in certain cells the rows become uneven and the information for customer B1 will be for C1 instead. I'm not familiar with excel but what i did notice when scrolling down i saw an outline of uneven rows.
View 1 Replies
View Related
Jan 1, 2009
I have posted about creating an Excel worksheet which would help determine proper entry into a retirement plan. Another idea that I had entailed setting up a separate worksheet which would return the dates of entry following an employee's date of hire. Therefore, an employee hired on 8/5/2005 would have dates of entry following that stand at 1/1/2006 and 7/1/2006. Then, I had an idea of creating a formula or function to add up the number of hours that the employee had worked.
Setting up a separate worksheet with the number of hours that an employee worked during the initial eligiblity computation period and the subsequent computation periods (separate respective columns for the initial eligibility computation period and then for each subsequent computation period) would help. I would calculate using sum functions.
So, to sketch this out:
One sheet would have the employee's DOH
Another worksheet would have the Dates of Entry subsequent to that DOH (e.g. for an employee hired 8/5/2005 the next Dates of Entry entails 1/1/2006, 7/1/2006, and 1/1/2007)
Yet Another Worksheet would sum the total hours that an employee had worked since DOH on a month by month basis (i.e. the total number of hours than employee had worked since his or her DOH up to a certain point on a monthly basis; e.g. an employee hired on 8/5/2005 would have on the sheet the calculation of the hours this employee had worked as of from 8/5/2005 to 9/1/2005, then the next column would have the total the number of hours worked by the employee from 8/5/2005 to 10/1/2005, etc.)
Finally, the worksheet with the DOH information would have a column which would (this would probably entail heavy use of VLOOKUP) snag the information as to how many months and years an employee had worked as of the subsequent Dates of Entry; if the employee had worked 1,000 hours and 12 months as of 1/1/2007, for example, the employee would enter the plan.
To explain the situation further:
The employee must work 1,000 hours during his or her initial eligibility computation period. That starts on the day of the first hour that an employee works for the company. So, an employee hired on August 1, 2005 who worked one hour on that day must 1,000 hours from then till August 1, 2006 to enter the plan as soon as possible. If the employee did not work 1,000 hours during that period, then the eligibility computation period shifts to the plan year. So, if the employee did not work 1,000 hours between August 1, 2005 and August 1, 2006, but did work 1,000 hours between January 1, 2006 and December 31, 2006 (this of course presumes the plan operates on a calendar year)
View 11 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
Jun 5, 2014
I want to be able to enter a value into a cell (within a specified range) and have it automatically multiply by a set value and overwrite the original entry with the result so making it all happen within one cell. The set value would be another cell on that sheet.
So the range for example is "H17:H74" and the value I want to multiply by is "D8"
Is this possible?
I've looked at another forum but it wasn't working when I wanted to define the set value as a cell.
View 5 Replies
View Related
Feb 24, 2007
I've got a software test plan that has test cases w/ individual pull-downs to select result states (All individually color-coded thanks to a macro). If any state is selected other than "Pass" or "Untested", a text entry feild appears two rows beneath the test case for additional notes and information regarding what behavior occurred when the test was done.
I need a way to a.) Automatically select this text field once any state other than "Pass" or "Untested" is selected in the pull-down, and b.) Require text entry in the field before the user can proceed to the next test case. (ideally w/ accompanying custom error message describing what information is required.)
I've pasted my macro code below: ...
View 9 Replies
View Related
Jan 30, 2008
I am having trouble creating a custom data entry form in excel. What steps would I need to take..
Attached is a example of the data, the Headers are in bold, the highlighted columns are to be drop boxes.
View 11 Replies
View Related
Aug 13, 2009
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
View 4 Replies
View Related
Mar 20, 2007
I'm making a user form in excel/vbe, that will enter data into a worksheet behind the form. I would like to make a unique reference number that is automatically generated by the form as the next in the series. It can be as simple a number as 00001 so nothing too complicated I hope.
When the user opens the user-form i would like the reference to appear on the form so that they know what it is. Then when they enter the data I want the number to go in the database with all the rest of the info they have entered and also for a message box to come up reminding them to rememeber their number.
View 9 Replies
View Related
Sep 24, 2011
I am in the exploratory stages of determining whether I can create a report in Excel 2007 to add the instances of a particular entry in a column. It is tricky, because I want to survey only the first 4 entry rows of a group of 8 entries and then return the result as only one instance of the entry over each of a set number of these sets of data rows, in each case 4 sets. This process would repeat down the spreadsheet, and the number of occurrences of each particular entry summed and reported in a separate tab of the same spreadsheet.
The rows in this spreadsheet are 17-1240. The entry names are a combination of two columns, J and K. These must match identically for the counter to count an additional entry.
I would prefer to make the report in a different tab in the same spreadsheet...just available for reference.
View 3 Replies
View Related
Sep 20, 2013
I have a program that creates a query of data into two different spreadsheets.I wish to create a master page that automatically searches for data in each of two spreadsheets and add them appropriately to the master page.
I get how to do such thing using vlookup to search for data in one spreadsheet, how to do that in two spreadsheet situation.Here is a formula that I am stuck on...
=IF(ISERROR(VLOOKUP(A2,Sheet1!$A$2:$B$3,2,0)),VLOOKUP(A2,Sheet2!$A$2:$B$3,2,0),VLOOKUP(A2,sheet1:$A$2:$B$3,2,0))
View 1 Replies
View Related
Mar 6, 2006
I am trying to update 25 different spreadsheets, and have the latest row of data from each of the 25 automatically go to a 26th spreadsheet that is my summary.
My goal is anytime I add a new row of data in any of the 25, that the summary sheet gets updated automatically.
View 9 Replies
View Related
Dec 31, 2012
I have a Spreadsheet with five columns. The column headings begins in rows 3 and 4(merged), these are Reg.#, Rank, First Name, Last Name and Station respectively.
The 5 row is used as a filter row. Therefore my Data Range is from A6:E505. So far I have input some data up to row 25.
Problem: Each time I input data I have to sort manually, I need a VBA Code to paste to automatically sort by Last Name even as I continue to input data in the other rows.
View 4 Replies
View Related
Mar 15, 2007
I have a master spreadsheet that I hold data relating to an event, the spreadsheet lists various companies names and contact details that I will need to the event and also what function they will provide by yes/no boxes. the data is currently spread all over the spreadsheet but I suppose it could be linked into some sort of simple table.
I then have to email or telex all the companies and request their assistance. Can excel automate this task? I know it can write an individual letter but could it write one message to all requesting exactly what I need that I could cut and paster to the application I would use to transmit the request?
Also when the list of Companies ends can the message move onto the next part.ie if there was a list of 4 Companies to use the message would start "Attn: Co1 / Co2 / Co3 / Co4" but if there was only 2 I would want "Attn: Co1 / Co2" only I currently get "Attn: Co1 / Co2 / /" For example
if the "catering" column and "water" column has "yes" but the "marquee" column has "no" as a check could it write "Please provide catering and water" BUT if all three columns were "yes" could it make it "Please provide catering, water and marquee" ?
View 2 Replies
View Related
