Results 1 to 5 of 5

Thread: filling in rows to a specified Nth

  1. #1
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0

    filling in rows to a specified Nth



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I know this can be done, have seen it before. Just cannot remember for the life of me how to do it! If I have a workbook, and it has a calc in cell a1, how can I tell it how many rows to fill in with that calc? I am pretty sure if its offset and match, but not sure. Basically, I am using a cubeset formula to have as my limit (or number of rows to go down).

  2. #2
    Something like

    =IF(ROW(A1)<=$A$1,output_something,"")

  3. #3
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    Wouldn't that still be a manual drop down for the row? Here is what I have (sort of, scrubbed a little),
    cubeset=CUBESET("OLAP cube","[Order Details].[Item Class].[Initial Item Class].members","Item SKUs") which s a total of CUBESETCOUNT("previous calc") or, how many rows of data to retrieve.
    IFERROR(CUBERANKEDMEMBER("OLAP CUBE",$A$8,ROW()-ROW($A$8)+2),"") gives me the actual Item SKU, cell A8 is the cubeset formula. I can drag the calc down and fill each cell, but seems it could be done with an offset/match. Hope that all makes sense...

  4. #4
    You have to put a formula in every cell allowing for a growth rate in the data. As such, dragging and copying the formula that you have seems as good as it gets to me (it is certainly the same approach that I teach in my Power Pivot course), so I am not really sure what you think you are missing, what OFFSET/MATCH might give you.

  5. #5
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    Wishful thinking maybe. Thanks again Bob, I will go with that approach using the cubesetcount to identify how many rows to drag the calc down to.

Posting Permissions

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