Forum Discussion

alecsi's avatar
alecsi
Copper Contributor
Dec 12, 2023

LET formula problem

Hi guys, can you have a look on this? My LET formula does not do the job, something is missing and I can't figure out what. I attach excel file and explain below what's needed: - Sheet1, Sheet2, an...
Excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Dec 12, 2023

    alecsi 

     

    In addition to the very valid observations by my friends Patrick2788 and Riny_van_Eekelen , I find myself wondering whether it's even necessary to have three separate sheets with exactly the same sets of columns.

     

    If they are referring--a no doubt they are--to different entities, different products, people, services, whatever, you can still differentiate if you

    • create a single table with those 12 columns you already have
    • add a column that contains the Identifier of whatever it is that makes Sheet1 differ from Sheet 2, etc

    So I've done that with the Sheet labeled "Combined" -- I haven't taken the time to go to your sheet 10 to work on your LET question. Just wanted to demonstrate a combined database.

     

    With that combined database, for what it's worth, Excel could still separately report on each entity whenever desired--and do the composites and whatever else. And you can add new rows pertaining to any one of the entities in any order. Just identify them.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 13, 2023

alecsi 

I agree with the others that you shouldn't need to filter blank records from Tables because they have no reason to be there in the first place.

 

= LET(
    tables,   VSTACK(Table1,Table2,Table3),
    isEmptyλ, LAMBDA(v, OR(v<>"")),
    data,     FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
    VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
  )

 

Where it is an issue is when one uses a 3D range (in order to accept addition sheets without changing the formulas), since this, by its nature, is likely to be oversized

 

= LET(
    tables,   VSTACK(Table3D),
    isEmptyλ, LAMBDA(v, OR(v<>"")),
    data,     FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
    VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
  )


Table3D
=Sheet1:Sheet3!$A$2:$L$20

 

These accept any records with data in one of the first 9 fields.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 13, 2023

    .. or with the pretty colours

    = LET(
    tables,   VSTACK(Table3D),
    isEmptyλ, LAMBDA(v, OR(v<>"")),
    data,     FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
    VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
  )


Table3D
=Sheet1:Sheet3!$A$2:$L$20

    I use direct cell referencing so rarely (here it is used to define a Name) that I thought I might as well re-enter the formula.  Shame 'Sheet1' is not yellow but the salmon pink shows well if you have a taste for that kind of thing.