Forum Discussion

FrameofLightDesigner's avatar
FrameofLightDesigner
Copper Contributor
Jul 21, 2020

Structured References Issue

I'm having an issue with my structured reference formula. It is part of a workbook for my job. And on one worksheet, I have a table. As folks check in, I have a timestamp put on it. in a column, outs...
excel
Formulas and Functions
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 22, 2020

FrameofLightDesigner 

It's not necessary to upload your working workbook. You may spend 10-15 minutes and generate simple sample workbook which illustrates an issue. Without it it's hard to say something concrete.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 22, 2020

    FrameofLightDesigner 

    Thank you. An error is since in formula

    =SUM(COUNTIFS('Rooming List'!H2:H252,"<=14",RoomingList[BRANCH],{"DAR/APP","DAR/ENL","DAV/APP","DAV/ENL"}))

    ranges 'Rooming List'!H2:H252 and RoomingList[BRANCH] are of different size. Formula requires the same size of the ranges. You may modify it as

    =SUM(COUNTIFS($H$2:INDEX(H:H,ROWS(RoomingList)+1),"<=14",RoomingList[BRANCH],{"DAG/APP","DAG/ENL"}))

    (see in cell B24), when it works. At least in this part.

    • FrameofLightDesigner's avatar
      FrameofLightDesigner
      Copper Contributor
      Jul 22, 2020

      SergeiBaklan  That worked beautifully! I can't thank you enough! I've been trying to fix that for 5 days now! You're amazing!

      • FrameofLightDesigner's avatar
        FrameofLightDesigner
        Copper Contributor
        Jul 22, 2020

        SergeiBaklan I spoke to quickly. It's not working in every cell. It worked in B24 and E24, but not C24 and J24, so far. What could be the cause?

Resources