Using a calculation to show which column a figure falls under

**Gadgetgeeze** · 11-09-2009, 05:28 PM

I've attached a simple Excel sheet to hopefully help explain what I want to do. Basically I have Column A to show the Project I am working on and Columns B-M show the months of the year with the figures under certain months. In Column N I just want to show where a figure falls under a certain month, I want Column N to show the month (header) that particular figure is under e.g. N2 should return a value of "March", N3 ro return a value "June" and N4 to return a value "April/September".

Can anyone suggest a suitable calculation for this?

Thanks in advance.

Gadgetgeeze

**teylyn** · 11-09-2009, 06:13 PM

How many months will have values in the end? Up to 12, possibly? Two at the most? This will impact how complex the formula needs to be.

please enlighten me.

cheers

**Gadgetgeeze** · 11-09-2009, 06:29 PM

Shouldn't be more than two.

Thanks

**teylyn** · 11-09-2009, 06:36 PM

Shouldn't

Here's to hope.

Anyway, this ugly one in N2 deals with all 12 months. Copy down

=SUBSTITUTE(TRIM(CONCATENATE(IF(B2>0,$B$1,"")," ",IF(C2>0,$C$1,"")," ",IF(D2>0,$D$1,"")," ",IF(E2>0,$E$1,"")," ",IF(F2>0,$F$1,"")," ",IF(G2>0,$G$1,"")," ",IF(H2>0,$H$1,"")," ",IF(I2>0,$I$1,"")," ",IF(J2>0,$J$1,"")," ",IF(K2>0,$K$1,"")," ",IF(L2>0,$L$1,"")," ",IF(M2>0,$M$1,"")))," ","/")

**Gadgetgeeze** · 11-09-2009, 06:42 PM

That's brilliant - that works great and is exactly was what I was after! I didn't expect it to be so complicated to work out though!!

Thanks so much for your help. I've given you rep!

**teylyn** · 11-09-2009, 06:45 PM

I didn't expect it to be so complicated to work out though!!

I'm sure someone else will come up with a slicker way, but my logic skills are on half their usual power at the moment, so this klutz is the best I could come up with in the time frame.