How to get data to follow through to seperate sheet?

[joelyoung94]

I have a large database of customers with their contact details and address etc.
With the database the main thing we analyse is source of the customer and sales person it was given to.
I have separate sheets of which narrow the database down by source. Such as telephone lead, website lead etc.
At the moment i am currently entering the data into the main database sheet and then have to enter again on the lead source sheet.

Is there any formula for a sheet for example website sheet in which it will look up a column in the main database (lead source column) and pull everything single row of customer details that have came from the website.

I would ideally like this to just continuously do this every time i enter data into the main database it automatically gets added to its separate sheet.

Can anyone help please?

[mikeTRON]

I suggest you post a sample workbook with sample data and mock up a proposed solution and we can attempt to help.

[joelyoung94]

Hi
I have attached a sample of what our database looks like, hopefully this will help.
Just basically would like some form of formula so each source sheet would automatically add all the customer details into that individual sheet when it is added to the main database.
When a status of a customer changes I want to be able to change the status on main database but then that also automatically feeds through.

Im quite good with formulas but this is far beyond me and still cannot find a way. Typing this in each time for 10-15 customers a time.

Regards
Joel

[mikeTRON]

Are you familiar with pivot tables? I am still not completely sure what you are trying to accomplish but give this a look.

AutoReports with Pivots.xlsx

Basically I wrapped a Pivot Table around columns A through M in the DATA tab then recreated what you tried to do with formulas with a pivot table. I included a filter on each of the tabs to only bring in those particular sources. Then to update, after you have added or removed the data in the DATA tab, you just click in any of the pivot tables, then click options -> refresh all. Boom. Done.

[FDibbins]

You could try this formula approach (does not need to be updated like a PT would, if new data is entered)

1. Put this in Database N2 and copy down (you can hide this if you want)...
=B2&COUNTIF($B$2:B2,B2)

2. In N1 on each sheet, copy this (to pull in the sheet name)
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

3. Put this in each sheet in A2, copied down and across...
=IFERROR(INDEX(Database!$A:$M,MATCH($N$1&ROW(A1),Database!$N:$N,0),MATCH(A$1,Database!$A$1:$M$1,0)),"")

[mikeTRON]

FDibbins has a clever solution. At the end of the day though, I typically pass a tool or workbook off to another person and they get confused too quickly with complex formulas and such. So I prefer a very simple pivot table approach where thy can click a button I make and they all refresh.

[FDibbins]

Thanks for the feedback Mike, PT's definitely have their place, and a macro button to update is a great idea. Mostly, I make WB's for others too, so the KISS principle is right up there for me too

[mikeTRON]

Yeah I have done some complex stuff (although not compared to what you do) and it just ends up confusing people so they ALWAYS come back to me for help updating or editing one simple thing, so lately I have been trying to keep all the logic and work within excel and in a very easy to follow logic stream. It tends to help, but they still come back asking me for help I suppose LoL.

[FDibbins]

It's been my experience that - if you make it, you own it lol

[joelyoung94]

Hi the database now looks like this.

Ive been trying to alter the original formula you gave me but cant for the life of me get it to work.

The following through will be starting at row 1054 on the main database. Are you able to further alter the code to just for the new layour and amount of columns.

I think i cant get it to work because on the database im trying to implement at row 1254 instead of row 1. Do i need to change anything for this?

Thank you for your response
Joel