Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

Thread: Table calc with a slicer

  1. #11
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010


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

    Sorry
    Couldn't resist playing about with a Pivot Table !
    Attached Files Attached Files

  2. #12
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,632
    Articles
    0
    Excel Version
    O365
    Try this Mike

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(Table1[[#Headers],[Sales]],ROW(Table1[[#All],[Sales]])-ROW(Table1[[#Headers],[Sales]]),,1)),--(Table1[[#All],[date]]=B20))

  3. #13
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Another possibility - Having set up the Pivot Table rather than a standard table, you can then connect slicers to it (as the attachment) provided your working with Excel 2010 and later. If you want to use slicers on other tables, you need Excel 2013.
    Attached Files Attached Files

  4. #14
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    With a pivot table, I could use the slicer, the just use a getpivotdata formula, but was really trying to avoid the pivot table altogether. However! Bobs solution worked like a charm. Does bring up another question tho, what does the double dash represent in this formula? I have never used that one.

  5. #15
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello Mike
    Glad we found the right solution for you. The double unary is a way to convert a string representation of a number into its number form (i.e. "123" into 123).

  6. #16
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    Interesting. One thing I put into charts is a text box under the chart to pull data in so they can see what the number is (such as Last weeks sales: N Average: N and o forth). To do this I have to put together a formula to concatenate the text along with the cell address to create the text. Calc works fine, right up till someone wanted me to insert commas into the number. Now I have to use the LEN along with the ROUND and CONCATENATE just to pull all the info together. Is it possible to just use the "unary" as you call it to do the same thing? Turn the number section into a number so it would automatically format as a number?

  7. #17
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Mike_Alex View Post
    Interesting. One thing I put into charts is a text box under the chart to pull data in so they can see what the number is (such as Last weeks sales: N Average: N and o forth). To do this I have to put together a formula to concatenate the text along with the cell address to create the text. Calc works fine, right up till someone wanted me to insert commas into the number. Now I have to use the LEN along with the ROUND and CONCATENATE just to pull all the info together. Is it possible to just use the "unary" as you call it to do the same thing? Turn the number section into a number so it would automatically format as a number?
    Im inclined to think that it wouldn't work on its own, and in any case you can achieve the same end result by performing any mathematical manipulation. The easiest is to add zero to the string numeric, which doesn't alter the value of the resulting number.
    I suggest that you open a new thread for this latest problem with an example showing the expected and actual results

  8. #18
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    I may have to do just that. Thanks for all your assistance there Hercules!

  9. #19
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,632
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Mike_Alex View Post
    However! Bobs solution worked like a charm. Does bring up another question tho, what does the double dash represent in this formula? I have never used that one.
    It is all explained in http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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