Build new table from existing table

[reaktorblue]

Hiyas,

I have a worksheet ("Plugins") that contains a table ("tblPlugins") with one of the columns ("Installed") having data validation that contains Yes/No. I'm trying to build a new table in a separate worksheet based on the condition that if the column ("Installed") has a value of Yes, that the data should exist in the new table.

Is there a way to do this?

 

[Peter_SSs]

See if this is what you want.

Sheet 'Plugins' with "tblPlugins"

Excel Workbook
	A	B	C
1	ID	Installed	Amount
2	ID1	No	1
3	ID2		2
4	ID3	Yes	3
5	ID4	Yes	4
6	ID5	Pending	5
7	ID6	Yes	6
Plugins

'Other' sheet with second table, "tblYes". Formula in A2 is copied across and down

Excel Workbook
	A	B	C
1	ID	Installed	Amount
2	))/(tblPlugins:]="Yes"),ROWS(A$2:A2))),"")]ID3	Yes	3
3	ID4	Yes	4
4	ID6	Yes	6
5
6
7
Other

 

[reaktorblue]

Thank you so much! I'm still running into a bit of an issue. Ideally, I was hoping for the output to be an actual table as opposed to a list or range but, I'm not sure that's possible. This solution does work for the most part but has some quirks but I think it's because I wasn't entirely clear on the data.

Thanks again, I really appreciate your help with this one!

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	URL	Dependencies	Description	Cost	Server1	Server2	Server3
2	Plugin 1	www.plugin1url.com	Test	This is a description	$ 10.00	Yes
3	Plugin 2	www.plugin2url.com	Test	So is this	$ 5.00
4	Plugin 3	www.plugin3url.com		This one too		Yes
5	Plugin 4	www.plugin4url.com	Test	Again, this one	$ 3.00
6	Plugin 5	www.plugin5url.com		Lastly, so is this		Yes
tblPlugins

Current Output - The problem I'm running into here is blank dependency entries appear as a 0. I'm trying to leave them as blank entries. Additionally, when an entry has no cost, I want to leave it as a blank entry as well.

Excel 2010
	A	B	C	D	E
1	Name	URL	Dependencies	Description	Cost
2	Plugin 1	www.plugin1url.com	Test	This is a description	$ 10.00
3	Plugin 3	www.plugin3url.com	0	This one too	$ -
4	Plugin 5	www.plugin5url.com	0	Lastly, so is this	$ -
Server1

Current Output

Excel 2010
	A	B	C	D	E
1	Name	URL	Dependencies	Description	Cost
2	Plugin 1	www.plugin1url.com	Test	This is a description	$ 10.00
3	Plugin 3	www.plugin3url.com		This one too
4	Plugin 5	www.plugin5url.com		Lastly, so is this
Desired Output

<table width="85%" cellpadding="2.5px" rules="all" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">tblPlugins[Name],AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">tblPlugins[Name]</font>)-ROW(<font color="Teal">tblPlugins[[#Headers],[Name]]</font>)</font>)/(<font color="Purple">tblPlugins[[Server1]:[Server1]]="Yes"</font>),ROWS(<font color="Purple">A$2:A2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Peter_SSs]

Ideally, I was hoping for the output to be an actual table as opposed to a list or range ...

Mine are tables, it is just that Excel jeanie does not show the standard banded table formatting. However, a formula cannot create a table. I created a blank table with ample rows on sheet Server1 below before populating the formulas.

A formula like I have provided that collects data from another table also will not create a hyperlink or copy the currency formatting from the 'Cost' column, you will need to do that formatting in the Server1 table yourself.

The problem I'm running into here is blank dependency entries appear as a 0. I'm trying to leave them as blank entries. Additionally, when an entry has no cost, I want to leave it as a blank entry as well.>

That requires a bit more work from the formula but can be achieved as shown below.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	URL	Dependencies	Description	Cost	Server1	Server2	Server3
2	Plugin 1	www.plugin1url.com	Test	This is a description	$10.00	Yes
3	Plugin 2	www.plugin2url.com	Test	So is this	$5.00
4	Plugin 3	www.plugin3url.com		This one too		Yes
5	Plugin 4	www.plugin4url.com	Test	Again, this one	$3.00
6	Plugin 5	www.plugin5url.com		Lastly, so is this		Yes
tblPlugins

Excel Workbook
	A	B	C	D	E
1	Name	URL	Dependencies	Description	Cost
2	))/(tblPlugins:]="Yes"),ROWS(A$2:A2)))="","",INDEX(tblPlugins,AGGREGATE(15,6,(ROW(tblPlugins)-ROW(tblPlugins,]))/(tblPlugins:]="Yes"),ROWS(A$2:A2)))),"")]Plugin 1	www.plugin1url.com	Test	This is a description	$10.00
3	Plugin 3	www.plugin3url.com		This one too
4	Plugin 5	www.plugin5url.com		Lastly, so is this
5
6
7
8
9
10
Server1