Linkage of Sheets

Welcome to the forum.

You cannot delete contents using a formula. I suspect you will need VBA for this, however to be sure we need to see a workbook.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

So I added an example. Basically if I put an X in Base 1 I need a new row to form in Base two with the name. And if I remove the X in Base one the row in Base 2 needs to be removed as well.

While Ali is most likely correct that VBA is needed to get the results you want, I felt that this might work.
1. A helper column (BG) is added to the Base 1 sheet and populated using: =SUMPRODUCT(--(E5:BF5<>""))
2. Column B on the Base 2 sheet is populated using: =IFERROR(INDEX('Base 1'!C$5:C$109,AGGREGATE(15,6,(ROW($A$5:$A$109)-ROW($A$4))/('Base 1'!$BG$5:$BG$109),ROWS($A$1:$A1))),"")
3. Column C on the Base 2 sheet is populated using: =IF(B5="","",INDEX('Base 1'!D$5:D$109,MATCH(B5,'Base 1'!C$5:C$109,0)))
Let us know if you have any questions.

In the attached file there are six helper columns, however the blue section may need to be expanded depending on how many columns (E:BF) could possibly receive an X, for a given row, at one time.
The formula for the blue section helper columns is: =IFERROR(VALUE(ROW()-4&AGGREGATE(15,6,COLUMN($E$1:$BF$1)/($E5:$BF5<>""),COLUMNS($A$1:A$1))),"")
The formula for the peach helper column is: =IFERROR(AGGREGATE(15,6,BG$5:BI$109/(BG$5:BI$109<>""),ROWS(A$1:A1)),"")
The formula for the green helper column is: =IF(BJ5="","",ROUNDDOWN(BJ5/10,0))
The formula for the purple helper column is: =IF(BJ5="","",MOD(BJ5,10))
The formula for the Representative column is: =IFERROR(INDEX('Base 1'!D$5:D$109,'Base 1'!BK5)&"/"&INDEX('Base 1'!A$4:BF$4,'Base 1'!BL5),"")
The formula for the ID column is: =IF(C5="","",'Base 1'!BK5)
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Deleted by User.

See if the following resolves the issue:
1. The range of the blue cells is extended to column BS (counting the highlighted columns in the table)
2. The formula* in BT is modified to read: =IFERROR(INDIRECT(TEXT(SMALL(IF((BH$5:BS$100<>""),ROW($5:$100)*100+COLUMN(BH:BS),10^10),ROWS($1:1)),"R0C00"),)&"","")
3. The formula in BU is modified to read: =IF(BT5="","",VALUE(LEFT(BT5,SEARCH("/",BT5)-1)))
4. The formula in BV is modified to read: =IF(BT5="","",VALUE(MID(BT5,SEARCH("/",BT5)+1,9)))
*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Thank you. Your help is much appreciated.

You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.