Self populate tables based on criteria of data in 'master' table

Hi,

This is my first question on the forum, I apologise if I'm not using the correct terminology or explaining myself properly - I'll do my best.

I work in Business Development, one of my duties is to prepare, submit and monitor the progress of tenders/proposals for new prospective clients

I have a 'master' table which lists each tender/proposal with some basic information on each row. One of the columns on the master table is labelled 'Stage' -This is a dropdown with options; 'PQQ', 'ITT', 'PRESENTATION'. I then have three other tables, all the same as the master table but labelled as; 'PQQ Stage' ITT Stage' and 'Presentation Stage'.

What I am trying to achieve is this; When I enter data onto the 'master' table, I want the other three tables ('PQQ Stage' ITT Stage' and 'Presentation Stage') to self populate depending on the criteria of the 'Stage' column. For example; any rows with PQQ entered into the 'Stage' column of the 'master' table to automatically copy into 'PQQ Stage' table..... any rows with ITT entered into the 'Stage' column of the 'master table' to automatically copy into 'ITT Stage' table.

The image below shows basically what I am trying to achieve. I've also attached the Excel spreadsheet for you guys to look at and edit if need be.

Example2.png

This is only a very basic spreadsheet with basic tables - I only made this for the purpose of demonstrating to yourselves exactly what it is I require.


If any of you guys can help me it would be much appreciated. Many thanks, Ross

Try

In B16

=IFERROR(INDEX(B$3:B$11,SMALL(IF($E$3:$E$11="PQQ",ROW($B$3:$B$11)-ROW($B$3)+1,""),ROWS($B$3:B3))),"")

Enter with CtrL+Shift+Enter

Copy across and down

Sane formula in other tables but change "PQQ" to "ITT" and "Presentation"

Originally Posted by JohnTopley

Try

In B16

=IFERROR(INDEX(B$3:B$11,SMALL(IF($E$3:$E$11="PQQ",ROW($B$3:$B$11)-ROW($B$3)+1,""),ROWS($B$3:B3))),"")

Enter with CtrL+Shift+Enter

Copy across and down

Sane formula in other tables but change "PQQ" to "ITT" and "Presentation"

You Sir, are a Godsend! That works perfectly.

One last thing I just thought of - if you're able to help me please?

Is it possible for me to have each of the three tables (PQQ / ITT / PRESENTATION) on their own sheet, and still auto-populate dependent on the criteria of records in the 'master' table? For example, a sheet for PQQ, a sheet for ITT and another for Presentation.

Thank you so much for your help

Originally Posted by Olly

Or using a bit of VBA:

Please Login or Register  to view this content.

Thank you for your help Olly - although I don't think I have enough knowledge to start delving into VBA yet lol. Thanks again!

Originally Posted by JohnTopley

Assuming data in Sheet1 and your tables in other sheet(s)

=IFERROR(INDEX(Sheet1!B$3:B$11,SMALL(IF(Sheet1!$E$3:$E$11="PQQ",ROW(!$B$3:$B$11)-ROW(Sheet1!$B$3)+1,""),ROWS($B$3:B3))),"")

See Sheet2 of attached

Thanks again John - you're a genius.

Sorry to be a pain, but is there anyway of self populating only certain columns rather than duplicating an entire row.

For example; The 'master' table contains all colums- then the other tables are condensed versions of the master table.

Please see attachment - you'll see on sheet 2 what I mean when I say condensed versions of the master table.

Many thanks, Ross!

Company

=IFERROR(INDEX(Sheet1!B$3:B$11,SMALL(IF(Sheet1!$E$3:$E$11="PQQ",ROW($B$3:$B$11)-ROW(Sheet1!$B$3)+1,""),ROWS($B$3:B3))),"")

Amount

=IFERROR(INDEX(Sheet1!D$3:D$11,SMALL(IF(Sheet1!$E$3:$E$11="PQQ",ROW($B$3:$B$11)-ROW(Sheet1!$B$3)+1,""),ROWS($B$3:B3))),"")

Thanks so much for your help John - much appreciated!

Thank you for the feedback and rep.

If your query has been addressed could you please mark the thread as solved ("Thread Tools" at top of first post)

Hi,

Alternatively you could do it using Pivot Tables.
Please see attached.

Regards

peterrc