Populate A Different Worksheet With The Date "data"
Oct 2, 2008
I would like to populate a different worksheet with the date "data" that corresponds to the ROW where the last column of the table below "principal_Writdwn" is not zero. In this case period 9 date Jul 25, 2009.
PeriodDate PrincipalInterestBalance Princ Writedown
0Oct 7, 2008 - - 23,074,928 -
1Nov 25, 2008 553,576 124,989 22,521,352 -
2Dec 25, 2008 517,549 121,991 22,003,803 -
3Jan 25, 2009 481,937 119,187 21,521,866 -
4Feb 25, 2009 446,750 116,577 21,075,116 -
5Mar 25, 2009 430,982 114,157 20,644,134 -
6Apr 25, 2009 415,366 111,822 20,228,768 -
7May 25, 2009 399,907 109,572 19,828,861 -
8Jun 25, 2009 384,606 107,406 19,444,255 -
9Jul 25, 2009 369,440 105,323 18,973,238 101,577
10Aug 25, 2009 314,515 102,772 18,476,892 181,831
11Sep 25, 2009 308,281 100,083 17,986,852 181,759
12Oct 25, 2009 295,685 97,429 17,519,564 171,603
13Nov 25, 2009 290,814 94,898 17,056,128 172,621
View 9 Replies
ADVERTISEMENT
Jul 11, 2012
I have to complete information providing dates and names for a long number of individuals. Is it possible to set up the spreadsheet so that if you enter a date next a persons details in on worksheet their information is automatically updated in the relevant ones?
View 2 Replies
View Related
Feb 25, 2014
I am hoping to populate a column with data from another worksheet. Basically, I have a set of columns, one for Location, Zip Code, Longitude, and Latitude. I want a formula to say, if the location equals X, then pull the associated information from the other worksheet. -- I assume it would be an if or vlookup, or combination.
View 4 Replies
View Related
Apr 15, 2013
I'd like to create a macro such that when a person's name is typed in a cell, a separate set of cells populates with data pulled from a worksheet containing the person's data.
So, for example, I have created a scorecard template. Rather than have 50+ scorecards for each individual, I'd like to have one template upon which the individual's respective data can be pulled into the appropriate cells based on the name entered in another cell on the template (or a pop up in which the name is entered).
View 3 Replies
View Related
Nov 26, 2012
I'm trying to figure out a way to populate multiple cells automatically with certain information after entering info into a single cell on a separate worksheet in a separate workbook.
Our "master" sheet has 22 total columns (A-V), and our weekly "status" sheet only has 7 total columns (A, E, P, L, M, N, R).
Column A on both sheets is labeled "Sample Number". On the "status" sheet I want to be able to type, for instance, "2012-228" into A2 and have the corresponding values from Columns E, P, L, M, N, R on the "master" sheet automatically populate into B2-G2 on the status sheet if this is at all possible.
View 5 Replies
View Related
Sep 27, 2006
I have a userform that stores data (1 row record with 20 columns) to a worksheet.Is there a way to grab the data back in (other or the same)userform, ambent(correct) data and store them back in the same row in the worksheet?
View 2 Replies
View Related
May 1, 2013
I have a table of data from which I would like to extract the information which is listed horizontally.
Column A Column B Column C Column D
Code Name Address Locality
SMITJOHN John Smith 123 Bell Rd Perth
On the 2nd worksheet I have a form which I have designed to which I would like to extract the information from the 1st worksheet.
I am hoping that it is possible to start entering a code based on some reference on the Name. Eg SMITJOHN
Once the code is true or correct it will populate certain cells on the 2nd worksheet automatically.
I work in transport and am looking to streamline some of the paperwork and am trying to do it within the means I have available to me.
The format of the 2nd worksheet is;
CODE: SMITJOHN
NAME: John Smith
ADDRESS: 123 Bell Rd
LOCALITY: PERTH
View 3 Replies
View Related
Jul 2, 2014
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get the minor files (68 seperate files) to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make matters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
View 7 Replies
View Related
Feb 19, 2014
I have made a Rota of sorts using Excel 2013 Desktop Edition for my charities volunteers (and stored it as a shared file via office 365 server that they can download and edit) and this rota is populated by our volunteers manually. Each day our controller needs to check the file to see who is on duty at that time.
How the Rota is populated.(the bit i managed to do myself)
The volunteer (Person A in this example) would open the excel file and go to the month they wish to choose a shift for (ref worksheet: FEB in this example). They would then pick a shift that suits them and click on the cell (ref: F32) that shows a vehicle available, then from the drop down list they select their name and then save and close the sheet.
Rather than our volunteer controller going through the sheet for the current month (ref worksheet: Sheets JAN to DEC) I would like them to use the first sheet in the workbook (ref worksheet: DC Info Page) to get an instant view of which volunteer is currently on shift.
My current problem
I don’t know how to make the excel file do the following
Search sheets JAN to DEC (ref cells: C4:I58 on each sheet) inclusive for the cell that contains today's (current actual) dateCopy the 8 (eight) cells below the cell that contains today's datePaste the copied cells in to the relative cells (ref: C8 to C15) in sheet one (ref worksheet: DC Info page)
I would also like this to be done automatically so the controller does not have to click on anything after they open the file. But if it needs a button to process the request, one could be added to the worksheet (ref: DC Info Page)
View 1 Replies
View Related
Sep 11, 2013
I have two worksheet. One worksheet is a form where in the Field Door No. should be a dropdown list. The list will be retrieve from worksheet Source. Now upon selecting a specific Door No. it should also autopopulate the respective Type, Brand, Serial No., and Type.
First thing i need to know is what should i do on Cell B1 worksheet Form for it to be a drop down list retrieving the Column A on worksheet Source.
Second, upon selecting for example ATC0009 on the drop-down, it should auto-populate like these
Door No. ATC0009
Type AT Crane
Brand A
Serial 43679
Type Crane
View 14 Replies
View Related
May 11, 2009
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
View 5 Replies
View Related
Sep 30, 2009
I've created a single worksheet that is essentially a "dashboard" type of report that displays data for a single employee/person. This worksheet (let's assume it's my "template") contains formulae that is driven by a single lookup cell (in this case, the employee's ID, in cell, for example, A1).
This "dashboard" report (for just one individual) needs to be duplicated for the entire employee population (let's say ~100 people); this employee population is stored as a list in a separate worksheet, in a single column.
Is there a vba solution that will copy my template and populate the cell A1 (of each duplicated template worksheet) with the the employee IDs - of course, it would be ideal if the solution can search in my employee ID list and continue down until there are no more IDs and create the same number of worksheets as there are IDs?
Because the macro is creating numerous worksheets, is it possible to name the worksheets based off of an adjacent value (in the same sheet as the IDs) to each respective ID (I'm thinking of a concatenation of the employees' IDs and names)?
One major wrinkle in this process: the above process would be ideal if the "template" worksheet won't change (i.e. no changes to formatting, no additional data elements, etc.) However, it's likely that the client will want to add/remove/change items/elements to the "template" dashboard. Assuming, at this point, all 100 worksheets for each individual/employee have been created, it would be a huge pain to manually add/remove elements from each sheet. Is there another macro that would delete ALL employee sheets? That way, I can re-modify my single template and just run the original macro to re-create my duplicate sheets (but populated with different IDs) again.
View 9 Replies
View Related
Aug 10, 2014
I am building a budget spreadsheet and would like the date to automatically populate in column H when data is entered into rows J through AN, or alternately update column H when a sum total is populated in column I
I need to repeat this for rows 4 through 63
I was trying to use the following code, but cannot get it tweaked right.
[Code] .....
View 1 Replies
View Related
Dec 3, 2012
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Nov 26, 2012
How do I populate a second worksheet with only the preselected highlighted rows of cells from another worksheet.
This way I will end up with only the selected data from the first worksheet in the second worksheet.
View 9 Replies
View Related
Jan 7, 2009
Sheet 1 contains a column titled "name" and 6 columns to the right of name titled "sat", "sun", "mon"........ the col titled name is not the first col in the work sheet.
In each row I enter the guest name under the name col and I enter a room number (example: 1A, 1B, 2A, etc...) in the column to the right that coresponds with the day the guest will be staying.
Name Sat Sun Mon Tue Wed Thu Fri
Mr. Smith 1A 1A 1A 1A
Mr. Jones 3B 3B 3B
Ms. Tiller 4A 4A 4A 4A 4A 4A 4A
Sheet 2 is in the same workbook and looks something like this:
Room # Sat Sun Mon Tue Wed Thu Fri
1A
1B
2A
2B
3A
3B
I want it to look at sheet 1 and populate the date columns with the guest name from sheet 1
I tried this formula =IF('Sheet1'!G:G="1A",'Sheet1!$F:$F,"Available")
It seems to work on the first row but I have problems with any rows below that. Basically it acts like there is no data in the rows below.... I think the fact that the names on sheet 1 are not alphabetical is creating part of my problem? I tried a lookup formula but it seems like it has to be the first column in the worksheet and it has to be alphabetical to work like that.
View 4 Replies
View Related
May 26, 2014
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies
View Related
May 30, 2013
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
View 2 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
Feb 14, 2014
I have my training file that is currently set up and working how I want it to, however I would like to go one step further if it's possible.
on the FIRE EXTINGUISHER TRAINING column there is one person out of date and 14 people that need the training, how can I populate all the name that need this training onto sheet 1 into the box that I have created. I'm wanting this to happen automatically so I don't have to retype the name onto this sheet.
View 14 Replies
View Related
Jun 13, 2009
I am struggling to populate a listbox with data from a different worksheet.
If it is the current worksheet works fine.
The worksheet is called "Drawing Data" ..
View 2 Replies
View Related
Jun 28, 2007
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies
View Related
Mar 4, 2013
I have a workbook with 2 worksheets being used. Sheet 1 has the months of the year starting with Jan in A1, Feb in B1 etc. On Sheet 2, I have a column of information. The first cell in the column eg. B1 has data validation list based on the months on Sheet 1. The information in the rest of the colum on Sheet 2 needs to be copied to the relevant colum on Sheet 1
View 5 Replies
View Related
Apr 8, 2012
copy the excel sheet data from one sheet to another sheet?
-> I have one excel sheet (name: Test.xls, sheet name: SHEET1)
Sn Code Type next calib
5BPR CORR7-Apr-12
4BPR CORR7-Apr-12
73BPR CORR7-Apr-12
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
17BRG CORR9-Apr-12
311DP CORR9-Apr-12
227DP CORR9-Apr-12
227DP CORI R9-Apr-12
Want to create a new work sheet and copy the today's date(next calib - filed name) records to new sheet.
Example: Today's Date is 8-Apr-12
So, I want to copy following record to new work sheet (when I click the button / run the macro).
Sn Code Type next calib
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
View 3 Replies
View Related
Jul 31, 2007
Is it possible to automatically populate a summary worksheet, based on matches in other worksheets within the workbook which match a particular date?
I have a workbook with 15 worksheets--each sheet is setup like a database with 4 columns (fields). One column is for the date.
I would like to populate a summary sheet that serves as an executive summary of all "records" which match a particular date.
View 10 Replies
View Related
Mar 18, 2009
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column B on the summary page for each row of theirs that is copied over....here is my current macro I use to copy the data: (this code is repeated for each sales persons sheet - "Blankenship", "Dew", etc...) I have attached a scaled down version of my file.
View 2 Replies
View Related
Oct 19, 2009
I've only just starting using Excel for anything other than basic calculations and have got a little stuck with a user form. On my attached spreadsheet I am trying to set up a form for staff incident reporting. There is a Contents worksheet and then each incident has it's own detailed worksheet. The user will click on 'create new incident' on the Contents page.
This opens a user form. From this form I want to populate the contents worksheet. I then also want it to populate the relevant incident worksheet. I can populate the contents page but I need help getting the correct Incident worksheet populated at the same time. Hopefully all will become clear running the file.
View 5 Replies
View Related
Mar 8, 2008
I have an excel worksheet that I need to populate with a few thousand data points from MS Access. Currently I do this through vba code somewhat similar to this:
rst2.Open myQueryString, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
Do Until rst2.EOF
wsht1.Cells(1+counter, 1) = rst2!val
rst2.MoveNext
counter = counter +1
Loop
rst2.Close
Now this method works, but it is pretty slow to load. I remember reading as a general excel optimization technique you should avoid using long loops accessing cells on an individual basis.
Is there a better way for me to dump large clumps of Access data into Excel, instead of populating it cell by cell?
View 9 Replies
View Related
Jun 4, 2009
I am currently working on a project that has six worksheets with information to populate into one master worksheet.
I would like it to work as if the information is not in the first worksheet to search the second sheet and so on and so forth.
Here is the complex part, the information that I need does not always appear in the same spot…Example: Sheet 1 has Weekly Benefit under B20, Sheet 2 has Weekly Benefit under B22, and Sheet 3 has Weekly Benefit under B23.
Here is what I have, unfortunately not working very well for me…
VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
View 9 Replies
View Related
