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
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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

Some videos you may like

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
  •