#N/A cell skip.

[serge]

Hi,
Here below is the display of my table that generate my combinations sets.
The return of other files are generated into this final table B13:F18 where all cells are not filled so it return #N/A.
So for my set combination to work in consecutive way and not skip, I have to physically type those numbers in the yellow table above in B5:F10.

In cell H7 down I have this formula :
=IF(ROWS($H$5:$H5)>COUNTA($B$5:$B$10)*COUNTA($C$5:$C$10)*COUNTA($D$5:$D$10)*COUNTA($E$5:$E$10)*COUNTA($F$5:$F$10),"",CONCATENATE(TEXT(INDEX($B$5:$B$10,MOD(INT((ROWS($H$5:$H5)-1)/(COUNTA($C$5:$C$10)*COUNTA($D$5:$D$10)*COUNTA($E$5:$E$10)*COUNTA($F$5:$F$10))),COUNTA($B$5:$B$10))+1),"00"),TEXT(INDEX($C$5:$C$10,MOD(INT((ROWS($H$5:$H5)-1)/(COUNTA($D$5:$D$10)*COUNTA($E$5:$E$10)*COUNTA($F$5:$F$10))),COUNTA($C$5:$C$10))+1),"00"),TEXT(INDEX($D$5:$D$10,MOD(INT((ROWS($H$5:$H5)-1)/(COUNTA($E$5:$E$10)*COUNTA($F$5:$F$10))),COUNTA($D$5:$D$10))+1),"00"),TEXT(INDEX($E$5:$E$10,MOD(INT((ROWS($H$5:$H5)-1)/(COUNTA($F$5:$F$10))),COUNTA($E$5:$E$10))+1),"00"),TEXT(INDEX($F$5:$F$10,MOD(ROWS($H$5:$H5)-1,COUNTA($F$5:$F$10))+1),"00")))

Then in :
J5 =MID(H5,1,1)+0
K5 =MID(H5,2,1)+0
L5 =MID(H5,3,1)+0
Etc...
Then in :
W5 : =CONCATENATE(J5,K5)*1
X5 : =CONCATENATE(L5,M5)*1
Etc...
So this way it works fine for me but I would like for the return of those files to be calculated directly without me retyping those numbers in the yellow table.

Because if I use = cell from the green table to the yellow this is what I get :

So what kind of formula could be use in H column that it would ignore the #N/A cells ?
Thank you.

 

[Peter_SSs]

I have assumed the green cells are actual numbers formatted to show leading zeros for the numbers less than 10. If that is so then try the formula shown for B5:F10 and then change every COUNTA in your big formula to COUNT

24 08 13.xlsm
	B	C	D	E	F	G	H
4
5	09	24	33	37	69		0924333769
6				64	60		0924333760
7							0924336469
8							0924336460
9
10
11
12
13	09	24	33	37	69
14	#N/A	#N/A	#N/A	64	60
15	#N/A	#N/A	#N/A	#N/A	#N/A
16	#N/A	#N/A	#N/A	#N/A	#N/A
17	#N/A	#N/A	#N/A	#N/A	#N/A
18	#N/A	#N/A	#N/A	#N/A	#N/A
serge

Cell Formulas
Range	Formula
B5:F10	B5	=IF(ISNUMBER(B13),B13,"")
H5:H18	H5	=IF(ROWS($J$5:$J5)>COUNT($B$5:$B$10)*COUNT($C$5:$C$10)*COUNT($D$5:$D$10)*COUNT($E$5:$E$10)*COUNT($F$5:$F$10),"",CONCATENATE(TEXT(INDEX($B$5:$B$10,MOD(INT((ROWS($J$5:$J5)-1)/(COUNT($C$5:$C$10)*COUNT($D$5:$D$10)*COUNT($E$5:$E$10)*COUNT($F$5:$F$10))),COUNT($B$5:$B$10))+1),"00"),TEXT(INDEX($C$5:$C$10,MOD(INT((ROWS($J$5:$J5)-1)/(COUNT($D$5:$D$10)*COUNT($E$5:$E$10)*COUNT($F$5:$F$10))),COUNT($C$5:$C$10))+1),"00"),TEXT(INDEX($D$5:$D$10,MOD(INT((ROWS($J$5:$J5)-1)/(COUNT($E$5:$E$10)*COUNT($F$5:$F$10))),COUNT($D$5:$D$10))+1),"00"),TEXT(INDEX($E$5:$E$10,MOD(INT((ROWS($J$5:$J5)-1)/(COUNT($F$5:$F$10))),COUNT($E$5:$E$10))+1),"00"),TEXT(INDEX($F$5:$F$10,MOD(ROWS($J$5:$J5)-1,COUNT($F$5:$F$10))+1),"00")))

 

[serge]

Hi Peter,
Thank you so much it works like a charm, I really appreciate the help.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.