+ Reply to Thread
Results 1 to 97 of 97

Call in the Cavalry - 2020

  1. #1
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Call in the Cavalry - 2020

    Happy New Year all!
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Call in the Cavalry - 2020

    Thank you. And you, too.

    Thanks for starting the new 'Call in the Cavalry' thread.
    Dave

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    This may need VBA as the OP says they do not want to use helper columns.
    https://www.excelforum.com/excel-for...ml#post5253774
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,986

    Re: Call in the Cavalry - 2020

    Can anyone help this member with a simple VBA random spelling test from a bank of 250 items?

    https://www.excelforum.com/excel-pro...uage-test.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Higher mathematics and/or programming may be the answer, in either case I have hit the proverbial wall on this one.
    https://www.excelforum.com/excel-for...ml#post5255516

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-gen...ing-array.html

    This is getting a bit out of hand with no helper columns. VBA expertise perhaps?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Either I am not getting the point across or I am not understanding. In any event I would be happy for someone to look.
    https://www.excelforum.com/excel-gen...ml#post5259620

  8. #8
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Call in the Cavalry - 2020

    Quote Originally Posted by JeteMc View Post
    This may need VBA as the OP says they do not want to use helper columns.
    https://www.excelforum.com/excel-for...ml#post5253774
    The OP never responded, but gave me a rep today (after 13 days) saying "Thank You". All seems good.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Thank You for looking at the thread Ace_XL. I hope that you have a blessed day.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    I'll readily admit I often do not see the obvious, but does anybody have any thoughts here?

    https://www.excelforum.com/excel-for...ml#post5265871
    HTH
    Regards, Jeff

  11. #11
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,596

    Re: Call in the Cavalry - 2020


  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    I'm completely lost here. Anybody have some extra time to see what's going on?

    https://www.excelforum.com/excel-pro...ml#post5271118

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    OP would like the months to update automatically in the columns field of the pivot table. Best I was able to offer was manual adjustment using grouping.
    https://www.excelforum.com/excel-cha...ml#post5271405

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    I provided a VBA that isn't helping out any better, but the OP is trying to fill in 300,000 rows with a Sumif formula. I suspect it could be recalculating all the way down, but not sure. Any thoughts or is there an alternative?

    https://www.excelforum.com/excel-pro...ml#post5272234

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Would appreciate someone with Power Pivot/Power Query expertise having a look at this one. I probably employed inefficient methods and was not able to come up with a solution to producing subtotals by month per type of investment.
    https://www.excelforum.com/excel-cha...ml#post5273253

  16. #16
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: Call in the Cavalry - 2020

    Am I doing some classic mistake that makes Excel crash? Or is it just that buggy? Too bad, I really liked the task...
    https://www.excelforum.com/excel-for...ml#post5278037

    It started when I put the code in the sheet module I think and I had to rename the macros to make the buttons work.
    Last edited by Jacc; 02-13-2020 at 03:48 PM.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,986

    Re: Call in the Cavalry - 2020

    I just cannot get my head around what is required in this thread: https://www.excelforum.com/excel-for...ied-value.html

    It's all time-related calculations, and the OP has provided a workbook with a very thorough explanation. Trouble is, I can't follow it. Anyone care to take a look?

  18. #18
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    My brain is not working correctly tonight.

    https://www.excelforum.com/excel-gen...ncurrency.html

    Could somebody offer some assistance on the above please!

  19. #19
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,070

    Re: Call in the Cavalry - 2020

    Calling Pivot Table Gurus. Can't think of any easy way to do this.

    https://www.excelforum.com/excel-gen...vottables.html
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  20. #20
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Call in the Cavalry - 2020

    I am sure that I am over complicating this one. It is a best fit bundling project.

    https://www.excelforum.com/excel-gen...to-bundle.html

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Call in the Cavalry - 2020

    This was already posted back on 2/23 by jeffreybrown. I thought I had a solution. I did for the sample, but OP has to output of 200K rows. My solution does not handle that.

    Can this be done more efficiently with VBA or other?

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    @Dave, perhaps posting the file*, in the Call Concurrency thread may help someone either build on that or convert the thought process into code.
    *I am speaking of the one you mention to in post #17 that you tested using helper columns and which sped up the original single formula process.

  23. #23
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Call in the Cavalry - 2020

    JeteMc. Good idea. Done back at the thread. Thanks.

  24. #24
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,430

    Re: Call in the Cavalry - 2020

    I managed to help the OP partially https://www.excelforum.com/excel-new...ml#post5288440 but have no idea why the conditional formatting triggers and why the COUNT function used by the OP returned a strange outcome
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  25. #25
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    This is out of my league. Can somebody kindly provide some assistance?

    https://www.excelforum.com/excel-pro...ml#post5290042

  26. #26
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Call in the Cavalry - 2020

    Any "Smart Guru's" out there that are good at CF?

    Try to tackle this one:

    https://www.excelforum.com/excel-gen...mity-date.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  27. #27
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020


  28. #28
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    Anybody have some ideas to lend here!

    https://www.excelforum.com/excel-gen...ml#post5291124

  29. #29
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    Am I missing the forest thru the trees here?

    https://www.excelforum.com/excel-gen...ml#post5293625

    Any suggestions or ideas?

  30. #30
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    I have a suspicion this will be awfully tough to do with formulas, but for some of you, not so much. I offered a pivot table, but no luck.

    https://www.excelforum.com/excel-for...ml#post5294037

  31. #31
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    Would anybody like to take a crack at this?

    https://www.excelforum.com/excel-for...ml#post5296893

  32. #32
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Call in the Cavalry - 2020

    Hey -

    Any "Smart Gurus" available to sort a two column table using different directions in a single formula? I'll give rep for any that work.
    https://www.excelforum.com/excel-gen...s-formula.html

  33. #33
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Call in the Cavalry - 2020

    This is humiliating. Looks like a straight forward Conditional Format ... not so much. Cannot get consistent results. https://www.excelforum.com/excel-for...ant-value.html

  34. #34
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    In this solution, I've used a helper cell. Can somebody offer a solution with the helper cell?

    https://www.excelforum.com/excel-gen...ing-dates.html

  35. #35
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Call in the Cavalry - 2020

    If I'm understanding this person's issue, they need to create a bar code from a value in the data tab A2 though it has taken a while for me to get the info out of them (and I could still be wrong LOL). I do not have a bar code font downloaded.
    https://www.excelforum.com/excel-for...ml#post5301436
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  36. #36
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,070

    Re: Call in the Cavalry - 2020

    Stuck on this one. The OP wants to create a new list of items that are in List 1 but not in List 2. OP wants to include any non-matched up items. For example, 300 appears in list 1 twice but in list 2 only once so it should go in the new list. thanks in advance

    Note: I'm pretty sure this is the post Brian is referring too https://www.excelforum.com/excel-for...-two-list.html
    Last edited by jeffreybrown; 03-27-2020 at 03:59 PM.

  37. #37
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Call in the Cavalry - 2020

    Anybody feel like putting in some vba.formula code for Rachael? I just don't think it is going to get her where she needs to go.
    https://www.excelforum.com/excel-pro...acro-code.html

    She clears a big range and then needs formulas back again? I'm just a little confused on her need.

  38. #38
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    204

    Re: Call in the Cavalry - 2020

    I am not familiar with Excel's Online version and barely so for google sheets; however it seems like an online offering should be able to do what the OP is asking for here https://www.excelforum.com/showthread.php?t=1309771

    Otherwise SharePoint has a bazillion options, one of which if I remember correctly is to host ASP files which they could just target with a HTTP request.

    I haven't had to do this yet, but hoping someone else with a bit more exp in this area can guide the way.
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

  39. #39
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Call in the Cavalry - 2020

    Any ideas for cycling through the autofilter options in one column while not losing the manually applied autofilter options previously applied to other columns?

    https://www.excelforum.com/excel-gen...eing-used.html

  40. #40
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Call in the Cavalry - 2020

    This OP is doing Lotto picks and has some algorithm I just can't grasp. Wanna help him?
    https://www.excelforum.com/excel-pro...two-pairs.html

  41. #41
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,624

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-pro...ror-287-a.html

    The OP is using VBA in Excel to create and send an Outlook email. He is getting a 287 runtime error on a call to instantiate a Word editor. He is using late binding.

    Please Login or Register  to view this content.
    I ran his code and I did not get the error.

  42. #42
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    This OP is attempting to produce two pivot tables from a data model having the dates grouped differently in each. The OP is using the 2013 version of Excel. I can produce the pivot tables as described in the video the OP references (link is in post #7 (@13:00 mark)), but I am using the 2019 version. Was hoping that someone who has the 2013 version would test and see if the method described in the video works for them.
    https://www.excelforum.com/excel-gen...ml#post5317643

  43. #43
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-pro...ting-name.html

    Getting an Error 75 with my code. Don't know how to fix it. Anyone got a solution here. Code is in post #4.

    Thanks

  44. #44
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Call in the Cavalry - 2020

    Hi Alan,

    I've posted a solution which appears to work.


    Thanks for all of your contributions on here!

    Regards,

    Greg M

  45. #45
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    Thanks Greg. Let's hope the OP likes it.

    Alan

  46. #46
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Call in the Cavalry - 2020

    Thought I had a pretty sweet running routine by post #43 here but it seems that there is an insurmountable date formatting problem. Good Luck!

    Update - I "doubled it up" and might have fixed it - we'll see

    Update - The "doubling" did it - so don't need help and thanks anyway!
    Last edited by xladept; 04-30-2020 at 12:41 AM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  47. #47
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,070

    Re: Call in the Cavalry - 2020

    OP looking for an event driven macro that, upon pasting into columns A:D, will modify the cell colors dependent upon which day of the week it is.

    https://www.excelforum.com/excel-for...ml#post5327047

  48. #48
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    OP is trying to plot "gaps as #N/A" in a line chart. The behavior of the #N/A error in charts has changed in 2016 and later, so I cannot test. I'm hoping someone with a newer version of Excel can cover the details of using N/A as empty for Excel 2016. (Link to Peltier's tutorial on this in my response to the thread).

    https://www.excelforum.com/excel-cha...ml#post5327646
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  49. #49
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    This one is currently headed towards a question on database design and how to get data into a database. Since I don't do databases, I am probably not the best one to help. Perhaps one of you with more experience with databases in Excel can see what help you can offer.

    https://www.excelforum.com/excel-for...ml#post5328759

  50. #50
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    Assuming I understand the OP's situation here (https://www.excelforum.com/excel-cha...on-x-axis.html ), we are trying to build a "pivot chart" that shows data for "hours" (categories) that may not exist in the source data. I have reached the limits of what I know about pivot tables and charts. I can work with the solutions I have proposed, if the OP accepts either solution, but I am not sure if there is a better solution (maybe using power pivot or power BI, if necessary?). If anybody has additional options to offer, I would be interested to see what else would be proposed -- even if the OP is not interested in other options.

  51. #51
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    The request in post #7 of the thread is to get the pivot table to display correctly after calculated items have been added.
    https://www.excelforum.com/excel-gen...ml#post5333396

  52. #52
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,624

    Re: Call in the Cavalry - 2020

    Does anybody know the macro language for Google Sheets? User wants to mirror cells on two sheets.

    https://www.excelforum.com/for-other...ml#post5326007

  53. #53
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-for...timestamp.html

    I don't do databases and have no experience with Power Query, but something in the direction this thread is headed feels like those may become important to the thread. Anyone with experience with Power Query want to jump in and make some suggestions?

  54. #54
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    I don't know PQ, and the OP has expressed interest in how PQ might help him/her work with some Power Shell generated text files: https://www.excelforum.com/excel-new...ml#post5343258

  55. #55
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    @MrS
    PQ solution applied.

  56. #56
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-cha...ml#post5350598

    The OP wants to be able to change the "inclusive/exclusive" setting for all the data series in his box plot in one go. If 2016 behaves like my older version, Excel does not allow you to select multiple series and change a setting for all series at once. I am looking for someone who actually uses 2016 or newer to comment on what options the OP can use to change multiple series in a box plot.

  57. #57
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    OP needs help with creating calculation in Pivot Table. I was able to achieve using Power Query. OP is new to this Excel add-in and is looking to achieve with regular PT. I cannot get it to work. Maybe you can?

    https://www.excelforum.com/excel-cha...ml#post5350692

  58. #58
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    OP is looking for a Power Query solution to remove duplicates but with conditions that the Max FTE be the surviving position while the total of the FTE be expressed in with that position. I am out of ideas on how to achieve this.

    https://www.excelforum.com/excel-gen...ml#post5354521

  59. #59
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,624

    Re: Call in the Cavalry - 2020

    OP has a fairly simple macro-free file that causes Excel to burn 25% CPU when it is opened, and Excel becomes unresponsive. He attached a stripped-down version (blank template) that he says also freezes for him but doesn't give my any problem at all.

    He has gone through all the textbook troubleshooting, including uninstall/reinstall.

    Any ideas? https://www.excelforum.com/excel-gen...-workbook.html

  60. #60
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,688

    Re: Call in the Cavalry - 2020

    This is my situation #2)
    https://www.excelforum.com/excel-for...ml#post5357410
    Also, my experience in the past,
    Sucuri stop me using ">" or "<"
    Why and how to avoid this? It confuse me a lot.

  61. #61
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,986

    Re: Call in the Cavalry - 2020

    Just type spaces either side of the < or > in the formula - this has been asked and answered many times.

  62. #62
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    Could somebody please lend a hand here. Thank you!

    https://www.excelforum.com/excel-pro...ete-chart.html

  63. #63
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,688

    Re: Call in the Cavalry - 2020

    Seen this thread several times and hope someone could jump in to help:

    https://www.excelforum.com/excel-for...ml#post5363931

    I will start with helper columns. But I do not guarrantee the success...

  64. #64
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    OP is using Vlookup to get data. All lookups are text and excel converts some of the values such as 25E1 as Scientific Notation and not as text. Can you help?

    https://www.excelforum.com/excel-pro...ml#post5365500

  65. #65
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-gen...ml#post5365559 This one quickly exceeded my knowledge of pivot tables.

  66. #66
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    This OP has a sheet with merged cells all over and is trying to get a formula to work.

    The things I have tried are not working, so looking to see if anybody else has some thoughts.

    https://www.excelforum.com/excel-for...ml#post5367932

  67. #67
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,596

    Re: Call in the Cavalry - 2020

    Hello,
    Can any Mac people help with this?

    https://www.excelforum.com/excel-pro...names-pdf.html

  68. #68
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,897

    Re: Call in the Cavalry - 2020

    So I tried to help this OP out, but turns out it wasn't just opening a web page, it is a link to a file download. I'm not sure how to avoid the download prompt, but I'm sure someone here has more experience in this area...

    https://www.excelforum.com/excel-pro...-with-vba.html
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  69. #69
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Call in the Cavalry - 2020

    Got an interesting 1 here, anyone feel like taking a look?
    https://www.excelforum.com/excel-gen...side-down.html
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  70. #70
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Call in the Cavalry - 2020

    Any suggestions to fix the issue will be very much helpful

    https://www.excelforum.com/excel-gen...hout-save.html


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  71. #71
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Call in the Cavalry - 2020

    Is it possible to read displaying sheet tab names?

    https://www.excelforum.com/excel-gen...ing-sheet.html

    In the below screenshot sheets 3,4 & 5 only visible in scroll area and rest of the sheets is not displaying.

    Any property is there to read that displaying sheet names?

    Screenshot2.JPG

  72. #72
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,986

    Re: Call in the Cavalry - 2020

    Can anyone pick this one up?

    https://www.excelforum.com/excel-for...e-at-work.html

    I have finally persuaded to OP to create a normalised master sheet from which he wants to produce Gantt-like time sheets with colour coding. Unfortunately I am short of time for the next few days, so can't show him the next step right now. Any assistance appreciated.

  73. #73
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Call in the Cavalry - 2020

    Can a VBA expert lend a hand here please?
    https://www.excelforum.com/excel-gen...erent-tab.html

  74. #74
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Call in the Cavalry - 2020

    Hi Ford,

    Even though jindon's solution to https://www.excelforum.com/excel-gen...erent-tab.html appears to work for me, the OP appears to still have problems, so I've posted an alternative approach.

    Thanks & best regards,

    Greg

  75. #75
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,070

    Re: Call in the Cavalry - 2020

    Someone looking for code to "View a hidden tab" Looks like they need code to unhide the sheet, then rehide it when leaving sheet or shutting down.

    https://www.excelforum.com/excel-gen...heet-tabs.html

  76. #76
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,199

    Re: Call in the Cavalry - 2020

    Can someone please look on this?

    I avoid to repeat calculation to to get 2nd largest. MATCH(LARGE(MMULT(SUMIFS(...)),2),MMULT(SUMIFS(.....)),0)

    https://www.excelforum.com/excel-gen...locations.html

    Any help greatly appreciated.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  77. #77
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,070

    Re: Call in the Cavalry - 2020

    Need VBA to pull contents of a cell and place them As a Comment (maybe a note)

    https://www.excelforum.com/excel-for...-one-cell.html

  78. #78
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,986

    Re: Call in the Cavalry - 2020

    Anyone (Olly?) able to offer a web scraping PQ solution here? It involves looping through E-mail addresses.

    https://www.excelforum.com/excel-pro...-web-page.html

  79. #79
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Could someone with a better understanding of how attachments work than I have a look at this thread. The OP is attempting up attach a .zip file which they state is 2.55 MB, so it seems that it should attach, but is not.
    https://www.excelforum.com/excel-for...ml#post5390715

  80. #80
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,054

    Re: Call in the Cavalry - 2020

    Could somebody take a look at this?

    https://www.excelforum.com/excel-for...ell-rules.html

  81. #81
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,596

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-pro...-function.html

    UDF won't kick in when opening workbook

  82. #82
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    Cannot determine why this works for all but row 4.

    https://www.excelforum.com/excel-for...ml#post5394123

    Any thoughts here.

  83. #83
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,688

    Re: Call in the Cavalry - 2020


  84. #84
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Can anyone help the OP with their request to lock/protect a cell without loosing the functionality of that cell as described in the last sentence of post #25 of the thread linked below?
    https://www.excelforum.com/excel-for...ml#post5395609

  85. #85
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Call in the Cavalry - 2020

    Anyone interested in looking at: https://www.excelforum.com/excel-for...olumn-l-p.html

    At first glance it appeared to be a straightforward "lookup" type problem, but I simply don't see a solution.

    With reference to the post #18 attachment, the two yellow shaded areas are the source data and are used to generated columns L:P. There's a good set of expected results in columns R:V. Columns W:AA flag where the proposed solution deviates from the expected results.

  86. #86
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,688

    Re: Call in the Cavalry - 2020

    Anyone ready to help?
    https://www.excelforum.com/excel-for...ml#post5402623

    For me, it is so complicated to allocate stock under priority order.

    The OP really needs help ASAP.

  87. #87
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Call in the Cavalry - 2020

    I've lost the direction the OP is going here... https://www.excelforum.com/excel-gen...iply-rows.html
    They wanted to replicate cells 8 times and I gave them a formula for that.
    So I thought I got it through post #8 but then it appears they decided they decided they have some they want replicated 16 times and some 8 times (I think) and I don't know how to do that - have the index change in the middle then change back.

    Then I'm not sure what the second request is they want beginning in post #9. Maybe someone else can take it on and get them a solution.

  88. #88
    Registered User
    Join Date
    07-20-2018
    Location
    Atlanta, GA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Call in the Cavalry - 2020

    Not sure I understand the is happeing in this thread

  89. #89
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    Resolved. Issue was OP default set up in PQ.

    OP has an issue with Power Query. I have offered up a solution that works for me (running Excel 2019). OP is running 2010 and 2013 however, I am not sure which machine has the issue.

    Is this a possible compatibility issue when building parameter queries in different versions of Excel/PQ? If you have any experience in PQ and parameter queries, please take a look see.

    https://www.excelforum.com/excel-for...ml#post5415144

    Alan
    Last edited by alansidman; 10-27-2020 at 04:53 PM. Reason: Resolved

  90. #90
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    Cannot determine issue with OP's copy line in VBA. Tried to resolve with Union.Range but getting 1004 error message and cannot repair.

    https://www.excelforum.com/excel-gen...-to-sheet.html

  91. #91
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,704

    Re: Call in the Cavalry - 2020

    Need some assistance on developing some VBA to help the OP rename cells. Look at Post #7

    https://www.excelforum.com/excel-for...data-help.html

  92. #92
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,883

    Re: Call in the Cavalry - 2020

    Could someone whom is fluent in Power Query code take a look. I got this one started but ran into an issue which I could not resolve (please see my post in the thread).
    https://www.excelforum.com/excel-new...ml#post5418030

  93. #93
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Call in the Cavalry - 2020

    https://www.excelforum.com/excel-cha...er-median.html

    This is a salary data charting request. The requirements are non-trivial and not crystal clear (at least not to me).

    Anyone want to take a look?

  94. #94
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Call in the Cavalry - 2020

    The OP wants a gradient shaded X axis. I was able to create one fairly easily using the options in the Format axis dialog, but now the OP wants to be able to replicate some of this in VBA. The macro recorder tells me nothing about how to format an axis line the way the dialog allows, so I have no idea what to recommend.

    https://www.excelforum.com/excel-pro...ml#post5422026

  95. #95
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,070

    Re: Call in the Cavalry - 2020

    I think this person needs a UDF to replace cell references with values and pasting the formula into the new cell as text. I can't see how to do it with formulas.

    https://www.excelforum.com/excel-for...ther-cell.html
    Last edited by ChemistB; 11-13-2020 at 05:21 PM.

  96. #96
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Call in the Cavalry - 2020

    Really interesting question being asked here...
    https://www.excelforum.com/excel-gen...ml#post5427731

    When I hold down the Ctrl key and left click a cell it darkens (as if fill shaded) and on further clicks on that same cell darkens even further to black.
    Anyone care to take a look and comment please?

  97. #97
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Call in the Cavalry - 2020

    Must be a full moon somewhere...another weird/interesting 1...
    https://www.excelforum.com/excel-for...ml#post5427739

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Call in the Cavalry - 2019
    By MrShorty in forum The Water Cooler
    Replies: 163
    Last Post: 12-30-2019, 02:39 PM
  2. Call in the Cavalry - 2018
    By AliGW in forum The Water Cooler
    Replies: 152
    Last Post: 01-10-2019, 12:06 PM
  3. Call in the Cavalry - 2017
    By JBeaucaire in forum The Water Cooler
    Replies: 166
    Last Post: 01-03-2018, 12:58 PM
  4. Call in the Cavalry - 2016
    By JBeaucaire in forum The Water Cooler
    Replies: 147
    Last Post: 01-10-2017, 12:36 PM
  5. Call in the Cavalry - 2015
    By davesexcel in forum The Water Cooler
    Replies: 297
    Last Post: 01-13-2016, 06:50 PM
  6. Call in the Cavalry - 2013
    By FDibbins in forum The Water Cooler
    Replies: 457
    Last Post: 01-06-2015, 08:00 PM
  7. Call in the Cavalry - 2014
    By JBeaucaire in forum The Water Cooler
    Replies: 351
    Last Post: 01-06-2015, 07:56 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1