Hi,
I need to extract rows from a master spreadsheet based on the department name which is located in column A.
For example:
[TABLE="width: 1818"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]STOCK_CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]QTY_IN_STOCK[/TD]
[TD]ORDER_NUMBER[/TD]
[TD]ORDER_DATE[/TD]
[TD]NAME[/TD]
[TD]QTY_ORDER[/TD]
[TD]QTY_DELIVERED[/TD]
[TD]Outstanding[/TD]
[TD]DESPATCH_DATE[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A1.5MS/R152[/TD]
[TD]152mm x 1.5m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]107949[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]Naples Components Limited[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A1.5MS/R152[/TD]
[TD]152mm x 1.5m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]107936[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]WS Westin Ltd (account 1)[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]A3M2PLY102[/TD]
[TD]102mm x 3m Flexible Aluminium Hose[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]107940[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]North West Aluminium[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R102[/TD]
[TD]102mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]107961[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Vapourflow Ltd Stores[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R127[/TD]
[TD]127mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]107876[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD]Kair Ventilation Ltd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R127[/TD]
[TD]127mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]107961[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Vapourflow Ltd Stores[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]ALUM102X10[/TD]
[TD]102mm x 10m Flexible Aluminium[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]107813[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD]Worth Electrical Wholesalers Ltd[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]05/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]ALUM102X10[/TD]
[TD]102mm x 10m Flexible Aluminium[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]106813[/TD]
[TD="align: right"]29/11/2017[/TD]
[TD]Vortice Elettrosociali S.p.A £ A/C[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
Then I want a formula on another worksheet that copies each row that contains 'hoses' in the first column.
The table is live data attached to a MS query, so its refreshing each day.
I know I could use versions of copying etc, but I need the data to flow into the next sheet without manual intervention.
The department column is a manually entered column and I tried making it a table then filtering it by hoses, but when I refresh the table it looses its filters each time.
Please help! Really stuck with this one. I want something like the sum ifs formula, but I don't want any adding up of data etc.
Thank you!
I need to extract rows from a master spreadsheet based on the department name which is located in column A.
For example:
[TABLE="width: 1818"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]STOCK_CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]QTY_IN_STOCK[/TD]
[TD]ORDER_NUMBER[/TD]
[TD]ORDER_DATE[/TD]
[TD]NAME[/TD]
[TD]QTY_ORDER[/TD]
[TD]QTY_DELIVERED[/TD]
[TD]Outstanding[/TD]
[TD]DESPATCH_DATE[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A1.5MS/R152[/TD]
[TD]152mm x 1.5m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]107949[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]Naples Components Limited[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A1.5MS/R152[/TD]
[TD]152mm x 1.5m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]107936[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]WS Westin Ltd (account 1)[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]A3M2PLY102[/TD]
[TD]102mm x 3m Flexible Aluminium Hose[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]107940[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]North West Aluminium[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R102[/TD]
[TD]102mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]107961[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Vapourflow Ltd Stores[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R127[/TD]
[TD]127mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]107876[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD]Kair Ventilation Ltd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R127[/TD]
[TD]127mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]107961[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Vapourflow Ltd Stores[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]ALUM102X10[/TD]
[TD]102mm x 10m Flexible Aluminium[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]107813[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD]Worth Electrical Wholesalers Ltd[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]05/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]ALUM102X10[/TD]
[TD]102mm x 10m Flexible Aluminium[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]106813[/TD]
[TD="align: right"]29/11/2017[/TD]
[TD]Vortice Elettrosociali S.p.A £ A/C[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
Then I want a formula on another worksheet that copies each row that contains 'hoses' in the first column.
The table is live data attached to a MS query, so its refreshing each day.
I know I could use versions of copying etc, but I need the data to flow into the next sheet without manual intervention.
The department column is a manually entered column and I tried making it a table then filtering it by hoses, but when I refresh the table it looses its filters each time.
Please help! Really stuck with this one. I want something like the sum ifs formula, but I don't want any adding up of data etc.
Thank you!