Excel - Getting data from multiple Excel Files...

[Chapster5]

... and displaying them in 1 Excel file?

Ok so here's what I am after,

I create invoices for work and they are all in separate folders on the computer (Like, May, June, July...) I want a Main Excel file that basically adds up all the invoices I currently have from the totals and then relays that information to me, instead of having to go through each spreadsheet individually and noting down how much was paid on that invoice. The invoice totals are in the exact same location (E25 I think) on all the invoices.

Any clues?

 

[richy22]

Write some VBA code/macro in excel.

 

[The Boosh!]

Yup you need to use VBA for this.

 

[Tim.]

I know it doesn't directly answer your question but could you amalgamate each individual worksheet into a single workbook for the year, and have each month as a separate tab?

 

[Deeg]

I know it doesn't directly answer your question but could you amalgamate each individual worksheet into a single workbook for the year, and have each month as a separate tab?

I would do this, then have a summary page.

13 pages a year in 1 workbook is much better than 13 workbooks.

Looks infinitely better when presenting the information.

But you need VBA / Macro to do what you want.

 

[Chapster5]

Cheers guys how do I go about a workbook?

 

[Deeg]

Open a new workbook in Excel.

Name each tab Jan - Dec.

Paste in (be careful pasting in, and make sure you get formulas / data where needed).

Name a final tab summary. Link each month to a total in the corasponding month.

Job done.

 

[Chapster5]

Cheers Deeg I'll have a play tomorrow at work

 

[Chapster5]

Right so here's what I have so far ( A blank work book! LOL)

My invoices look like this... (sorry for the bad image hard to squeeze it in lol)

I32 - Is a total of the invoice "=SUM(I18:I30)" - Is the formula, I invoice on a weekly basis. Whats the SUM for totaling all the sheets in a work book (Say I32) into the Annual Summary Tab?

 

[The Boosh!]

Right so you could use VBA to open up each xls workbook in a directory and add a tab into a core workbook with the invoice on. So the macro would essentialy start from the top > open > copy tab > paste into core > Close > loop onto the next work book until the end. This isn't that difficult to write but if there are only say, 10-15 workbooks it's quicker to do it manually.

When you've got the invoices as tabs (i.e lots of invoices in 1 core workbook) you need a tab that says summary. To add the total invoices up you can use something called cubed formulas. For cubed formulas to work you need to name the tabs appropriately. So tab 1 would be Jan 2011, tab 2 Feb 2011 etc etc and you would keep going until you have got all the dates. Again you can use VBA to do this quicker (write a bit of code into the VBA that imports the tabs).

So once you're named up your formula on the summary sheet would be

=sum(Jan2011:Dec2011!i18:i30).

This will sum everything in the above range in all of the sheets. Look up cubed formulas on google for more info. If the VBA bit scares you then don't bother just do it manually.

I think thats what you're asking? Sorry if i've misunderstood.

 

[Chapster5]

Right so you could use VBA to open up each xls workbook in a directory and add a tab into a core workbook with the invoice on. So the macro would essentialy start from the top > open > copy tab > paste into core > Close > loop onto the next work book until the end. This isn't that difficult to write but if there are only say, 10-15 workbooks it's quicker to do it manually.

When you've got the invoices as tabs (i.e lots of invoices in 1 core workbook) you need a tab that says summary. To add the total invoices up you can use something called cubed formulas. For cubed formulas to work you need to name the tabs appropriately. So tab 1 would be Jan 2011, tab 2 Feb 2011 etc etc and you would keep going until you have got all the dates. Again you can use VBA to do this quicker (write a bit of code into the VBA that imports the tabs).

So once you're named up your formula on the summary sheet would be

=sum(Jan2011:Dec2011!i18:i30).

This will sum everything in the above range in all of the sheets. Look up cubed formulas on google for more info. If the VBA bit scares you then don't bother just do it manually.

I think thats what you're asking? Sorry if i've misunderstood.

Bang on Boosh,

VB Is not my department so I shall do it manually I shall give it a good go at dinner! Thanks mate I shall report back with how I get on

 

[The Boosh!]

If you send me your e-mail address I can send you a working example. That way you can bodge it to suit your needs.

Also having had a play you don't need to name all your sheets. You just need to have a start and an end sheet and anything inbetween those will get put into the total.

[LEFT][COLOR=#333333][I]=sum(start:end!i18:i30).

[/I][/COLOR][/LEFT]

 

[Chapster5]

You got mail Booshy baby! x

 

[Tim.]

If you send me your e-mail address I can send you a working example. That way you can bodge it to suit your needs.

Also having had a play you don't need to name all your sheets. You just need to have a start and an end sheet and anything inbetween those will get put into the total.

[LEFT][COLOR=#333333][I]=sum(start:end!i18:i30).

[/I][/COLOR][/LEFT]

Very useful tip

 

[Jamie]

Fair play boosh! Very nice of you.

 

[Chapster5]

Thanks for the help Boosh One last one as well so I know how much tax to pay,

How do I work the SUMIF statement to calculate the tax I have to pay for that year. Trouble is I have to earn 8700 (I think) before the threshold kicks in then I would pay tax on what I make weekly after that threshold. So I can have up to 8700 of tax free invoices then after that each invoice I make I have to pay 20%? Tax on it.

Think I may just be over complicating things to be honest.

 

[AK]

Tax isn't as easy as that, lol.

 

[The Boosh!]

I don't understand what you're asking.

You want to add 20% tax to any value that is >£8700?

So, if it was £10k you would want 20% tax on £1300?

 

[Chapster5]

Basically if the Annual Total is below 8700 then dont display any tax at all, if its over 8700 then start displaying tax on my invoices that make the total go over the 8700 value and display 20% of the invoice. If you see my Email to you how I layed out the Sheets in the work book this could maybe be achieved? If not it's not biggy I can always work the tax out

Or maybe a sum like, =SUM(Annual total - 8700 * 100 / 20) ?

So if its 8700 bang on then if you minus 8700 = 0 there for 0 /100 * 20 = 0 (so no tax is displayed)

Yet if it was say 9000 - 8700 = 300 /100 * 20 = 60 (so £60 tax )

 

[AK]

=IF(Annual total>8700,(Annual total-8700)*20%,0)

 

[AK]

Obviously put the exemption rate and tax rate into cells so it's easier to change for next year.

 

[Chapster5]

Thanks ak I think I have it all mapped out in my head just trial and error now