Automatically Create List/Table from data that changes often

exceldummie675

New Member
Joined
May 31, 2013
Messages
5
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!!!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
AB
1
2#NAME
34Andrae Christo Engelbrecht
45Andraetech Engineering Draughting and Surveying CC
57Antony Clive Geldenhuys
68Bernabe Jordaan
79Big and Small Distributors CC
817Charmaine Sanet Meiring
918Clifton Dunes Inestments 281 (Pty) Ltd
1022Daniel 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
AB
1#NAME
22Adriaan E Botha (Own eFiling profile)
33Anita Strauss
46Biz Afrika 604 (Pty) Ltd
510Black Gold Mining Resources CC
611Bonolo Labour Solutions CC
712Boy Butter (Pty) Ltd
813Calicom Trading 218 (Pty) Ltd (Dormant)
914Carlos Arendse
1015Colonial & 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

New Member
Joined
May 31, 2013
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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

Well-known Member
Joined
Mar 14, 2009
Messages
2,911

ADVERTISEMENT

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

New Member
Joined
May 31, 2013
Messages
5
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

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,952
Messages
5,525,851
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top