Is there a way to populate a new sheet/table based on cell value?

Question

just registered since I cant find any answers to this on my searches alone.

So, at work I have this massive excel where I simplified keep track on bought and sold cars. I have a lot of columns but for this issue only the acquisition price and the selling price is needed.

So if the column acqusition price is set, its in stock, if both are set its sold and not in stock. So ive setup an formula that counts the acqusition price and sums the column only if the selling price is not set so that actually gives me the correct stock value. So far so good.

But the file gets really big and complicated to look at, and I would like a 2nd tab/sheet where it populates a new table and only copy rows from sheet 1 where acquisition price is set, but not selling price. Is it possible? I know i can do this manually by using filters and then copy the result to a new sheet, but then this has to be done all the time, or at least once a month. Ideally I would like this to happen automatically? And in this way Ill have a clean-looking table with only the cars actually in stock.

I have only googled for solutions for this but havend found one. Wanted to check in with you guys cause I know if its possible I can get the help here.

Answer 1

You should upload sample dataset and desired result so it is easier to understand your problem.

If I understand correctly simple FILTER would be helpful here. Convert your data to table so it auto expands when new row is added then filter that table with:

=FILTER(Table1,Table1[SELL PRICE:]="")

or

=VSTACK(Table1[#Headers],FILTER(Table1,Table1[SELL PRICE:]=""))

To get headers. Result:

Or don't convert to table, but use something like this:

=FILTER(A2:D1000&"",D2:D1000="")

Result: