return a value

grm

New Member
Joined
Feb 28, 2002
Messages
7
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!?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
={"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
 
Upvote 0
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
 
Upvote 0
brilliant works a treat!

iv been trying to do that for ages!

thanks very much, saved me loadsa time

much appreciated

Gareth
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top