Is it possible to make an entire table based on values of another group of cells?

[Budman361530]

I have been searching this and not even sure if it is possible.  I have a table of 25 employees.  I would like to duplicate the same table but omitting empty ones on another part of the sheet assuming data is entered in a specific group of cells.  Example below…

Let’s say I enter data in Employee 5, 7, and 8 where the red circles are.  I would like to have it auto populate table 5, 7, and 8 only on another part of the page.    

Original table .... 

Proposed table.... 

 

 

[#2]

I'd like to say yes it is possible. but would like to know more to define how. first, what version of excel are you using (very important to know what technique/functions are available for you)? next, need to understand this better. do you want the sheet to automatically detect if you type something into a previously unused employee in table A and automatically add that employee into table B? or just as you add data into certain employees that information is automatically updated in table B? The latter can be done using 'paste link' or using simple formulas that reference the original cells (if you have excel 365 a single formula can reference the whole table array). if it is the former, it can be done but more complicated and how depends on if you have excel 365. Alternatively if may be possible/better to see if there is a power pivot solution.

[#3]

@mtarler 

Hello.  Thank you for the response.  Yes, I am working with 365.  I am trying to get my program to auto generate table B, whenever you add any data into table A.   Example… Let’s say employee 5 worked 8 hrs Monday.  Table B would appear automatically on another part of the sheet.   Then employee 6 worked a full week.  Another table would appear automatically under the first table B… etc. etc….  if you had 19 of the 25 employees table B would show 19 employees.  Omitting the six that were not on that job.   As I am typing this, I am thinking I could even do this as a Macro that could run after I am finished updating table A.  We use table A for payroll tracking.  What I am trying to do, is to make basically an invoice reporting program that will show who worked on what job, what days, and what hours each day without having to retype each guy, and their times.  

[#4 · Accepted solution]

@Budman361530 so yes of course you could use VBA but there are trade-offs.

in the attached I used a helper column to decide which employees to include and then a simple FILTER to show the result and 1 more trick was to use a custom number format to hide all the zeros.

Is this something close to what you want?

[#5]

@mtarler Sorry for the slow response... YES!  Your workbook appears to be exactly what I am looking for!  I need to play with it, then try to understand the Voodoo Formulas that make this happen....lol... 

[#6]

@Budman361530 

Why not automate the invoicing instead and skip the automatic table, where you enter the employee number or a translation thereof and the invoice will automatically populate the Line item based on the week number on the date of the invoice?

[#7]

@Yea_So 

Got side tracked on another project, and coming back to this.  I don't think your auto invoicing would work for my application, but can you show me an example of what you are talking about?  As for the formula you helped me with, it works well.  However, how can I get rid of "#N/A in unused fields?  I tried to use "If", Xlookup, Iferror... noting seems to work correctly.  

[#8]

@Budman361530 

i don't remember helping you with the formula, it was Mr Tarler who did help you

[#9]

I looked at the sheet I attached it has 2 parts: a helper column that returns true for all rows of an employee's table if there is data in their table, and then a simple FILTER() to return any row that was marked true. I probably could merge it all into 1 formula but it is sometimes nice to see what and how things are working (or not). That said, your picture doesn't make sense unless you have #N/A in columns A:I for some reason or you changed things around.