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

## MrShorty

Am I allowed to start a Call in the Cavalry thread for the new year? In the spirit of "it is easier to ask forgiveness rather than permission", I'm going to start a Call in the Cavalry thread for the new year.

I do not have the business/financial know how to advise this OP on calculating interest on a payment plan. XIRR() seems like the obvious first choice, but I do not know if it is really applicable to the specific scenario being presented. Anyone with knowledge of such accounting issues contribute some financial expertise?

Edit because, like an idiot, I failed to include the link to the thread in question: https://www.excelforum.com/office-36...ease-help.html

----------


## AliGW

Thread now stuck!

You've not provided a link ...

----------


## MrShorty

This is on a MAC, and the OP is having trouble array entering the formula: https://www.excelforum.com/excel-for...ml#post5037299 I think I have described the problem correctly, but perhaps someone who works on Macs can confirm and/or clarify how to array enter array formulas.

----------


## FlameRetired

A conditional formatting problem has me stumped.

I almost never venture into these specialized areas of CF. 
OP wants to apply icon sets to two merged cells. Even after un-merging them I can't find a solution.
Also CF rules for his criteria don't seem doable for icon sets. Hope I'm wrong.

Conditional Formatting with a twist

----------


## 63falcondude

Solver issue here:
https://www.excelforum.com/excel-for...ml#post5041509

I believe that I answered the original question but upon changing one of the cells that holds the constraint, a linearity error comes up.

I don't understand the 3 different solver methods enough to say why. Evolutionary seems to work.

*Edit*: I believe O.P. is happy with the result using the Evolutionary method.

----------


## davesexcel

Anybody understand what is going on here??
https://www.excelforum.com/excel-pro...lumn-data.html

----------


## jason.b75

Had what I thought was an easy follow up question until I realised I had misread it.

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

OP is looking to identify the earliest possible combination of 3 distinct rows. The rows identified should be unique to each other, not the whole table (this was the bit I misunderstood).
Follow up question starts at post #4, potential curveball identified in post #13.

----------


## Sam Capricci

This person needs some help and the post doesn't seem to be drawing much attention AND, I am in the middle of reports at work so don't have the time.
https://www.excelforum.com/excel-gen...and-group.html
What has me a bit concerned is that he's PM'd me twice wanting to work only with me and asking about the business I am in, this might be completely innocuous but maybe not so you might want to be careful.

----------


## AliGW

If he PMs you again asking for help personally, please report the PM using the Report button and we shall deal with it, as it's against this forum's rules and code of conduct.

----------


## FDibbins

Any VBA members feel like taking a look here please?
https://www.excelforum.com/excel-pro...tra-error.html

----------


## FlameRetired

OK.
Got in over my head again.

OP has a counting mission. I can not seem to interpret the criteria.


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

----------


## davesexcel

Anybody want to tackle this?

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

----------


## alansidman

Anybody up for this OP's situation?  I originally thought that Power Query was the answer, but now I am not sure,  perhaps a complex VBA?  I am at a loss.

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

----------


## Sam Capricci

checking to see if someone wants to look at this person's issue.
https://www.excelforum.com/excel-for...ice-value.html
he has PM'd me twice about helping him.  I told him first time that I don't open macro enabled sheets often d/t work firewall issues.
then he uploaded a .xls sheet and I told him I can't open those because I get an error notice if they are from outside sources, even if I try to save them first or change the extension.
anyway, I have no idea why he came to me but hopefully someone can help him.

----------


## 6StringJazzer

> checking to see if someone wants to look at this person's issue.
> https://www.excelforum.com/excel-for...ice-value.html
> he has PM'd me twice about helping him.  I told him first time that I don't open macro enabled sheets often d/t work firewall issues.
> then he uploaded a .xls sheet and I told him I can't open those because I get an error notice if they are from outside sources, even if I try to save them first or change the extension.
> anyway, I have no idea why he came to me but hopefully someone can help him.



This guy PM'ed me too. People are not really supposed to do that.

----------


## Sam Capricci

> This guy PM'ed me too. People are not really supposed to do that.



well then, it really wasn't for my expertise {what expertise} (you can't imagine how hard I'm laughing as I write that)!   :Smilie:  :Smilie: 
he quit PM'ing me so that is good.  Don't think he's gotten any help yet.

just checked and didn't realize there is a forum rule about that, good call to whomever thought about putting that in.  :Wink:

----------


## Sam Capricci

Wow, I'm back again on this post.
This time this person appears to need VBA to "move" what is in one cell in one area to another area when a date is entered.
https://www.excelforum.com/excel-for...own-sheet.html
thx.

----------


## TMS

Anyone want to have a go at this? I am at a loss.

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

----------


## FlameRetired

Anyone VBA literate? I am not.

This OP has posted several self-attempts at solving their own problem. It is Xposted (now with link) and no response there yet.

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

----------


## FlameRetired

This appears to be a very specialized question.  :EEK!: 

Can't tell if this is a math question or a finance formula question.

 ODDFPRICE function solved mathematically

----------


## davesexcel

Can anybody help with this, I am not sure what the OP is asking

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

----------


## FDibbins

Have a strange 1 here...
https://www.excelforum.com/excel-new...rge-excel.html

3 cells merged with text in them.  Unmerging sees the test repeated in the 2nd row.  Re-merge, change the text to something else, unmerge and the initial text still shows in the 2nd cell  :EEK!:

----------


## AliGW

Anyone want to chip in here? I have to go. I am sure it does not require VBA, but the OP needs to give some clarification about what's going on.

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

Thanks.  :Smilie:

----------


## MrShorty

OP is using the built in "box plot" chart available in newer versions, but claims that the box plot is calculating the wrong median. I cannot see the box plot in my older version of Excel. Can anyone with a newer version of Excel look at the sample and comment on the question? https://www.excelforum.com/excel-cha...ml#post5074706

----------


## davesexcel

> OP is using the built in "box plot" chart available in newer versions, but claims that the box plot is calculating the wrong median. I cannot see the box plot in my older version of Excel. Can anyone with a newer version of Excel look at the sample and comment on the question? https://www.excelforum.com/excel-cha...ml#post5074706



Won't work for me, references errors. 365 at work.

----------


## TMS

Anyone with a Mac help out here: https://www.excelforum.com/excel-pro...-on-a-mac.html

I've tried to help with links to relevant sources but I cannot answer Mac related questions.


Update: between us (me and the OP) we've muddled through and he has a solution based on code at Ron de Bruin's web site.  Sadly, no takers from the community so maybe not many folk have Macs  :Roll Eyes (Sarcastic):

----------


## Melvosh

I hope I'm not breaking any rules by posting here, being relatively low on the forum hierarchy  :Smilie: 

This person needs help with data form VBA (I think), which I am woefully unfamiliar with: https://www.excelforum.com/excel-for...t-comment.html

----------


## JeteMc

I feel that this thread lends itself to a VBA solution.
https://www.excelforum.com/excel-pro...ml#post5081172
Seems to me that the code should start with cell AF1
It should check the values of each cell in that column row by row until it finds one that has an absolute value that is larger than the tolerance +/- 0.005
Once that value is found the code should delete the corresponding row's cells in columns G:AE (if AF is positive) or AG:DA (if AF is negative) 
The deletion should be such that all of the cells in the columns below the deleted cells move up as would happen if the cells were selected and 'Delete Cells' were chosen from the cells pane on the home tab
The code would then need to copy the formula from cell AF1 down that column again, as the deletion will cause the cell in column AF of the row where the deletion takes place to produce a #REF! error
The code would then start from AF1 again looking for a cell in the column that has an outside of tolerance value
The code should terminate if either the cells to the left or right of an out of tolerance cell in column AF are blank or if it finds that there are no more out of tolerance values in column AF.
Of course as many of you will already know I am VBA illiterate, so I may assume that VBA is able to do something that it can not.

----------


## JeteMc

I feel as if I have seen a number of threads regarding this topic and that they are solved using VBA.
https://www.excelforum.com/excel-for...ml#post5081974
The OP needs to import data from one of multiple workbooks into a workbook that is used to make a report of some of the data.
Not sure that what I have done with formulas will be of any help, however the formulas in post #6 of the thread are the ones that work with the sample files provided by the OP.
Reading post #7 in that thread, makes me feel as though VBA is a better approach.

----------


## shg

Rep for anyone that helps this guy out: https://www.excelforum.com/excel-pro...-recorder.html

----------


## jeffreybrown

I have to run out for the next few hours and struggling here on this VBA.

I've read about how the mod in VBA is not like the worksheet function, so not sure how to apply multiple formats requested.

----------


## 6StringJazzer

> Rep for anyone that helps this guy out: https://www.excelforum.com/excel-pro...-recorder.html



I am working on this but he is asking for a lot for free.

----------


## ChemistB

Braindead on a Friday afternoon

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

----------


## XLent

I'm apparently missing a basic requirement here and so hoping someone can offer a fresh pespective?

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

----------


## JeteMc

Is there a code (VBA) writer that could take a look at the following. OP wants some specific [conditional] formatting i.e. thick outline around certain cells and also have text horizontally centered across certain cells.
https://www.excelforum.com/excel-pro...ml#post5091628

----------


## MrShorty

This guy is trying to do some linear algebra involving quaternions and vectors and rotations/translations and other stuff that I know I could figure out with enough time and refreshing my memory. I seem to recall a couple of you who had done this before, so I was hoping you could chime in and help.
https://www.excelforum.com/excel-gen...d-a-plane.html

----------


## ben_hensel

Struggling to come up with an answer to:
https://www.excelforum.com/excel-gen...he-ribbon.html

Where my answer isn't just "no, also, this is casually the worst idea I've ever seen on this board".

----------


## jeffreybrown

Anybody have a better formula solution here?

----------


## jeffreybrown

I seem to be hitting an afternoon wall on formulas, but that is not all that unusual.

I have this Sumifs working with multiple OR criteria in a Sumproduct, but now the OP has asked if the criteria value can be read from a range.

I know the Sumproduct will allow the Sumif to use the OR, but can't seem to figure out the ranges.  Any thoughts?

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

----------


## MarvinP

I can't seem to convert Text to Values using a Power Query conversion.  It is in the Debit and Credit columns of the Append table.

What am I missing on this thread?  I changed the source type on both so they might convert with no success.  HELP!??

https://www.excelforum.com/excel-for...t-working.html 

Love some help.

----------


## davesexcel

Anybody want to tackle this? I'm focusing too much on the title and not the actual question.

https://www.excelforum.com/excel-pro...rk-unlock.html

----------


## Melvosh

Not sure how to accomplish this one.

User is looking to match any maintenance item in a short list in one range to data in another sheet based on vehicle number, and return the date from the first match.  This needs to be done for various maintenance periods (weekly, monthly, 6 months, etc.).  There are items in each list that don't appear in the bigger data set, and items in the data set that don't appear in the lists.  Example workbook with expected results is attached in the first post.  Any assistance would be appreciated!

----------


## JeteMc

I believe that the OP is looking to produce a permanent time stamp when a worksheet change event occurs in an corresponding cell. I know very little about VBA but have seen this kind of thing discussed as doable.
https://www.excelforum.com/excel-gen...ml#post5102530

----------


## Sam Capricci

anyone have anything for this long-time member?  puts this in the cell =A5*1400*(9/7) and the formula bar shows it, then half the time it changes it to this...
=A5*1400*(1.28571428571429)
I can't recreate it and couldn't find anything relevant searching.  
https://www.excelforum.com/excel-gen...rmula-bar.html

----------


## jeffreybrown

Anybody have some VBA thoughts to offer here?

----------


## AliGW

Anyone fancy a stab at this?

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

It's not a regular list extraction - see the latest workbook sample attached to the post linked to.

I'm experiencing brain freeze on this one!

----------


## FlameRetired

OP wants a calculated field to Pivot table to count 1s in two fields of the source data for each 'Name' in a third.
OP is unable/unwilling to add helper column to source data.

I am not sure this is doable with PT.

Anyone? 
https://www.excelforum.com/excel-for...ml#post5111903

*Edit* Forgot to mention OP profile is Excel 2010.

----------


## jeffreybrown

Can I get an assist here?  I'm must be missing something.  Thanks.

----------


## FDibbins

This looks like an interesting Q for the VBA boffins here?
https://www.excelforum.com/excel-gen...t-working.html

----------


## alansidman

Here is a challenge for you.  I cannot get it to work for me.  If you need to have the PQ results to get the file normalized, PM me and I will post it with the file for you.

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

----------


## Melvosh

This is a weird one.  OP is having trouble clicking on the proper cell, and Excel seems to be blurry when that occurs.

https://www.excelforum.com/excel-gen...ll-i-want.html

----------


## Melvosh

2nd one today.  Guess my brain is on vacation  :Roll Eyes (Sarcastic):   This one needs either a math guru or an interpreter, not sure which.

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

----------


## 63falcondude

OP does not want to manually insert formulas so I believe this requires VBA:

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

*Edit:* It doesn't sound like they want a VBA solution either. Not sure any solution will be approved by them but I'll leave this here anyway.

----------


## davesexcel

> OP does not want to manually insert formulas so I believe this requires VBA:
> 
> https://www.excelforum.com/excel-gen...ple-sheet.html
> 
> *Edit:* It doesn't sound like they want a VBA solution either. Not sure any solution will be approved by them but I'll leave this here anyway.



Could you suggest grouping the sheets, then entering the formula in the grouped sheets?

----------


## MrShorty

I thought this thread would be about pivot tables and charts: https://www.excelforum.com/excel-cha...ive-chart.html However, the OP says that he/she is already pulling data from an Access database into Excel and wants to make it more "interactive". Since I know nothing about querying an access database from Excel, I'm calling in the Cavalry.

----------


## ben_hensel

I think that one will be really tough, in that it will be hard to give OP what they want, because they don't seem to even know what they want.

----------


## alansidman

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

OP looking for a Formula Based Solution.  I have provided a workable VBA.  This is beyond my Formula Skills.

----------


## TMS

Anyone have a look at this please. I'm in hospital with only an iPad and I won't get near a computer until some time over the weekend. And, even then, Im not sure how well INDIRECT works with closed files.

*Have a cell that is linked to another workbook change*

----------


## 6StringJazzer

> Im not sure how well INDIRECT works with closed files.



It doesn't. At all. I posted to the thread but just for background there are three possible solutions:

Write VBA to open the file and read it instead of using INDIRECTUse an add-in that provides a UDF called INDIRECT.EXT, which uses VBA to open the file and read itUse VBA to write the formulas with direct external references

Note that theme that VBA is required. Only the third solution avoids the overhead of opening files.

----------


## 6StringJazzer

> I'm in hospital



By the way, sorry to hear you are in hospital, cheering you on for a quick recovery and to get back home.

----------


## XLent

new insights required...

https://www.excelforum.com/excel-gen...of-a-cell.html

----------


## alansidman

I can't come up with a VBA solution here and it has been years since I played in DOS.  If you can offer a suggestion.....

I believe the answer should be in the .txt file but maybe you have a means to do it in Excel.

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

----------


## TMS

@6sj: 



> ... sorry to hear you are in hospital,...



Thank you. I've had both knees replaced following several years of pain which was progressively getting worse. Now I have a different pain, but one which hopefully will ease over time. Thanks for your kind thoughts  :Smilie:

----------


## jeffreybrown

Richard might come up with the answer, but in the meantime, anybody with the formula chops want to tackle this?

----------


## alansidman

@TMS 
My wife just had her left knee done and is seven weeks post op.  Having watched her for the past seven weeks, I know that your recovery will be quick and you will be amazed at how well you will be able to move.  PT is a lot of work.  Don't cheat on it as it is critical to your continued success.  Good Luck with the new knees.

Alan

----------


## TMS

@Alan: thank you for your kind words and thoughts. I have a good start, can get both legs straight, and excellent bend on both sides. But I know I need to work on it!

----------


## Melvosh

@TMS

Happy to know you're doing better, and I hope your recovery is quick and successful  :Smilie:

----------


## TMS

@Melvosh: thank you, much appreciated  :Smilie:

----------


## AliGW

Anyone any good with generating links to share files on Sharepoint via VBA? Could be a mail merge to E-mail.

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

----------


## GeoffW283

Poker anyone?

Here's a potentially interesting and novel problem for any poker players out there.  It involves ordering poker hands in a very specific way.  I got somewhat close but not close enough for the OP and I am unable to see any way forward either by formula or VBA.

https://www.excelforum.com/excel-for...ulas-like.html

----------


## 6StringJazzer

Very large file (34MB) with data only, no formulas, CF rules removed, but Excel CPU% spikes into the 90s for no apparent reason when merely scrolling the screen.

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

----------


## AliGW

Anyone fancy having a go at understanding what the OP is trying to do here: https://www.excelforum.com/excel-for...setpoints.html

----------


## AliGW

Another one: https://www.excelforum.com/excel-for...ry-lookup.html

----------


## jeffreybrown

I've tried do some researching on this, but I'm not winning.  Any takers?

I found this, but again, I must be using it wrong because it did not help.  I managed to get the last row of a filtered set of data, but couldn't get the font to color.

----------


## Olly

Anyone able to do a quick sanity test of this post, please: https://www.excelforum.com/excel-pro...ml#post5133205

I can't recreate the OP's issue.

----------


## jeffreybrown

Could somebody please sanity check this thread for me?  Thanks.

----------


## davesexcel

Anybody understand this?
https://www.excelforum.com/excel-pro...ast-digit.html

----------


## Olly

> Anybody understand this?
> https://www.excelforum.com/excel-pro...ast-digit.html



Worryingly, yes, I think I did...  :EEK!:

----------


## FlameRetired

When importing data and refreshing wb OP wants a way to retain sheet formatting. 

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

----------


## MrShorty

I think the solutions I have provided can work, but I expect the OP would much rather have a nice array formula based solution: https://www.excelforum.com/excel-for...h-vlookup.html It involves the XIRR() function which does not like the usual array formula "replace the unwanted data with boolean/text and the main function just ignores the boolean/text" approaches. I seem to recall some of you having a different array formula technique that can completely remove the unwanted data from the input arrays. I expect that any of your solutions will be a lot easier if the OP will add the final valuation records to the XIRR data like I describe in the pivot table approach I outline in post #5.

----------


## davesexcel

> I think the solutions I have provided can work, but I expect the OP would much rather have a nice array formula based solution: https://support.office.com/en-us/art...b-a303ad9adc9d It involves the XIRR() function which does not like the usual array formula "replace the unwanted data with boolean/text and the main function just ignores the boolean/text" approaches. I seem to recall some of you having a different array formula technique that can completely remove the unwanted data from the input arrays. I expect that any of your solutions will be a lot easier if the OP will add the final valuation records to the XIRR data like I describe in the pivot table approach I outline in post #5.



What is this referring to?

----------


## MrShorty

> What is this referring to?



 My mistake, wrong link. This thread: https://www.excelforum.com/excel-for...h-vlookup.html (correcting the previous post, too).

----------


## 6StringJazzer

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

User has set up a worksheet with a couple of pages of forms. There are ActiveX checkboxes that cause some rows immediately below to be populated or cleared. If the checkbox appears in the top 1" of the page in Page Layout View, the checkbox behaves erratically or doesn't work at all. The code is fine.

This is either an Excel bug or a file corruption but I am not sure how to proceed.

----------


## FlameRetired

I have never encountered this before.

=isodd(combin(25,10)) returns TRUE.

combin(25,10) = 3,268,760

Have tried CSE, coercion, Fx. Fx shows no different.  :Confused: 

Any ideas?


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

----------


## ChemistB

@Flameretired  Post 84
Weird glitch.  I even copied and pasted values of COMBIN(25,10) and ISODD(Cell1) still said TRUE.
Then entered 3,268760 in another cell and that's FALSE
then entered the equation Cell1 = Cell2 and that came out TRUE

----------


## rorya

The value Excel is actually storing as the result is 3268759.9999999995. No idea why though.

----------


## Paul

I'm guessing it's because Excel only stores up to 15 significant digits?  Once you go past 17!, Excel starts replacing digits with zeros.

In the case of 25!, the true value is 15,511,210,043,330,985,984,000,000, while in Excel it's 15,511,210,043,331,000,000,000,000.  The denominator of the COMBIN function [(n-r)!*r!] also exceeds 15 significant digits at ~4.74e+18.   It seems like a large number is being lopped off, but that portion is of little significance when you're looking at the numerator (~1.55e+25) and denominator (~4.75e+18).

----------


## FlameRetired

It seems strange (at least to me) that MS didn't do a final rounding step when it developed this function.

----------


## MrShorty

I don't know how badly this needs the cavalry to come in, but I am kind of curious, too. The OP is asking about a new "spill" feature in the newest version of Excel that may be hidden behind a specific Office 365 subscription wall. I proposed a simple UDF to test with, and was just wondering if anyone with the proper 365 subscription could run the test and tell us if this spill range feature works for VBA UDFs: https://www.excelforum.com/excel-pro...ersection.html

----------


## ChemistB

This is a supply chain type of question and I am not sure exactly what the OP is asking for.

https://www.excelforum.com/excel-gen...d-on-fifo.html

Thanks in advance

----------


## JeteMc

Trying to help the OP use Power Query to append tables. I feel as if some of the steps that I took to append the tables could be consolidated using the Power Query Advanced Editor and that some of the contributors here could be of more help in that regard than I.
Note that the file's summary sheet lists 87 tables that will need to be appended although the sample file only contains two.
https://www.excelforum.com/commercia...ml#post5146002

Edit: This is a commercial services thread.
Update: The OP has appreciated the thread.

----------


## AliGW

*FAO: Olly*

Could do with your PQ expertise/advice here: https://www.excelforum.com/excel-pro...ml#post5146460

----------


## Olly

> *FAO: Olly*
> 
> Could do with your PQ expertise/advice here: https://www.excelforum.com/excel-pro...ml#post5146460



Done  :Cool:

----------


## jeffreybrown

Could use some help here?

----------


## 63falcondude

I could keep going here but I feel like I am wasting my time.

----------


## JeteMc

The linked thread is a "populate a calendar from a list" type request, and I imagine that a calendar similar to the one the OP wants already exists.
https://www.excelforum.com/excel-for...ml#post5150708

----------


## AliGW

Data layout disaster - anyone fancy helping this member to get his data into a usable and unbreakable format? I'm off out, so can't help just now.

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

----------


## 63falcondude

Could use help here.

OP wants to combine two workbooks with different structures based on two columns.

I suspect that this can be done with Power Query but it's beyond my know how.

https://www.excelforum.com/excel-for...column-id.html

----------


## CK76

My colleague with MAC laptop is on vacation and I can't test. Can someone with more MAC knowledge or access to MAC help OP?
https://www.excelforum.com/excel-pro...cript-mac.html

----------


## scottiex

Copied to the thread the code he was using for zillow and he has now provided the API ID to run it, 
but not sure if I can take it from here and don't want to leave everyone else thinking I'm working on it and him with no answer - (if someone else would otherwise have answered it)

https://www.excelforum.com/excel-pro...illow-api.html

----------


## AliGW

I am pretty sure the OP's maths is wrong here, but I am not confident enough in my own assumptions to help him. Any maths experts want to help out?

https://www.excelforum.com/excel-gen...in-2019-a.html

----------


## 6StringJazzer

*VBA implementation for CDO for sending email*

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

I started to help based on the code he showed that uses Outlook, but then he says that they decided to use CDO. It's not hard but I have not done this and don't have time to figure it out from scratch. If someone else is already comfortable with coding for CDO sendmail in VBA please feel free to step in.

----------


## AliGW

The OP of this VBA thread is not sure why they arenÂt getting any responses. Could a VBA expert take a look, please?

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

----------


## jeffreybrown

Not sure I'm understanding this.  Any helpers!

----------


## Olly

> Not sure I'm understanding this.  Any helpers!



The lovely joys of PowerPivot and DAX  :Cool:

----------


## AliGW

Potential for a PQ solution here, but I've run out of time. Anyone?

https://www.excelforum.com/excel-gen...ells-h-mm.html

----------


## jeffreybrown

Thanks Olly for the help.  Yes, this is an area I have not even waded into.

----------


## Sam Capricci

I think this person needs a VBA solution to their issue (and if so maybe the post moved there too).
https://www.excelforum.com/excel-for...-invoices.html
I'll be interested in seeing if anyone has a formula solution for the OP.

----------


## AliGW

I've hit a brick wall with this one: https://www.excelforum.com/excel-for...-criteria.html

The OP has rejected the solution that gave wthe icon sets they wanted as too fiddly. They now want a cell fill solution that does not involve helper columns. I'm not managing to make the lookup needed in the CF rule work. Anyone able to step in?

----------


## AliGW

VBA query: something to do with checkboxes controlling macros. The OP is understandably perplexed at getting no response: it looks as if the detail is there. Can a VBA expert take a look, please?

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

----------


## jeffreybrown

Unfortunately I just got called away, but at the same time, can't see this one.

I believe other parts of the spreadsheet are missing, plus cell F14 points to B18 which is really nothing.  Thanks.

----------


## alansidman

In this thread, look at posts 7, 8 and 9.  https://www.excelforum.com/excel-pro...me-folder.html

Firstly, this is a Power Query Issue Consolidation of Excel Files issue.  If you filter the appended file (Book8) for the indicated spreadsheet, you will see that the columns C & D are imported as nulls.  Yet they contain data and if you Load this file on its own and not part of a consolidation, it will populate those columns.  I am baffled by this.  Do you see anything in the original file that will cause this aberration.

----------


## FDibbins

Anyone feel like taking at look at this 1?
https://www.excelforum.com/excel-pro...ml#post5169679

"add-existing-image-from-excel-sheet-to-the-body-of-an-email"

----------


## MrShorty

It is not yet clear, but this one is probably going to involve the newer waterfall chart type in the newer versions of Excel that I don't have access to. Can anyone familiar with the waterfall charts in the newer versions comment on what is possible and not possible in these charts?
https://www.excelforum.com/excel-cha...ml#post5172714

----------


## FlameRetired

All I am managing to do is confuse myself here.

OP prefers formula solution to a summary table.

They have consented to helper column(s) in the source data sheets and eliminated the merged cells from source.

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

----------


## Sam Capricci

I got them a "partial" answer but I think they need to restructure their data which was my last recommendation...
https://www.excelforum.com/excel-gen...rect-data.html
the problem is that the formula needs to index 8 columns to the right when they come up with a different date selection.
it appears to me to be a three way match for 17 weeks, then jumps to the right for the next set of dates and positions for the next 17 weeks.
I wonder if it needs a macro solution, it is already a macro enabled workbook.

I'll be interested to see what someone else comes up with.

----------


## alansidman

https://www.excelforum.com/excel-gen...-w-commas.html

Custom Sorting -- Gave a solution to the OP but he is interested in something less cumbersome.  I am empty on this!

----------


## Aardigspook

Strange behaviour here:  http://www.excelforum.com/excel-gene...down-list.html
Right-clicking a cell and choosing 'Pick from drop-down list' enables an entry in a cell which should not be allowed according to the Data Validation rule for that cell.  I found a sort-of workaround, but it's not great.  Has anyone seen this before / have a better solution?

----------


## JeteMc

I believe that this thread has gone beyond the capabilities of formulas. Any VBA contributors want to take a look at the last couple of posts by the OP and see if code could be written to accomplish the task?
https://www.excelforum.com/excel-gen...ml#post5183056

----------


## AliGW

Anyone interested in taking up either of these two threads? I don't have enough time today to pursue them, and don't want the OPs to feel marooned!

To do with job scheduling and calculation working hours for a job spanning two or more days: https://www.excelforum.com/excel-gen...ng-issues.html

Working out most recent five match outcomes in a football league: https://www.excelforum.com/excel-for...lude-form.html

Thanks to anyone who can step in.  :Smilie:

----------


## JeteMc

OP would like to have formulas to automate tracking of withdrawals from retirement portfolio while preserving the formulas and set up that are in place in the file attached to post #1.
I had tried to steer OP towards some formulas that I felt were more efficient, however...
https://www.excelforum.com/excel-gen...ml#post5184192

----------


## davesexcel

Anybody have time to fix the autofilter macro?

https://www.excelforum.com/excel-pro...ter-range.html

----------


## MrShorty

I can't really help with this one, since my version of Excel does not support slicers. https://www.excelforum.com/excel-pro...ing-dates.html

----------


## jeffreybrown

I don't think I have the skills to really get involved here, but if somebody wanted to take a run at it.

I think the problem stems from what started out as a formula to capture unique numbers (dates) is now bleeding over into VBA producing text results.

https://www.excelforum.com/excel-for...mn-values.html

https://www.excelforum.com/excel-pro...-2-sheets.html

----------


## 63falcondude

My brain isn't working this morning.

Does someone want to take over? Thinking it'll come down to a nested IF formula.

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

I recreated the sample (picture) and desired results as well.

----------


## davesexcel

Can someone thing of a formula solution to this problem?

https://www.excelforum.com/excel-gen...d-columns.html

----------


## jeffreybrown

Not sure I see this one as achievable in the way the OP wants.  Any ideas?

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

----------


## Glenn Kennedy

I am beginning to lose the will to live here:

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

Any takers??

----------


## TMS

Anyone have a look at this ...

https://www.excelforum.com/excel-pro...or-method.html

We've narrowed down the cause of the problem but IÂm not strong on combo boxes and their properties.

----------


## Sam Capricci

I can't help this person.  Every time I open his workbook the links cause the data to go away and the formula I offered works in his workbook as I can see it but apparently doesn't work for the OP.
https://www.excelforum.com/excel-gen...numbering.html
The cells it references contain an array formula to an external workbook.

----------


## jeffreybrown

Any suggestions here that may have been overlooked?

----------


## jeffreybrown

Macro wise, this is out of my league.  Any want to help?

----------


## jeffreybrown

Here's another one I got tangled in and don't see an easy solution.

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

----------


## alansidman

I can't see anything wrong here, but the OP insists that it does give him the results he needs.  If you can help.....

https://www.excelforum.com/excel-for...her-sheet.html

----------


## JeteMc

dflak, I don't know if you have seen this post about a 9 box performance matrix however the set up looks almost exactly like one that you gave as a solution in post #2 of this thread
I do not know VBA so I can not explain to the OP how it would work, or even know if the concept you proposed could be used in this instance, however I thought that you might want to take a look.

----------


## alansidman

Look at this link.  The OP needs a consolidation of information in a range.  The issue is that the number of sheets to consolidate for the range varies amongst a number of files.  I suspect it is a case of determining a valid array which I cannot determine.

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

----------


## Glenn Kennedy

Ahem.... I didn't notice this was a Google sheets Q..

What's a Google sheet???????*


Any takers?

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

* Only joking... I just never inhaled near one...

----------


## MrShorty

This user wants to draw with drawing shape objects in VBA-- something I have no idea exactly how to do. https://www.excelforum.com/excel-pro...ml#post5206507

----------


## XLent

https://www.excelforum.com/excel-cha...g-columns.html

OP wants to apply a calculated item on a Pivot which relies on Grouping (unrelated Date field); I don't think it can be done, but hoping someone else may know better...

----------


## Olly

> I don't think it can be done, but hoping someone else may know better...



Power Pivot to the rescue...  :Cool:

----------


## XLent

Thanks Olly - tried to add rep but, apparently, I need to share around first...
{tried to yesterday re: CUBEVALUE suggestion, in fact}

----------


## 63falcondude

Trying to use a SUM(COUNTIF( ... , {} , ... , {} )) style formula that isn't working as expected.

O.P. came up with a solution using SUMPRODUCT but I would like to know why the SUM COUNTIF formula was not calculating as we expect.

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

See post #6 for smaller sample to test on.

----------


## FlameRetired

I suspect this can be done formula wise but will require a different layout / lookup table(s).

I am stumped on how to approach that. https://www.excelforum.com/excel-gen...onditions.html

----------


## MrShorty

Some of you guys are better at writing formulas to a spreadsheet with VBA. In this one, we are writing a SUMIFS() to the spreadsheet, and I think we are having trouble with the quotation marks that are part of the formula string: https://www.excelforum.com/excel-pro...ml#post5212094

----------


## 6StringJazzer

*Migrate Excel UserForm to Access Database Form*

This is a *Commercial Services thread*.

This user has an Excel file with a UserForm that does several updates to worksheets. OP wanted this ported to Access so that the code would still update the Excel file. I imported the UserForm and rewrote the code so that Access could execute the code to update the Excel file. I used late binding. However, the user did not want it just ported as a VBA UserForm, user wants a new Access form created to emulate the same functionality. I do not know how to link actions on an Access form to VBA and it's not worth the time it would take me to learn it just for this one question. OP has valued thread at 50 points.

The Access data is almost 20MB so cannot be attached to the thread. I received the file and send a solution via email with the OP.

----------


## FlameRetired

OP put this on "hold" for about a year and are now back.

I am useless in VBA. All I could do confirm the errors they were getting. https://www.excelforum.com/excel-gen...eekends-2.html

----------


## XLent

"known" issue re: reading filter criteria on a date field, where default grouping behaviour enabled:

https://www.excelforum.com/excel-pro...ct-filter.html

does anyone happen to know of workarounds aside:

a) disabling the grouping and/or using the date filter option (in preference to grouping), or 
b) reading saved XML {per a link provide in above thread}

----------


## AliGW

*Calling PQ Experts (Olly???)*

This seems ripe for direct access to data on a website: https://www.excelforum.com/excel-for...to-a-cell.html

The OP is doing comparisons of players - I have been able to directly access some of the data he wants, but not all. Maybe someone knows how to move forward with this?

----------


## Olly

> Maybe someone knows how to move forward with this?



That was fun  :Smilie:

----------


## AliGW

Aha - my call to arms was answered! Thanks, Olly - going to have a look now, with great interest.  :Smilie:

----------


## AliGW

I seem to be experiencing complete brain fog with this one: https://www.excelforum.com/excel-gen...-on-range.html

Help!  :EEK!:

----------


## AliGW

VBA - adding prefixes to folder names based on a lookup list in Excel - can anyone help?

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

----------


## AliGW

Anyone fancy a stab at this? Non-VBA solution requested. I was trying to do it with PQ, but hit a brick wall.

https://www.excelforum.com/excel-gen...s-headers.html

----------


## alansidman

Any takers for an AverageIfs need.  No Pivot Tables! No VBA!  Formula only!  https://www.excelforum.com/excel-for...statement.html

----------


## FlameRetired

Never seen this before.

Upon downloading the attachment here there are no buttons to enable editing and the screen is blue ... as if the sheet is hidden. It's not.

----------


## AliGW

Can anyone help with a FREQUENCY question with dates? I have to go offline very shortly.

https://www.excelforum.com/excel-for...early-etc.html

----------


## jeffreybrown

Can I ask for a VBA smart person  :Smilie:  to help here?

----------


## davesexcel

Can somebody look into this lookup function, it worked originally, now seems to only work for about 7 rows.
https://www.excelforum.com/excel-for...onditions.html

----------


## davesexcel

ANybody know how to save an email?

https://www.excelforum.com/excel-pro...to-folder.html

----------


## Glenn Kennedy

This one is doing my head in.  I can get it working for one machine... but it falls over when I try and add in another.  It's probably a horrible SUM/SUMPRODUCT + MMULT concoction.

https://www.excelforum.com/excel-for...-a-lookup.html

----------


## Pete_UK

Hi Glenn,

it didn't involve those functions at all !!

Pete

----------


## Glenn Kennedy

It might, Pete.  He said he wanted the formula dynamic to cope with any number of machines.

----------


## jeffreybrown

This seems to be out of my league.  The OP wants to return a unique list, but with two variables.  The two part I don't know how.  Any takers?

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

----------


## Pete_UK

Hi Jeff,

I used a helper in Sheet1 to select the records, then a straightforward INDEX/MATCH to retrieve them.

Pete

----------

