# Off Topic > The Water Cooler >  >  Call in the Cavalry - 2020

## JBeaucaire

Happy New Year all!

----------


## FlameRetired

Thank you. And you, too. 

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

----------


## JeteMc

This may need VBA as the OP says they do not want to use helper columns.
https://www.excelforum.com/excel-for...ml#post5253774

----------


## AliGW

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

----------


## JeteMc

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

----------


## Ace_XL

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

This is getting a bit out of hand with no helper columns. VBA expertise perhaps?

----------


## JeteMc

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

----------


## Ace_XL

> 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.

----------


## JeteMc

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

----------


## jeffreybrown

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

----------


## davesexcel

Fixing url in code
https://www.excelforum.com/excel-pro...ml#post5269666

----------


## jeffreybrown

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

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

----------


## JeteMc

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

----------


## jeffreybrown

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

----------


## JeteMc

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

----------


## Jacc

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.

----------


## AliGW

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?

----------


## jeffreybrown

My brain is not working correctly tonight.

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

Could somebody offer some assistance on the above please!

----------


## ChemistB

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

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

----------


## FlameRetired

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

----------


## FlameRetired

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?

----------


## JeteMc

@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.

----------


## FlameRetired

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

----------


## Pepe Le Mokko

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

----------


## jeffreybrown

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

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

----------


## MarvinP

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

----------


## jeffreybrown

Not sure on this one

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

----------


## jeffreybrown

Anybody have some ideas to lend here!

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

----------


## jeffreybrown

Am I missing the forest thru the trees here?

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

Any suggestions or ideas?

----------


## jeffreybrown

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

----------


## jeffreybrown

Would anybody like to take a crack at this?

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

----------


## MarvinP

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

----------


## FlameRetired

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

----------


## jeffreybrown

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

----------


## Sam Capricci

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

----------


## ChemistB

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

----------


## MarvinP

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.

----------


## Gregor y

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.

----------


## JBeaucaire

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

----------


## MarvinP

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

----------


## 6StringJazzer

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.

----------


## JeteMc

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

----------


## alansidman

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

----------


## Greg M

Hi Alan,

I've posted a solution which appears to work.

https://www.excelforum.com/excel-pro...ml#post5320935
Thanks for all of your contributions on here!   :Smilie: 

Regards,

Greg M

----------


## alansidman

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

Alan

----------


## xladept

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! :Smilie: 

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

Update - The "doubling" did it - so don't need help and thanks anyway! :Smilie:

----------


## ChemistB

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

----------


## MrShorty

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

----------


## MrShorty

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

----------


## MrShorty

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.

----------


## JeteMc

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

----------


## 6StringJazzer

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

----------


## MrShorty

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?

----------


## MrShorty

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

----------


## alansidman

@MrS
PQ solution applied.

----------


## MrShorty

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.

----------


## alansidman

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

----------


## alansidman

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

----------


## 6StringJazzer

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

----------


## bebo021999

This is my situation  :Frown: #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.

----------


## AliGW

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

----------


## jeffreybrown

Could somebody please lend a hand here.  Thank you!

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

----------


## bebo021999

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...

----------


## alansidman

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

----------


## MrShorty

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

----------


## jeffreybrown

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

----------


## davesexcel

Hello,
Can any Mac people help with this?

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

----------


## Arkadi

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

----------


## FDibbins

Got an interesting 1 here, anyone feel like taking a look?
https://www.excelforum.com/excel-gen...side-down.html

----------


## :) Sixthsense :)

Any suggestions to fix the issue will be very much helpful  :Smilie: 

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

----------


## :) Sixthsense :)

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

----------


## AliGW

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.  :Smilie:

----------


## FDibbins

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

----------


## Greg M

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

----------


## ChemistB

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

----------


## Haseeb A

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.

----------


## ChemistB

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

----------


## AliGW

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

----------


## JeteMc

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

----------


## jeffreybrown

Could somebody take a look at this?

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

----------


## davesexcel

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

UDF won't kick in when opening workbook

----------


## alansidman

Cannot determine why this works for all but row 4.

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

Any thoughts here.

----------


## bebo021999

Anyone willing to learn?

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

----------


## JeteMc

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

----------


## GeoffW283

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.

----------


## bebo021999

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.

----------


## Sam Capricci

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.

----------


## godawgs

Not sure I understand the is happeing in this thread

----------


## alansidman

*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

----------


## alansidman

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

----------


## alansidman

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

----------


## JeteMc

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

----------


## GeoffW283

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?

----------


## MrShorty

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

----------


## ChemistB

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

----------


## FDibbins

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?

----------


## FDibbins

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

----------


## JeteMc

Hoping someone with knowledge of VBA could look as the OP is trying to conditionally color the worksheet tab.
https://www.excelforum.com/excel-gen...und-color.html

----------

