Population of worksheets from one primary sheet. Famous Last Words.

[SoullessToaster]

Working on an accounting spreadsheet, and i have the boss breathing down my neck to get this one finished, however i need some help with a formula.

I have got a worksheet called "I&E" (Income and expenditure) which lists all financials coming into and out of the company and charged to the company charge card.

I have created some new sheets in which i want the same information but filtered in such a way as to isolate different types of payment methods. For example, a sheet for JUST mastercard payments, a sheet for bank account payments, a sheet for cash payments in etc.

I know i can just use filtering to influence the main sheet, but i don't want to do that. I have different formulas that need to be carried out on the different sheets, so therefore need the other worksheets.

The formula that i need, for the bank account one for example, i thought should look something along the lines of

=IF'i&e'!$E:$E"Bank Transfer"OR"Direct Debit"OR"Cheque"OR"Cash Withdrawal"

Then i want it to populate column A with the contents of column A from I&E if column E matches the above criteria.
Same for column B
C
D
E
F
and
G


Hopefully you guys understand what i'm after, i'm really struggling here.
When i started this spreadsheet i though "How hard could it be?"

Famous Last Words

 

[Zakkaroo]

Hi there!

This is a little long-winded ... perhaps too much so to describe it here, instead, I've uploaded what I've done to Google Docs and I'm hoping you can use it to work out what I've done.

The fundamental principle is this:

Give each 'payment type' a category - e.g. Bank Transfer, Direct Debit, Cheque and Cash Withdrawal all have the Category of 'Bank Account'
In your main data sheet, insert two colums - use one of these to lookup the category and the 2nd to give each row a unique ID that we can look up later
Create your separate sheets (e.g. Bank Account) and lookup those unique ID's that we created.

https://drive.google.com/file/d/0B_56uhr3qFmva0s2VXozSG0xSWM/view?usp=sharing

I hope this helps, but if it's not quite what you meant, let me know.

 

[SoullessToaster]

Hi there!

This is a little long-winded ... perhaps too much so to describe it here, instead, I've uploaded what I've done to Google Docs and I'm hoping you can use it to work out what I've done.

The fundamental principle is this:

Give each 'payment type' a category - e.g. Bank Transfer, Direct Debit, Cheque and Cash Withdrawal all have the Category of 'Bank Account'
In your main data sheet, insert two colums - use one of these to lookup the category and the 2nd to give each row a unique ID that we can look up later
Create your separate sheets (e.g. Bank Account) and lookup those unique ID's that we created.

https://drive.google.com/file/d/0B_56uhr3qFmva0s2VXozSG0xSWM/view?usp=sharing

I hope this helps, but if it's not quite what you meant, let me know.

That sounds like it could work well, it looks like it may well work. However i would need help implementing it, as i am self taught when it comes to excel, so i wouldn't know how to format this correctly.

 

[Zakkaroo]

Ok, no problem, did you check out the link I attached? It should let you download that workbook, which is set up and working ... hopefully you can adapt that sheet to your needs, I will run through below what each sheet is and what the formulas refer to, but it's too complicated to describe without referring to a spreadsheet. You WILL need to look at the example I uploaded.

1) Payment Types Worksheet
There is a worksheet called 'Payment Types' it contains 2 columns, Column A contains some example payment types (I used some of the ones you mentioned and I made up a couple, e.g. Bank Transfer, Cheque etc.). Column B contains a category that you want to put those payment types into - if 3 payment types are in the same category, then they'll all appear on the separate payment type sheets that we create later.

So, your first step is to insert a blank sheet in your own workbook and fill in all of these details for all of the payment types you have.

2) Original Data Worksheet (The sheet with all your data on it)
On this sheet you need to insert two columns at the far left of the screen (i.e. the 2 new columns will be Col A and Col B)
Starting with Column B - this is where you will lookup the payment type and return the category. So, if your payment type is now in Column G, you need to vlookup Gx on the sheet where you just wrote out all of your payment types and categories, in order to return the correct category.
This should leave you with a Col B that contains categories, like Bank Account, Mastercard etc.
Check my sheet for an example formula - I assume you already know how VLOOKUPS work, and can adapt the one I've done?

Col A is where you will create a unique ID for each row - what you will end up with, is the Category from Col B, followed by a number - so for example, if the first Mastercard appears in Row 238, then A238 will be Mastercard_1 etc.

That's it for the sheet with all your data on

3) Mastercard, Bank Account etc. worksheets (the sheets where you want to show ONLY the data for each category)
The way i've set this up is that you can create ONE of these sheets as a template and then copy the whole sheet ... when you change the value in cell A1, it will lookup a different category of data.

This sheet needs to be set up as follows:

Col A
A1 contains the name of the category
A2 Blank / whatever
A3 onwards is just a list of numbers from 1 to .... whatever you want

Columns B onwards:

Row 2 should contain the exact same headings as your original data sheet (excluding the new Col A and B we inserted)
Row 1 is used in the VLOOKUP. If you're familiar with VLOOKUPS, it's the col_index_num. - e.g. it represents the part in bold =VLOOKUP(A1,PLACE_TO_SEARCH,WHICH_COLUMN_TO_RETURN,EXACT_MATCH_OR_NOT)
If you're unfamiliar with vlookups, what it means is this ... if the value you are searching for is in Column A and the value you want to return is in Column D - then the number here should be 4 because Col A = 1, B=2, C=3 D=4
If the value you're searching for is in Col T and the value you want to bring back is in Col V then the number here should be 3. T=1, U=2, V=3
etc.

Row 3 is where you start your VLOOKUPS - basically you're going to be looking up a combination of whatever is in A1 and the number in A3, A4 etc.
So for Row 3, if A1 = Mastercard, you'll be doing a lookup of Mastercard_1 and you'll be searching for this in your original data and then returning the 3rd, 4th, 5th columns etc.

I hope that makes sense ... my solution is not as simple as "Here's a formula, copy / paste and away you go" - you will need to download / view the spreadsheet I posted, work out what everything is doing and adapt to your situation.

An alternative is that you upload a sheet that is set up exactly the way you want (any sensitive data removed) and I can then set it up for you and upload it.

 

[SoullessToaster]

You have been an absolute saviour. Not only have you solved my problem, you have taught me how to fix things like this in the future as well.
I have managed to get the sheet working through your instruction and guidance. Thank you so so much.

 

[Zakkaroo]

I'm really glad it's worked out ... there might be more eloquent solutions around, but the one we've done is pretty sturdy and you can easily add more categories / more payment types etc.

HTH