Link certain data from one tab to another based on condition

[Horry131]

Hello, 

I have one sheet and on the first tab I have the main data of leads. This data is in order of thousands and I want to pull the information of a particular lead in another tab only if for example in one cell I put the word hot or warm (I use a dropdown list for this). In the other sheet it will be approximately 20% of them, just the ones who are potentials for my business. How to create a formula to put them in order in the next available empty row based on the condition that I selected hot or warm in a particular cell in the main sheet.

thank you

[#2]

@Horry131 

A formula cannot put the data elsewhere; you would need VBA or Office Script to perform that.  But a partial solution using a formula is possible.  See the attached workbook.

[#3]

@SnowMan55 Thank you. 

I will try to be more specific and I have attached a picture of my spreadsheet. Basically if in column A on the main sheet it is selected Hot or Warm the same information for all the columns to be transferred for that lead in the next tab (from Status to the one called Cursanti). So, for example maybe the information for person from row 5 on the main one (Status) to be copied to the second tab (Cursanti) in the 2nd row. And than perhaps the next person could be in row 13 and needs to be copied in the next empty row, row 2 on the next tab. 

[#4 · Accepted solution]

@Horry131 

The structure shown in your picture makes no conceptual change to my prior reply, but details change.

Change the value in cell B3 on the Cursanti worksheet in the new attached workbook to see the results.  (And be sure to read the final note on the _Info worksheet.)

From your picture it appears that you are using Google Sheets rather than Microsoft Excel. No guarantee is given that the partial solution will work with Google Sheets also.

[#5]

Thank you.