Excel gurus - Part II

Desmo · 18-11-2009, 12:37

Need a little help with a formula I've got. It's currently working but I need to extend the range of cells covered but when I change the figure it stops working and I can't figure out why.

On the sheet JMA I have a list of jobs I do showing job no., customer name, job type and cost. The sheet this formula is on is for my invoice. I just have to add an invoice number to the job on the JMA sheet and it adds it to the current invoice sheet automatically.

When I first got the formula working, I only covered the first 100 rows on the JMA sheet, I now need to extend this to cover more cells.

The current formula that works is...

=IF(ISERROR(INDEX(JMA!$A$1:$F$100,SMALL(IF(JMA!$A$ 1:$A$100=$C$3,ROW(JMA!$A$1:$A$100)),ROW(1:1)),4)), "",INDEX(JMA!$A$1:$F$100,SMALL(IF(JMA!$A$1:$A$100= $C$3,ROW($A$1:$A$100)),ROW(1:1)),4))

...but if I change every reference of 100 to 200 it stops working and I can't figure out why. The cell just goes blank even though it showed data before and I'm still covering the same range of cells, just more of them.

18-11-2009, 12:37	#1
Desmo The Last Airbender Join Date: Jun 2006 Location: Pigmopad Posts: 11,915	Excel gurus - Part II Need a little help with a formula I've got. It's currently working but I need to extend the range of cells covered but when I change the figure it stops working and I can't figure out why. On the sheet JMA I have a list of jobs I do showing job no., customer name, job type and cost. The sheet this formula is on is for my invoice. I just have to add an invoice number to the job on the JMA sheet and it adds it to the current invoice sheet automatically. When I first got the formula working, I only covered the first 100 rows on the JMA sheet, I now need to extend this to cover more cells. The current formula that works is... =IF(ISERROR(INDEX(JMA!$A$1:$F$100,SMALL(IF(JMA!$A$ 1:$A$100=$C$3,ROW(JMA!$A$1:$A$100)),ROW(1:1)),4)), "",INDEX(JMA!$A$1:$F$100,SMALL(IF(JMA!$A$1:$A$100= $C$3,ROW($A$1:$A$100)),ROW(1:1)),4)) ...but if I change every reference of 100 to 200 it stops working and I can't figure out why. The cell just goes blank even though it showed data before and I'm still covering the same range of cells, just more of them. __________________