Search results

That depends on how often you might want to change things. If it's only very occasionally then we can change those things manually, otherwise it'll be a case of writing it in to the code - perhaps a bit like the path.

That's odd. Are you using the web version of Excel, or perhaps you're on a Mac? I don't know. What version of Excel are you using? OK, try this:

No macro, no vba (it's not a .xlsm file). Right-click the results table, choose Table, then Edit Query…

Was this after trying the new file in msg#4 or before?

1. No need to. You can, but do not delete the entire table. 2. Yes 3. Yes.

My fault. I have updated the attached file in msg#4. Please try again.

That error dialogue shouldn't be trying to look at …\Chandoo57767… it should be looking at what's in cell A13 via cell A13 being a named range in Name Manager called Path. Have you changed something there? Attach you failing file here so I can check for changes?

Be absolutely sure that the path is correct, with no leading/trailing spaces and no final slash character. Copy/Paste that folder path from somewhere if you can. If you still can't make it work, could you quote the actual path here?

In the attached, table at cell A14 of FinalResults sheet. I think that values in cells D9 and F9 should be swapped? I put your 3 csv files into a folder on my system called C:\Users\Public\Documents\Chandoo57767 by themselves and that folder path is in cell A13, which is a single-cell named...

=LET(a,IFERROR(CHOOSE({1,2,3},5*4,10*1,22*0),0),AVERAGE(FILTER(a,a<>0)))or=LET(a,IFERROR(CHOOSE({1,2,3},5*4,10*1,22*0),0),AVERAGE(IF(a<>0,a))) Note that 22*0 does not raise an error. Do you really have formulae with values manually typed in like that?!

Me too. Can you confirm which table you want to add a column to? Looking at your data, there's not a single matching 8-character string between the tables, so I haven't the foggiest what I'm looking for.

Yes! I discovered I'd acquired GROUPBY and PIVOTBY only today - I don't know how long I've had it; a week or two perhaps.

fnStuff is my lack of imagination when naming a function! In the attached, I've added a second group of queries called develop function. In it I've shown 2 versions of the query fnStuff (2) and fnStuff (3). fnStuff (2) is the function converted (back) into a plain query fnStuff (3) is the same...

I don't understand the above sentence! The equivalent for ALL in the slicers is the red cross (which means no filtering):

Note the last message was updated just now.

Test: Function FindCodes(ProdCode, Codes) Set dict = CreateObject("Scripting.Dictionary") dict.CompareMode = vbTextCompare RawCodes = Codes.Value 'Filter out blank codes: Count = -1 ReDim Codes(-1 To 0) If IsArray(RawCodes) Then For Each RCode In RawCodes x = Application.Trim(RCode)...

Why re-invent the wheel?

In the attached, run the macro blah. ps. there is no sheet Travel

For Excel2016 I can only suggest a UDF: Function FindCodes(ProdCode, Codes) Dim result As String For Each code In Codes.Cells If InStr(1, ProdCode.Value, code.Value, vbTextCompare) > 0 Then result = result & "+" & code.Value End If Next code If Len(result) > 0 Then result = Right(result...

Try in cell BR2:=TEXTJOIN("+",TRUE,FILTER(Classification!$G$3:$G$5,ISNUMBER(SEARCH(Classification!$G$3:$G$5,Sale!L2)),"STOP"))and in cell BS2:=TEXTJOIN("+",TRUE,FILTER(Classification!$G$3:$G$5,ISNUMBER(SEARCH(Classification!$G$3:$G$5,Sale!X2)),"STOP"))both copied down. Cell BR8 has the only...

I looked at this and didn't reply because my solution was so convoluted that I thought there must be a more elegant formula (I still do!). @mohamed ilyas' solution made me think that my formula wasn't so bad! So in the attached, on Sheet1 there's a single-celled formula at cell D2 (with hints...

Then point no.2 is the way to go.

1. If the spreadsheet is in the same workbook as the Power Query query it can read updated Tables (proper Excel Tables, not just ranges that look like tables) and defined/named ranges. [MCode starts Excel.CurrentWorkbook()] 2. If the spreadsheet is in a different workbook from the workbook which...

I have a 4 year old car. I need to travel 2,000 miles. I'm going to cycle.

Then Power Query is the way to go. I'm not going to re-write what is already a complicated formula into old versions of Excel. These formulae would be totally unmanageable. In the attached a Pivot Table at cell F2 of the Total sheet based on your source data transformed by Power Query...