Formula with LAMBDA not updating the master sheet

Question

Hi There,

We are using an excel file with 5 sheets or Tabs. 4 Tabs are source tabs where we input data and then these data will move to the master Tab where we do calculations, use formula.

One strange issue that we are facing is while doing initial data input it works all fine but if add any row or rows in any of the 4 source tabs, those rows will move to somewhere in the middle of the master tabs, and it wont append at the bottom.

As we are using many functions, may be we are unable to find out where the actual issue is.

Can someone guide how to start the troubleshooting or fine tune the function.

The function that we saw has possible issue from the Formula Auditing group in Formula Menu in excel.

The example of the command is follows:

=LET(v,

VSTACK(FILTER('src-AB' !A2:L9999&"",BYROW('src-AB' !A2:L9999,LAMBDA(row,SUM(--(row<>"")).0))*(' src-AB ' !M2:M9999="Y"),0),

       FILTER('src-CD' !A2:L9999&"",BYROW('src-CD' !A2:L9999,LAMBDA(row,SUM(--(row<>"")).0))*(' src-CD ' !M2:M9999="Y"),0), 

           FILTER(v,INDEX(v,,1)<>0)),

Any help would be great.

Thanks in advance.

Answer 1

Hi,Ms Query is an option.

Answer 2

Here’s the ready-to-use formula:

=LET(
 v,
   VSTACK(
     FILTER(
       'src-AB'!A2:L9999,
       ('src-AB'!M2:M9999="Y") *
       BYROW('src-AB'!A2:L9999, LAMBDA(row, SUM(--(row<>""))))
     ),
     FILTER(
       'src-CD'!A2:L9999,
       ('src-CD'!M2:M9999="Y") *
       BYROW('src-CD'!A2:L9999, LAMBDA(row, SUM(--(row<>""))))
     ),
     FILTER(
       'src-EF'!A2:L9999,
       ('src-EF'!M2:M9999="Y") *
       BYROW('src-EF'!A2:L9999, LAMBDA(row, SUM(--(row<>""))))
     ),
     FILTER(
       'src-GH'!A2:L9999,
       ('src-GH'!M2:M9999="Y") *
       BYROW('src-GH'!A2:L9999, LAMBDA(row, SUM(--(row<>""))))
     )
   ),
   SORT(FILTER(v, INDEX(v,,1)<>""))
)

---

How it Works

FILTER – selects only rows where:

Column M = "Y"

  Row is not completely blank (`BYROW` with `SUM(--(row<>""))`)
  
  **VSTACK** – stacks all 4 source sheets together.
  
  **FILTER(v, INDEX(v,,1)<>"")** – removes empty rows just in case.
  
  **SORT(...)** – sorts the combined data by **column A ascending**.
  

---

You can also control sorting more precisely. For example:

To sort descending by column A:

SORT(FILTER(v,INDEX(v,,1)<>""),1,-1)

• To sort by column B ascending:

    SORT(FILTER(v,INDEX(v,,1)<>""),2,1)Here’s the ready-to-use formula:
  

Answer 3

Issue is still there. I found that VSTACK function does not allow the new row to be appended at the last of the master sheet after the last entry. So, it will only append after the specific src-sheet last entry which is somewhere in the middle.

Is there any alternative function which can resolve this issue?