return a value

[grm]

i am trying to find away of using a formular to put a period number in a cell dependant upon which period the costs have be put into.

periods nrs accross the top in the row, a cost is put into one of the periods and another column heading is period work carried out. in that column i need a formular to return the period number only, not the costs...can anyone help!?

 

[Aladin Akyurek]

On 2002-03-01 04:56, grm wrote:
i am trying to find away of using a formular to put a period number in a cell dependant upon which period the costs have be put into.

periods nrs accross the top in the row, a cost is put into one of the periods and another column heading is period work carried out. in that column i need a formular to return the period number only, not the costs...can anyone help!?

Select an empty cell, type =, select 5 rows of relevant data including the top row with numbered periods, hit F9, copy what you see, paste it in the follow up along with the expected results.

Aladin

 

[grm]

thanks aladin,

i think im doing your reply correctly, it seem to work in a fashon but it is returning period 1 every time.

the formular recognises that the value is in the 7th cell along but is not returning period 7 in the formular cell. any ideas? am i doing this correctly?

thanks again.

Gareth

 

[Aladin Akyurek]

On 2002-03-01 05:53, grm wrote:
thanks aladin,

i think im doing your reply correctly, it seem to work in a fashon but it is returning period 1 every time.

the formular recognises that the value is in the 7th cell along but is not returning period 7 in the formular cell. any ideas? am i doing this correctly?

thanks again.

Gareth

Gareth,

Just to make sure, what I proposed to do is not a solution. It's just a procedure to get a piece of your data so that you can post it. I looked at the proc I suggested: it doesn't seem to miss any required step, so please try it again.

Aladin

 

[grm]

={"Period 1","Period 2","Period 3","Period 4","Period 5","Period 6","Period 7","Period 8","Period 9","Period 10","Period 11","Period 12","Period 13";0,0,0,0,0,0,1600,0,0,0,0,0,0}

this returns 'period 1' not 7 like i would like it to.

any help appreciated

Gareth
This message was edited by grm on 2002-03-01 06:42

 

[Aladin Akyurek]

On 2002-03-01 06:16, grm wrote:
={"Period 1","Period 2","Period 3","Period 4","Period 5","Period 6","Period 7","Period 8","Period 9","Period 10","Period 11","Period 12","Period 13";0,0,0,0,0,0,1600,0,0,0,0,0,0}

this returns 'period 1'

i have sent you the sheet via email

Gareth,

What you posted above is exactly what I needed; you forgat to tell which range the above data occupy (saw it from the WB you sent). :wink:

In W2 array-enter:

=INDEX($1:$1,SUM(MAX((LEN(H2:T2)>0)*COLUMN(H2:T2))))

To array-enter a formula, you need to hit control+shift+enter, not just enter.

Copy down this formula as far as needed.

Aladin

 

[grm]

brilliant works a treat!

iv been trying to do that for ages!

thanks very much, saved me loadsa time

much appreciated

Gareth

 

[Aladin Akyurek]

On 2002-03-01 06:54, grm wrote:
brilliant works a treat!

iv been trying to do that for ages!

thanks very much, saved me loadsa time

much appreciated

Gareth

Gareth,

Wait a minute. That's an expensive formula.

Here another, ordinary and an effiecient formula:

In W2 enter and copy down:

=INDEX($1:$1,MATCH( 9.99999999999999E+307,A2:T2))

or

=INDEX($1:$1,MATCH( 9.99999999999999E+307,H2:T2)+7)

Copy down as far as needed.

Cheers.

Aladin