return a value
return a value
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: return a value

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ={"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 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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).

    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


  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    brilliant works a treat!

    iv been trying to do that for ages!

    thanks very much, saved me loadsa time

    much appreciated

    Gareth

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com