Automatically Create List/Table from data that changes often

[exceldummie675]

Hello All Excelions!

Here my problem: (cause I'm a TOTAL newbie/excel idiot)

I have a client list/table that gets new data each day.
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria... The All sheet has different columns... Name, ID, Reg no, Tax, VAT etc...

On the (ID) sheet I need all the clients that have ID numbers on ALL sheet, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.

Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.

I want to add columns on the separate sheets like on ID sheet I want to add month columns. And tick the months off when the work is completed. So the client with ID num should automatically be add at bottom of ID sheet of if i sort main list alphabetically, it should insert a new row in the ID sheet.

Is there a way to do this using formulas or do I need code to do this?

PS.I'd prefer to use formulas.
Formulas I might be able to figure out/understand and edit if necessary. But with code my brain will go into a continuing loop and burn out!

PLEASE HELP!!!

 

[Akashwani]

Hi and welcome to MrExcel.

Without some sample data and your expected results it is going to be difficult for anyone to provide you with a solution!

You may want to take a look here....

excelisfun -- Excel How To Videos - YouTube

Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Through - YouTube

Search/Lookup | Get Digital Help

Table | Get Digital Help

I hope this helps.

Ak

 

[exceldummie675]

Hello
Sorry... here is link to my file...

http://sdrv.ms/18FWdh8

 

[Akashwani]

Hi,

On sheet ID in cell A3 put this formula...

=IFERROR(INDEX(Table_All['#],SMALL(IF(Table_All[ID]<>"",ROW(Table_All['#])),ROWS(A$2:A2))-ROW(ALL!$A$2)+1),"")

This needs to be entered using ctrl shift enter NOT just enter, it can then be copied down as far as you require.

On Sheet ID in Cell B3 put this formula...

=IF(A3="","",VLOOKUP(A3,Table_All[#All],COLUMN(B1),0))

And copy down as far as you did the first formula.

Excel Workbook
	A	B
1
2	#	NAME
3	4	Andrae Christo Engelbrecht
4	5	Andraetech Engineering Draughting and Surveying CC
5	7	Antony Clive Geldenhuys
6	8	Bernabe Jordaan
7	9	Big and Small Distributors CC
8	17	Charmaine Sanet Meiring
9	18	Clifton Dunes Inestments 281 (Pty) Ltd
10	22	Daniel Coetzee
ID

********

On sheet Reg No in cell A2 put this formula...

=IFERROR(INDEX(Table_All['#],SMALL(IF(Table_All[REG NO]<>"",ROW(Table_All['#])),ROWS(A$2:A2))-ROW(ALL!$A$2)+1),"")

This needs to be entered using ctrl shift enter NOT just enter, it can then be copied down as far as you require.

in B2 put this formula...

=IF(A3="","",VLOOKUP(A3,Table_All[#All],COLUMN(B1),0))

Excel Workbook
	A	B
1	#	NAME
2	2	Adriaan E Botha (Own eFiling profile)
3	3	Anita Strauss
4	6	Biz Afrika 604 (Pty) Ltd
5	10	Black Gold Mining Resources CC
6	11	Bonolo Labour Solutions CC
7	12	Boy Butter (Pty) Ltd
8	13	Calicom Trading 218 (Pty) Ltd (Dormant)
9	14	Carlos Arendse
10	15	Colonial & Fine Furniture (Dormant)
Reg No

You will need to replace the BOLD parts of the above array formula(s) for each of your sheets, eg... Tax, VAT

I hope this helps and if you have any problems, check out the links previously provided.

Good luck.

Ak

 

[exceldummie675]

Thanks for the HELP!!!

I made some changes because it gave me wrong values but it work like a dream now! I had removed the (+1) at the end and it worked. Not sure why but it did. And I also did not use the Vlookup in column A. I only change the values in the column B formula to lookup the actual Name and not the number in column A. It worked! Now I can add formula to add number of entries on each sheet on column A.
Thanks you for this!!!

My next problem is that if I add new client data on the main sheet, and sort it, it adds the new details on the separate sheets but the sorting on those sheets is only done in column B (the name of client). I need it to add l row so that the data I capture on each sheet don't get mixed up...!?!? Only column B is shifted down so data in column C, D, E ... is opposite the wrong name...

If i could explain more in detail: If add "BATMAN" with ID number and sort it on the main sheet... it gets added to the ID sheet alphabetically. Batman is added after Antony on the ID sheet, but the data I entered for Bernabe show in the BATMAN row.

I only want to do a "sort data" on the main sheet and all other sheets should be sorted automatically. But the data that's entered on the separate sheet should always stay opposite the correct name...

Hope this make sense.

PS. Once again thanks for the previous formula! You are a excel genius!

 

[Akashwani]

Hi,

I'm not sure why it gave you the values or why you had to remove the (+1), as it worked correctly for me!

What formula are you using in cells C, D, E etc?

Ak

 

[Akashwani]

Hi,

I'm not sure why it gave you the values or why you had to remove the (+1), as it worked correctly for me!

What formula are you using in cells C, D, E etc?

Ak

 

[exceldummie675]

HI. There is no formulas in these cells... only conditianal color formatting. The columns next to the name column is Months of year. I'm only going to put "x" in cell to mark it as complete. So on the ID sheet the names with ID numbers is displayed. Next to each name is columns for months of the year. I would like to just add "x" in each cell as work is completed. It is for checking what is done and what not on each client.

Now when new client is added on main sheet and sorted... only the B column on ID sheet is sorted/shift down and the work complete tick box (x) is mixed up. I need for it to insert a row when main sheet is sorted.

 

[Akashwani]

Hi,

I have no idea how you can achieve what you require as the "X" is static/locked due to it being entered manually.
You may require a VBA solution to get the result you desire and I'm sorry, but that isn't something I can help you with as I don't write or understand VBA.

Ak

 

[jeffreyweir]

Crossposted at:
Autamatically insert row when data is sorted on another sheet - Microsoft Community
http://www.mrexcel.com/forum/excel-...lly-create-list-table-data-changes-often.html
Autamatically insert row when data is sorted on another sheet


ExcelDummie675 -please read Excelguru Help Site - A message to forum cross posters