Assistance with an ARRAY

[RobSA]

Hi Folks,

I have been working on a file which contains the ARRAY function and whilst it works initially - it does not seem to continue through to its end and thus it does not provide all the values required.

I have attached the file and more detail in the REPORT page.

I have also tried to use the COUNTA function to tally the value created by the array and it does not work as it counts all the cells having an array rather than count the value.

Your help here would be most appreciated.

 

[Asheesh]

Hi,

If you change value in AO156 to "IN REVIEW", it will work..

Please check

 

[RobSA]

Hi,

If you change value in AO156 to "IN REVIEW", it will work..

Please check

Hi Asheesh,

Thanks for the reply. I have looked at the value in the LOG worksheet specifically AO156. That function doesn't change the formula or fix the error being encountered.

 

[Asheesh]

I am talking about "LOG" tab.

Also, to count the number of values available in in F134 of tab "REPORT" use the below method

COUNTIF(C138:C162,"*?")

 

[RobSA]

I am talking about "LOG" tab.

Also, to count the number of values available in in F134 of tab "REPORT" use the below method

COUNTIF(C138:C162,"*?")

Thanks for the countif function - I will try that

 

[Asheesh]

The ultimate function is dependent on column BL of LOG tab..It first checks if a date available in Column BL is greater or equal to $Q$138(date) of “REPORT” Tab and must be less than or equal to $O$138(Date) of “REPORT” tab.

Basis that it pulls the value from tab “LOG” C4:C500..

But the problem in this case:

Date in Column BL of tab “LOG” populates only if the status in Column BO of tab “LOG” is “IN REVIEW” status.

See the image

 

[RobSA]

Hi Asheesh,

Thanks for the reply.

The logic you provide is correct. And based on the selection made in the LOG worksheet "Column AO" will impact the result shown in columns BL, BK,BM of the same worksheet.

What seems to be happening is that the item 153 should be found in both column BL and BK and so it hangs.

Basically the formula is set up to look for items between certain dates and report them in the tables under the various headings.

I think if I can get the formula to recognise the items in column BK & BL at the same time, the problem would be resolved.

I hope this provides some clarity to the challenge

 

[Asheesh]

Hi Robsa,

Can you elaborate a little more on this “What seems to be happening is that the item 153 should be found in both column BL and BK and so it hangs.”

I am little confused when you say that the formula should recognixe items in column BK & BL at the same time..

 

[RobSA]

Hi Asheesh,

Thank you for the reply -my apologies for responding somewhat late.

The submittal information is currently found in columns BL & BK by a date appearing for the day the submittal took place.

It is possible that a submittal was received and returned in the same week and on the same day and that being the case the date will only show in one of those columns.

So for example the submittal 173 was received and returned on the same week and also on the same day.

This might be causing a problem.

I perhaps need to create another column or develop another formula to cater for that instance.

I look forward to your reply in support