INDEX, MIN, COLUMN Formula help.....please!

[James8761]

Hi All,

I’ll try my best to explain. We had a bit of an excel whizz in the office last week who was setting up a spreadsheet.

He put in a formula that was working…….now it isn’t.

The idea is that each time we buy a new asset an asset number is given to it when you insert a column in an excel spreadsheet. This means we avoid duplication.

The formula currently reads……….=INDEX(DS8:EX68,1,MIN(IF(DS68:EX68="NO",COUMN(DS68:EX68)-(COLUMN($DS$68)-1),""))). The formula sits in Cell B5.

The first asset number is in Cell DS8. Cell EX8 has the word Insert. The idea being I insert a column each time a new asset is purchased and use the Next Available Asset Number given to me in Cell B5

Any help greatly appreciated.
James

 

[steve the fish]

Click on the formula. Is it surrounded by { } in the formula bar? It needs entering CTRL-SHIFT-ENTER to work. By the way i presume COUMN is just a typo?? By the way you can write it like this which doesnt require that type of entry:

=INDEX(DS8:EX8,MATCH("NO",DS68:EX68,0))

 

[James8761]

Click on the formula. Is it surrounded by { } in the formula bar? It needs entering CTRL-SHIFT-ENTER to work. By the way i presume COUMN is just a typo?? By the way you can write it like this which doesnt require that type of entry:

=INDEX(DS8:EX8,MATCH("NO",DS68:EX68,0))

Excellent, thanks so much for the quick reply. I just used the formula you gave and it works perfectly.

Thanks again,
James