Formula not recognising cells as blank with formulas that return "" (empty text)

Hi All,

I am trying to set up a sheet that will automatically generate a table/list of all possible combinations of 3 seperate lists of data. I have essentially got what I want however as I wish to change the base list lengths from time to time, the generation of all the combinations has to be flexible such that it doesnt do what it is doing for me in this example I have attached.

Combination_Generation.xlsx

I shall explain this spreadsheet.
Collumns A, B and C contain my base list data, that is, the days of the month, the asset numbers of assets that will be working that month and the shifts that they will work. Column C will not change in length, however column A and B will as the length of the month will change and the number and type of assets used in that month will change. Column A is generated in reference to cell L2 (which is determined from L1). Column E then generates the first set of combinations followed by Column F which adds the final combination as you can see.

It is important to note that I have dragged the formula in column E down to the 26th row (highlighted in grey) as list B will change in length (potential length change shown by red highlight). This works fine and returns blank cells as per the formula ("") depending on the length of list B. However, Column F is reading the blanks in column E and using them in the final combination function which as a result is returning false combinations as I have highlighted in orange (for the first occurence). (Ignore data in columns G, H & I)

How can I fix this?

Any help will be greatly appreciated and thankyou in advance.

Kind Regards,

Ben

Change the blue highlighted:

=IF((COUNTA(B:B)*$L$2*2)>(COUNTA($F$1:OFFSET(F1,-1,0))),IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(E:E),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(E:E)+1))&"/"&INDEX(E:E,MOD(ROW()-ROW($F$1),COUNTA(E:E))+1)),"")

with this:
COUNTIF(E:E;"*?")

What you have there sort of makes sense to me, however, unfortunately Excel cannot make sense of it :/

I get an error saying;

"You've entered too many arguments for this function. To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula."

Which I have done and it turns out the following logical test is volatile:

Formula:

Please Login or Register  to view this content.

Any suggestions?

Also I have used array formulas before, do you think this would be more appropriate? I think the main problem is that the COUNTA function will still count a cell that is empty if it has a formula in it, which is a problem with excel as you cannot have a truly empty cell with a formula still applied to it. I dont wish to delete the formula for the empty cells like some suggest as this will mean that the sheet has to be edited each time the list length changes, which I can do, but the people I am making this for not so much. Any other ways around this dilema?

Cheers

OK sorted!

That "Problem" was because I hadn't replace all the COUNTA(E:E) with COUNTIF(E:E,"?*"). So thankyou martindwilson for particularly pointing that out.

Thanks all for your help, I shall add reputation for everyones combined effort. Really appreciated! :D

Ben

I know you marked this as SOLVED but I have a somewhat simplified solution that I thought would be of interest. It doesn't require building intermediate results then breaking them back down again to the final results.

This file is set up for up to 500 combinations. For more, just copy the formulas in H:M down as far as necessary.