Forum Discussion

Norman_Glenn's avatar
Norman_Glenn
Brass Contributor
May 01, 2024

Formulas wile working with few sheets information

Hi,,, below is an example of a work book I'm using, In this example I'm using 2 work sheets but normally it's more, in work sheet 1 I have information and in column 1 I have a formula been used, in sheet 2 I have filled information from sheet 1, you may see I have coloured them using conditional formatting, to the right on sheet 2 I want to creat a formula that will pull the correct iteams under the correct section,colour, I'm using, in cell I22 I'm puting   =if(A3="WT C",A3:A4,"")    when I enter this it returns nothing, well nothing that I can see, no error no box saying I'm doing it wrong, 

  • SergeiBaklan's avatar
    SergeiBaklan
    May 09, 2024

    Norman_Glenn 

    #SPILL! error

    if it's not enough space (range wit blank cells) to expand the data by formula.

    If we check next row(s)

    there are some formulas or data in the next cells. Select and clean (Del) them to empty the space for the dynamic array, it works now

  • djclements's avatar
    djclements
    Bronze Contributor

    Norman_Glenn On the items worksheet, the formula in column A is returning an extra space character at the end of the text string. For example, the first part of the formula in cell A2 is =B2&IF(logical_test," C "," P "), which returns "WT C ".

     

    Change the formula in cell I22 on the Carry Over Page worksheet to either of the following:

     

    =IF(A3="WT C ",A3:D3,"")
    
    //OR
    
    =IF(TRIM(A3)="WT C",A3:D3,"")
    • Norman_Glenn's avatar
      Norman_Glenn
      Brass Contributor
      I tried the trim formula and it carried over 2 difrent options in stead of the one I was looking for but I'll try it tomorrow in my spread sheet and see how it works
  • Norman_Glenn 

    In addition, since you use formulae like =if(A3="WT C",A3:A4,"") it looks like you are on Excel 365, or at least 2021. If so why don't to use dynamic arrays more extensively, e.g. fo the beginning in I22

    =IFERROR(
        FILTER(
            CHOOSECOLS('items '!$A$2:$Q$600, {1,3,4,17} ),
            'items '!$A$2:$A$600 = $I$20 ), "" )

    and so on

    • Norman_Glenn's avatar
      Norman_Glenn
      Brass Contributor
      So far your reply works best on my phone, tomorrow ill be trying it on my sheet on my work book and I'll let you know

Share