Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, back again, of course with problems. I am using a formula" =CHOOSE($AM$1,$S6,SUM($S6:$T6),SUM($S6:$U6),SUM($S 6:$V6),SUM($S6:$W6),SUM($S6:$X6),SUM($S6:$Y6),SUM( $S6:$Z6),SUM($S6:$AA6),SUM($S6:$AB6),SUM($S6:$AC6) ,SUM($S6:$AD6))" to get the desired results. My queries are :

    1. to cut the formula short to get the same results. Not looking for traditional lookup formula.
    2. is there any way to display the value instead of forumla, without using macro/VBA Code.

    Thanks in advance as I know it would be solved soon.


    Regards
    Prasad
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use this formula:

    =SUM(OFFSET($S$6,0,0,1,$AM$1))

    OFFSET is used here not to displace the cell, but to resize the number of columns; the number in AM1 specifies the number of columns.

    You cannot have a value in a cell (instead of a formula) and keep it up-to-date without VBA.

    You can, however, replace a formula with its result:
    - Select the cell with the formula.
    - Click the Copy button on the toolbar.
    - Click the little arrow next to the Paste button on the toolbar and select Values from the dropdown list.
    Once you have done this, the cell value will not be updated any more by Excel.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779844' date='15-Jun-2009 06:15']You could use this formula:

    =SUM(OFFSET($S$6,0,0,1,$AM$1))

    OFFSET is used here not to displace the cell, but to resize the number of columns; the number in AM1 specifies the number of columns.

    You cannot have a value in a cell (instead of a formula) and keep it up-to-date without VBA.

    You can, however, replace a formula with its result:
    - Select the cell with the formula.
    - Click the Copy button on the toolbar.
    - Click the little arrow next to the Paste button on the toolbar and select Values from the dropdown list.
    Once you have done this, the cell value will not be updated any more by Excel.[/quote]


    Thanks Hans for quick reply. I want to update the value on every change and copy/paste is not at all a solution. Pls suggest some VBA to serve the purpose.


    Regards
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why can't you use a formula? Using VBA has disadvantages:
    - Users must enable macros, otherwise the code won't run.
    - The code will disable undoing changes to the cells that contribute to the result.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779849' date='15-Jun-2009 06:59']Why can't you use a formula? Using VBA has disadvantages:
    - Users must enable macros, otherwise the code won't run.
    - The code will disable undoing changes to the cells that contribute to the result.[/quote]

    I think so and thats why I am avoiding VBA. Thanks for suggestion and help.

    Regards
    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='779850' date='15-Jun-2009 08:48']I think so and thats why I am avoiding VBA. Thanks for suggestion and help.

    Regards[/quote]


    Can "=CHOOSE($AM$1,$E6,$F6,$G6,$H6,$I6,$J6,$K6,$L6,$M6 ,$N6,$O6,$P6)" be replaced in same manner. I am bit confused.


    Regards
    Prasad

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes:

    =OFFSET($D6,0,$AM$1)

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779855' date='15-Jun-2009 10:40']Yes:

    =OFFSET($D6,0,$AM$1)[/quote]


    Thanks, I got the "OFFSET" and can use it easily.
    Regards
    Prasad

Posting Permissions

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