Excel 2003 :: Extracting Sub Sheet Of Data From Larger Schedule

Dec 31, 2013

I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.

I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.

I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)

View 7 Replies


ADVERTISEMENT

Excel 2003 :: Pulling Data From Amortization Schedule To Debt List

Feb 25, 2013

I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).

For example, this is my 'Amortization Schedule':

Balance
Due
Interest
Rate
This Month's
Interest
This Month's
Payment

[code]....

And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)

Name
Starting
Balance
Remaining
Balance
Interest
Rate
Minimum

[code]....

For example, if today were 1/15/13, I would want $3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?

Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a $0 Balance Due?

View 2 Replies View Related

Excel 2003 :: Extracting And Collating Data From Multiple Columns

Apr 27, 2014

The link to my file is: [URL] ........

I am using excel 2003.

My query goes like this:

I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".

However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).

View 1 Replies View Related

Extracting Name From A Larger String

Jan 23, 2008

I'm trying to extract a first name and surname from a username :

CN=Joe Bloggs/OU=stneots/OU=EU/O=SAC needs to read Joe Bloggs
CN=Fred Smith/OU=stneots/OU=EU/O=SAC needs to read Fred Smith
CN=Ray Jones/OU=stneots/OU=EU/O=SAC needs to read Ray Jones

The string is always in this format and the name always has a space between. Given a day or so I could probably figure it out, but unfortunately deadlines are breathing down my neck,

View 9 Replies View Related

Excel 2010 :: Cross Checking Info With VLookup And Extracting Data From Sheet

May 9, 2014

So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:

=VLOOKUP(B(//SomeValue),Locations,1,FALSE)

I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:

=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")

Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010

View 2 Replies View Related

Excel 2003 :: Formula To Pull Data From One Sheet To Another?

Jan 16, 2014

I have Excel 2003

My spreadsheet has multiple lines of data for multiple items (have a specific item #) and I need to be able to enter an item # on a different sheet so that the requested information can be pulled from the data source. The items have information on multiple rows. I need to have all of the rows pulled into my output file when I specify the specific item. #.

I have attached a spreadsheet showing the type of data is contained and what my desired output would look like.

View 1 Replies View Related

Using Excel Data To Create A Game Schedule

Jul 11, 2013

I am trying to use Excel to generate a game schedule for my fantasy football league. Right now I handle this task manually but I figured there has to be a way for Excel to work it out.

Let's say I have 16 teams playing 16 games. I have a table written out using numbers to represent the different teams. The x-axis has one team number per column while the y-axis has a week number in each row. The intersection of any column with each row shows the x-axis team's opponent for the week. What I want to do is substitute a team name or abbreviation for the team numbers in the body of the table and then generate a formula (or script? - I'm not sure of the correct terminology) to then produce a listing of the games each week. So the output would look something like this:

Week 1
BNS v ROC
NYL v NFB
WBB v TCF
etc...

Each 3 letter abbreviation represents a team name. This would significantly reduce the amount of manual work I have to do since Excel would generate the game schedule as above for each week and then I take that information and dump it into the software I use to run the League.

View 3 Replies View Related

Extracting Summary List Of Larger List Showing Only Items That Have Quantities?

Apr 11, 2014

It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.

What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.

I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.

I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.

View 8 Replies View Related

Excel 2003 :: Extracting Text From Text String?

Sep 17, 2013

Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003

View 7 Replies View Related

Excel 2003 :: Conditional Format - Highlight Cell In Sheet 2 If Duplicated From Sheet 1

Mar 25, 2014

I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.

I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.

View 3 Replies View Related

Excel 2013 :: Averaging Rows Of Data Within Larger Blocks Of Datasets - Skipping Rows

Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

View 1 Replies View Related

Excel 2003 :: How To Detect Pattern In Sheet

Aug 25, 2013

We have a huge excel-sheet, containing disputes with customers. Everytime there is a dispute, the customers data will be transferred into the sheet. The sheet is organized like this:

DATE / NAME / ID / COMMENT / FREQUENCY (how often one customer appears in the list) /ID2 (ID combination of the date + ID) /Weekday

And here comes the task:

Find all customers who appear 10 work-days(Mo-Fr) in a row. So if a customer appears Mo-Fr and keeps on being in the list the next Monday etc. it still counts as "in a row". Same with official holidays. If a customer appears at a certain date he is "booked" and it doesnt matter if a appears a second time on that specific date.

Attached to this post you find an example in which you can see the structure of my excel sheet!

example.xls

Note: I had to use a german version of excel when I made this little example - thats why the formula in the collum "Frequency" says "Zahlenwenn" its the german version of COUNTIF

Excel 2003

View 7 Replies View Related

Excel 2003 :: Selecting Only Certain Items From One Sheet To Another?

Mar 26, 2012

Using Excel 2003

We have a spreadsheet set up showing a list of sales invoices, each invoice has the customer name and a customer reference assigned to it i.e B1, B2 etc

We want to set up another sheet that takes the sales invoice information but only picks up that which relates to a specific customer, i.e want a list of the invoices relating to B1

This will be done with a view to creating statements for each individual customer reference number which will just take info from these sheets B1,B2 etc

View 1 Replies View Related

Excel 2003 :: Log Sheet - Creating Invoices

Apr 20, 2014

I am using Win XP Pro, Excel 2003. In the workbook, I have a invoice spread sheet (lets call it sheet1) to create invoices. Once the invoice is completed, I save it with this macro which saves it to the contents of E3 and E7 for the file name:'

Sub SaveMeExtraQUOTE()
Application.DisplayAlerts = False
' Dialog's Title
strTitle = ""My Tite""
If MsgBox("WARNING !! STOP! Enter ALL your quotes information to be saved BEFORE saving the file!!!

[Code] .......

This is working just fine BUT it keeps saving it to a template file where the master template is kept. Why will it NOT save it to the QUOTES folder when I am using this:

Filename:="C:EXCELATX Concrete DesignQUOTES"

I am also trying to have a space between the contents of cell E3 and E7 when the file name is saved. Now it just puts them together.

View 2 Replies View Related

Extracting / Transposing Sheet Data?

Mar 9, 2014

I have the attached file with two worksheets D30(1) and Sheet 2.

I need to extract some of the information contained on the D30(1) sheet into the format as contained on Sheet 2. It is almost like a transpose but because all of the information is not needed and all cells don't have info in it, I didn't even try to transpose the D30(1) info.

Is there an easy way to extract the information from sheet D30(1) to present it in the format of Sheet 2? I need to do this for approximately 50 sheets similar to D30(1). Some sheets might even have more columns than the example provided.

View 4 Replies View Related

Extracting Dates Data From The Same Sheet?

Apr 1, 2014

I'm attaching the test file with one example sheet. Within the same sheet, there is month end dates in column A, and from column B till column E, there are 4 variables (Foreign LT Debt, Local LT Debt,Foeign ST Debt and Local ST Debt). Their respective month end values are already given from column G till Column R. How it is going to be extracted is that against each effective date for that variable (Foreign LT debt, for example), the Rating is given under Rating header. So, for instance, for Foreign LT debt, rating from 25/8/1993 till 4/2/1997 is BB-. So, for all CELLS in column B between these dates, the rating should be BB-. Before that, since there is no value given, I want to put it NA. I have filled the values in column B for demonstration. I want to write a function or perhaps a macro to do that automatically for these 4 variables. There are 50 + sheets with the same layout, so I hope the macro or look up function have not to be written 50+ times.

View 1 Replies View Related

Protecting Data When Extracting From Another Sheet

Feb 17, 2010

I need is for the Roster sheet, which has the exact same column and row headings as the Leave sheet, to read the data from the Leave sheet and change dynamically if the Leave sheet is changed. If there is data in any cell then I need to protect just that cell in the Roster sheet so that the user creating the staff roster is unable to edit it out.

I have created a plain cut and paste macro on open from Leave to Roster but this doesn't give me the dynamic linking, and doesnt help solve the problem of needing to check each and every cell in Roster against a corresponding cell in Leave, but also if its blank in Leave to allow the user to update that cell in the Roster sheet.

View 3 Replies View Related

Excel 2003 :: Identify All Sheets Related To One Sheet

Oct 13, 2011

I have been given an excel model to review;the workbook has 53 sheets. I would like to know if there is a way to create a flowchart/matrix with the relation of all the sheets. I would like at least to get a list of all the sheets related to each sheet. All what I was able to do is to get all the sheet names in one sheet. (I am using Excel 2003).

View 2 Replies View Related

Excel 2003 :: Finding First Empty Cell In The Sheet?

Nov 8, 2011

I have two work sheets where I have data.

Sheet1 contain daily input table which as follows:
Name salaryBonusXX10002XY9001YY11002ZY15003ZZ12502AA10502AZ9501

Sheet2 have table where all the information is saved. So we can say this is database of sheet1. Which store every day information of sheet 1.

SALARY DATABASEMonth Name Salary Bonus

Problem:

I want that when I finished writing on the sheet 1(which i do manually) then with macro it go the sheet 2 and find first empty cell in the name column and past the all the data of sheet1 table. Months I will put manually. I am working on MS2003.

View 9 Replies View Related

Excel 2003 :: Error When Pasting To Protected Sheet?

Feb 7, 2013

Excel 2003
Windows Vista

I keep getting this error: (at the line highlighted in green)

Runtime error '1004'
PasteSpecial method of range class failed
**** Open workbooks to transfer temporary data

[Code]....

View 2 Replies View Related

Excel 2003 :: Copying Row To Different Sheet Based On Condition

Feb 16, 2013

I wanted to know if it was possible for me to be able to copy a whole row into a different sheet based on a column value. For example, if B6 = CLOSE, the whole row would be coppied to the sheet name CLOSE. If B6 = OCCUPIED it would be copied to sheet name OCCUPIED. I also need if W7 = to a date thats passed today it would be added to the sheet name EXP PRD. An off topic question, i have this code for column "W":

=COUNTIF(W6:AD155,">"&TODAY()+15)

It works just fine, but I also have "N/A" / "INDEF" in the blocks as well so it's not giving me the correct results. I'm using Excel 2003.

View 8 Replies View Related

Extracting Matching Data From Two Sheets Into Third Sheet?

Jan 17, 2014

I'm not sure if this is best handled in the Formulas & Functions section or elsewhere, but this is my best guess. I have a spreadsheet used for tracking hardware issues with data in two worksheets:

1. PageCount
Column 1: Date
Column 2: Serial#
Column 3: PageCount

2. Tickets
Column 1: Date
Column 2: Serial#
Column 3: TicketNumber

What I'm trying to do is find a way to extract from those two sheets the date, serial number, page count, and ticket number where the date / serial # are the same and dump this to a new sheet. I've thought that I may need to concatenate the date / serial number together into a single cell or something, but I'm really at a loss as to how to pull this data. Part of me thinks this should be done in SQL, not a spreadsheet, but that's how our reports are produced.

View 1 Replies View Related

Extracting Data In One Cell And Populating Another Sheet?

Apr 26, 2013

I want to create a sort of complex logging system for my work.

As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.

View 4 Replies View Related

Extracting Table From Data Within Excel Using VBA?

Feb 10, 2014

I have some data, in a xlVeryHidden sheet, with multiple headers. An example is given below.

I would like to auto-generate a table on a different sheet with only revelent headers and filter by source and month/year. Basically, I want to write a macro that would make this table for, say, January and source1 only, without headers 1, 2 or 3.

(Very similar to the Pivot table, only without combining the data)

--------------------------------------------------------------------------------------------------------
Source
Type
Ref

[Code]....

View 1 Replies View Related

Excel 2003 :: Information From 6 Different Worksheets Populating One Master Sheet?

Oct 23, 2013

(EXCEL 2003)I have 6 worksheets for 6 separate ad reps that will be populated with customer info. To make my life simple I just want column A from each of the 6 worksheets to show up on a master sheet in column A. But what also needs to happen is if we add and delete customers that also needs to happen on the master. I understand the "=" and then pointing to a cell in another sheet and hitting enter. I got that to work but I just have soooo many cells to work with that would just be way too time consuming.

View 2 Replies View Related

Excel 2003 :: Unprotected Cell Won't Allow Input On Protected Sheet

May 19, 2010

Using excel 2003, Workbook has several sheets, all sheets are protected workbook is not. I have my cells containing formulas locked, I have unlocked the other cells that require input of various data. When the user goes to type in a "unlocked" cell, (to over write the now obsolete data) it will not allow the user to delete the data, the curser remains a cross and the formula bar or the cell itself will now allow any changes.

I have double checked that the cells are unlocked. I can enter data after the last entry in the unlocked row and or column, but I want to be able to "cut or delete the data that is no longer need and begin entering data from the beginning cell of choice. I want to copy this "old" data to an archive sheet, but it will now allow me to select it.

Example: I have 6 columns starting, (a to f) the formulas are in columns e and f and start at row 4 These columns are locked and hidden. Columns a,b,c and d are unlocked for user input. All data is started from A5 which I want it to start from each time the old data is deleted and new data entered. If I place my curser on any of the cells in the e and f rows, the cursor remains a cross which is what I expect it to do, but columns a to d are doing the same thing, they are acting as if they are locked OR atleast those cells in the a to d areas which have existing data, as I can enter below.

View 7 Replies View Related

Excel 2003 :: Deselect Grid Lines To Get Clear Sheet

Jul 15, 2012

How do i deselect grid-lines in Excel 2003 so as the sheet is clear.

View 6 Replies View Related

Excel 2003 :: Renaming Textboxes Based On Their Position In Sheet?

Jun 14, 2013

I have an excel file (2003 version) with one sheet called sheet1.

On sheet1 I have multiple text boxes, however each text box has the same text box number "Text Box 1244" (this number appears in the top left-hand side in excel when I click the textbox).

I was wondering if it's possible to rename the text boxes based on their location on the sheet.

Eg. If i had a sheet with 5 rows of text boxes and 3 columns of text boxes (15 text boxes in total).

The top left-hand box gets renamed to "Text Box 1", then the text box below that gets renamed to "Text Box 2" and so on to the bottom of the sheet to "Text Box 5". Then the vba script would move to the text box that was to the right of the first text box (1st row again but 2nd column), and rename all the text boxes in that column ("Text Box 6" onward).

The outcome would look something like:
TB1 TB6 TB11
TB2 TB7 TB12
TB3 TB8 TB13
TB4 TB9 TB14
TB5 TB10 TB15

I'm stuck on this problem a while now and cannot find any scripts to solve it. My VBA is non-existent, I usually get by on bits of code I find on the web.

I hope each textbox has a hidden co-ordinate associated with it, then it might be possible to loop through all the textboxes based on their positions and rename them.

View 3 Replies View Related

VBA Macro - Formula Extracting Data And Transpose Into Another Sheet

Feb 3, 2014

Extract data from one sheet to another sheet then transpose automatically.

I have attached the work book of what I'm trying to accomplish.

View 14 Replies View Related

Extracting Similar Data From Different Worksheets And Displaying On One Sheet?

Apr 13, 2012

I have two work sheets as-

Sheet1

idnameclassscores
1abc280
2efg276
3hij555
8klm478
9mno490

Sheet 2

nameschooldate of admission
abcpublic school2/9/2011
efgpublic school3/4/2010
hijprivate school5/9/2011
klmprivate school8/9/2011
mnoprivate school9/10/2011

now what i want is - on sheet 3 compiled data as-

idnameclassscoresschooldate of formation

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved