Compare Two Financial Reports From Two Different Sources

Nov 3, 2009

i have to compare two financial reports from two different sources each and it should derive the same result.

I am still working on it and only focus on Column C & D. However, i have come across some problems and hope that these milestones can be cleared so that i can move on to another step to achieve my goal.

So, my current problem is

1) #N/A (eg Cell D9 & D32) will be resulted when hlookup function is used. Since the workbook 'CB_Consol_2009' does not have the same name appear, how can i make the answer become '-' instead of #N/A when hlookup function is being used?

2) Double counting. The same name appears more than one time (eg CURRENT ACCOUNTS-RET_ (01#0505_01) and as such, its figures also calculating more than once. Is there a way to calculate the figure only once so that i come out with the correct grand total?

As my objective is to compare two financial reports results (which should be the same), this is the table i can think of. If anyone here have any constructive comment on my current format.

View 12 Replies


Compare Two Reports

Jul 2, 2007

I have an Excel Spreadsheet WorkSheet Analysis Work there are two reports side by side. A) Source of Truth & B) Analysis Studio.
What i'm trying to do is find out if Analysis Studio Matches off with the Source of Truth.
The columns in Analysis Studio being Column M & Column N have to match the Source of Truth Columns E "Cil account" & Column I "Code Name". Now the problem is that the names in Analysis studio Column M Example Cash is Different to the Source of Truth Coloumn I Example Cash Assets. This is what I would match as Yes being the Same as it is. The other Problem is Analysis Studio Column N has the Account & Name in one cell whilst the Source of Truth has both these in separate Columns. Im happy if it can just match off Column E being the account to the Analysis Studio Column N. Ideally would love the results to fall either go in Column O being YES it matches or Column P it does not match the Source of Truth.

View 5 Replies View Related

Summary / Reports (Multiple Columns Calculation Into Different Reports)

Feb 17, 2014

I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.

The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".

The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".

The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".

write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.

View 4 Replies View Related

2 Different Data Sources?

Mar 25, 2014

i have a excel file that comes out of one system that i want to then use as a data source to combine with details out of a SQL server.

I need to be able to take the excel file and then in MS Query lookup from SQL to return fields based on the excel table.

I can see how you can create a data source for the excel file but i cant get a second data source added.

I need to do it in Excel rather than Access as this needs to be given to other users to use who dont have MS Access?

missing how to do it?

View 1 Replies View Related

Pivot Table With Two Data Sources?

Aug 7, 2014

I'm creating report from a pivot table that has two data sources from two different work sheets. I noticed that you can use two data sources if you use the pivot table wizard which I tried but it can't combine the data. So I tried power pivot the same thing happened there - I will have all the column names but it will only show values from one report and if it shows values from other reports it will show the same value in all rows.

So I realized I need to create relationships which I did with a Concatenate of the dates. But still no luck.

View 2 Replies View Related

This Workbook Contains Links To Other Data Sources

Jan 23, 2014

My files always says this "This workbook contains links to other data sources" can I turn this off?

View 1 Replies View Related

How To Evaluate Rankings From Multiple Sources

Feb 17, 2013

Suppose I have a table with a collection of "Top Ten" rankings for something. For example, suppose I have rankings from five "sources" for the Top Ten Ethnic Cooking. For simplicity, I'll show the top 5 here:

Source A
Source B
Source C


I'd like a way to calculate some sort of average ranking. I have two problems: (1) I don't know what the formula should be and (2) I don't know how to implement it in Excel.

View 8 Replies View Related

Sharing A Workbook Dependent On Sources

Aug 12, 2007

I have a workbook which relies on another "source" workbook for getting some data. It then does some processing on the data and displays some results.

I want to share the workbook with the results with others, but I don't want to give them the "source" workbook.

View 9 Replies View Related

List External Queries & Sources

Nov 12, 2006

Can anyone explain how to name a chart? I've been accessing charts in my VBA scripts using chart names, e.g. "Chart 4". I've been determining the chart name by recording a macro and selecting the charts. But, is there a way to either:

1- change the name of a chart (as you would name a pivot table)
2- determine the name without having to select the chart in a recorded macro
3- reference multiple charts from the same worksheet without using their names

View 4 Replies View Related

How To Get Calculation In One Cell From Sources In A Number Of Different Cells

Jan 17, 2013

Please see the attached.

I have put together a sort of hire chart for a charity that hires out scooters/wheelchairs.

All the formulas were done and then they said that they sometimes hire out extra equipment with the chairs as well.

I have created an 'extras' column but I'm having nightmares trying to integrate it into the current formula.

I have just changed the first 3 rows to experiment with.

There is a basic cost of £2 per week or part of a week, that price is regulated from the master page.

Column P calculates the rate from the weeks/days and uses the master page as the source of costing.

I have created a column 'C' for the extras and tried to integrate it into the already complicated formula.

Unfortunately, although it seems to work, it won't work if the cost is lower than £9. At that point, it doesn't matter what I do in column C, it just ignores column C

When it gets to £12 it seems to take notice of column C, very strange.

Wheelchair Hire-Sample New (1)(1) (5).xlsm‎

View 4 Replies View Related

Make Three Tables Using Three Different Data Sources In Sheet?

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

Interface Design: Drop Down List Sources

Jun 4, 2006

I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.

I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.

I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).

So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).

I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.

View 6 Replies View Related

Get Last Date Of Financial Year

Aug 30, 2009

Financial Year ended on 31st March. If date is 18th August 2008, Then last date for financial year is 31st March 2009. IF date is 15th March 2009, Then also last date for financial year is 31st March 2009. I want formula / function to get last date of financial yera.

View 2 Replies View Related

Financial Calculator Functions

Sep 30, 2007

I would like a user friendly form where I could enter

50 15% discount and it would add 50% plus VAT and produce POR & POC which would adjust if the suggested price was changed.

View 14 Replies View Related

Calendar To Financial Year

Jun 14, 2013

I have a report at the moment which shows me the calendar year that an action was carried out in, what I would also like to show is which of our financial years (they run April to March) the action was carried out in.

View 7 Replies View Related

Financial Year Function

May 31, 2009

I have created a monster formula that extracts the Australian financial year (FYR) from a date string and would like to convert the formula to a nice tidy function.

the formula works on a date string with the following format: yyyy-mm-dd (2008-12-15)
for example the formula result for 2008-12-15 is 08/09 (yy/yy)

=IF(OR(LEFT(ExDate,1)+13,MID(ExDate,5,1)"-",MID(ExDate,8,1)"-",LEN(ExDate)10),"Change the expiry date format to yyyy-mm-dd",

View 9 Replies View Related

VBA Date To Financial Year

Jan 11, 2010

I'm looking for a VBA code which will review the date in column C, which is in the format DD/MM/YYYY, and convert it to a financial year in column D.

So... given a financial year April to March what i think i'm looking for is...

If the month is 04 to 12 then column D should display = Current Year / Current Year + 1

If the month is 01 to 03 then column D should display = Current Year - 1 / Current Year

I need column D to be in the format XX/XX ie 10/11 for the period April 2010 to March 2011. I need the code to work from row 2 to the last used row based on data entered in column E.

View 9 Replies View Related

Compiling Info From Different Sources To Make A Mailing List

Aug 22, 2013

I do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.

View 2 Replies View Related

Arrange Weekly Report By Getting Random Data From Other Sources

Jan 16, 2014

I am facing a issue with arranging a data in excel as weekly reports by getting a random input in the form as mentioned in the attachment

Needed like below

Name 1-Jan 2-Jan 3-Jan 4- Jan...........
Ram 0 L 0
Ravi 0 0 0
rohan 0 0 0
sheel 0 0 0

Inputs are like below format

name 2-Jan 8-Jan
Ram L 0
Ravi 0 0
rohan 0 L
sheel 0 L

Attached File : formal needed.xlsx‎

View 2 Replies View Related

Today Or Now Functions In Queries To External Data Sources?

Dec 2, 2009

Is it possible to use a today or now function in an sql query to an external datasource in excel 2007? I have an excel 2007 spreadsheet, that I have linked to our financial database (MySql) using the database wizard. I can get the tables to display in excel no problem. One table shows how much staff have billed in each invoice period. I would like to create a query in excel, so that when this table displays in excel it shows how much each staff member has billed for the current invoice period, as opposed to all of them.

We weren't sure how to do this, so we decided to do a query to pull out the current invoice period, then we could combine this pivot table with the one for the amount billed table to give a table showing the amount each staff member has billed in the current invoice period (do this bit in excel).

The problem is we can't seem to make the query to pull out the current invoice period work. We have tried the query below, and all the variations of it that we can think of

SELECT invoice_period FROM tbl_invoice_periods WHERE STR_TO_DATE('" & NOW() & "', '%d/%m/%Y') BETWEEN start_date AND end_date;

We have also tried referencing a cell which shows the current date, but we cannot get this to work either (we may have made a mistake in the query here, we are not entirely sure how to reference an individual cell). The only way we have been able to get it to work is if you enter the current date directly into the query, but obviously this means it's no longer automated, which isn't a solution.

View 3 Replies View Related

Financial Data - Graph Not Aligning

Jan 18, 2014

I work on graphics which show financial data. The base is day data together with calculated added values the graphic worked and showed good pictures.

But now I encountered a problem with the graph - related to not listed days, points are "generated" which do not be in line with the rest of the data !?


View 1 Replies View Related

Formula To Sum By Month And Financial Year

Jun 28, 2014

I have a spreadsheet that consists of date, product number with amount received against each date.

What I need are formulas that will automatically provide me with:

1. The sub-total monthly income for each product against each financial year.

2. Total income for each product by financial year.

3. Total income for each financial year.

View 11 Replies View Related

Lookups For Financial Statement Formatting

Dec 13, 2012

I have a tab that has rows with line items from a financial report (the items start with unique identifier numbers) and the tab also has historical monthly data for each of these items. I now have a new tab with the same line items but they are now in a different order. Is there a formula that I can use to copy all of the data from the old tab into the corresponding monthly line item in the new tab? Nothing changes except for the order of the rows.

Also, at the top of the old tab, there are totals that grab from each of the rows below. If I copy these formulas into the new tab, I think that they will reference the incorrect rows (as the order has changed) Is there any way to transfer these formulas and keep the linkage intact?

View 2 Replies View Related

Calculate Financial Year From Date

Nov 9, 2006

How do I calculate/display the financial year for a given date? For instance if my source cell has 25/11/2006 how do I get the result cell to show 2006/07 (or 06/07, 2006/2007, etc).

I've tried a Text() function but the best 'format_text' I could come up with was "yyyy/yyyy+1", which not only doesn't work, but would be wrong if the source date was in the second half of the financial year.

View 5 Replies View Related

Financial Modeling Spreadsheet Standards

Mar 14, 2008

In my efforts to improve worksheet readability across the board at my work I am researching spreadsheet standards. I have found the Spreadsheet standards review board that has a massive document containing valuable standards to adhere to for financial modeling. Since I learn best by example, I am wondering what standards any of you guys are using and if possible post a sample spreadsheet containing those standards.

View 3 Replies View Related

Listbox To Select (open/close) Files From Linked Sources

May 22, 2006

The main file was linked to other files. Instead of using the "edit links" dialogue box provided by Excel, I wish to use a list box. This list box will show all the files that the main file is linked to.

The user will have the the option to single select or multiple select the files.
Upon clicking "ok" it will "open and close" the selected files, thus updating the main file. (it should also remove the update links dialogue box of the source files if they had it, it will be a hassle for the user to click on the "don't update" for every linked file that is open)

I know the update links in Excel is able to do it but the links sometimes will result in errors, beside it doesn't offer multiple selections.

View 5 Replies View Related

Use Today () To Show What Financial Month This Relates Too.?

Nov 19, 2009

If today = any date in November, how can I display in a seperate cell that that is month 8 of an April - Mar Financial year.

A4 =Today() 18/11/09

a5 = can this display as 8 and then 9 when a4 becomes a december date?

View 3 Replies View Related

Count Period Based On Financial Month

Aug 21, 2012

Excel formula? What I would like to get is a period number for financially month - not sure if it is possible

For example: 29, 30 & 31 of July suppose to be period 8.

View 3 Replies View Related

Create 26 Folders (depends On Financial Year)

Apr 16, 2009

I want to create 26 - 27 folders (depends on financial year)

Basically in the current folder I want the folders to be created in the following format FE 22-04-09 for each fortnight in the financial year.

View 9 Replies View Related

Macro To Reduce Lengthy Financial Data...?

Jul 5, 2006

I'm researching a load of financial data downloaded for me from Bloomberg. It lists daily prices of 15 commodities for nearly 20 years in some cases! Its far too much data to get anything worthwhile out of, so I'd like to know if any of you guys have used or know what the VBA is to write a macro that will reduce all the data from daily to monthly. (ie. I want a macro that will allow me to quickly delete all data corresponding to dates which are NOT the end of the month).

View 5 Replies View Related

Copyrights 2005-15, All rights reserved