How to write a script or any PQ or in Excel to download the zip files from a Webpage
Dear Experts, Greetings! https://www.etsi.org/deliver/etsi_ts/138300_138399/138306/ Could you please help me on how to download the pdf.zip files from above for all the versions? Using a single command in Excel or PQ-option. Thanks in Advance, Br, Anupam
Formula to retrieve data from several sheets and return values from given criteria.
I am trying to get data from each of our areas of the school staff timetables EY MY LY and summaries what days each staff member works where to another sheet, as staff move around. I would appreciate some help please. This is how far I got: =IFERROR(VLOOKUP(A10,EY!$C$3:$G$33, 2, FALSE), IFERROR(VLOOKUP(A10,MY!$C$3:$G$33, 2, FALSE), "Not Found")) This just returns; I tried to add in the same formula for LY but it is saying too many arguments, this is why Michelle is Not Found. Will I also be able to return the area they are in on each of the days ? Any assistance would be very much appreciated.Please update Excel to handle more than 15 digit numbers!
I'm a professional database designer, and this limitation has caused me no end of headaches. The problem: numbers that are more than 15 digits long have all digits after the 15th converted into zeros. Microsoft provided workaround: format the the field as text. This workaround is only useful if you are doing data entry directly into an existing Excel spreadsheet that you are able to format the cells ahead of time. It's incredibly unhelpful when you are pushing and pulling data from different data sources. For example, if I need to push data out of a program like FileMaker to Excel, and the system I am pushing from has numeric fields (in particular ID fields), excel sees them as numbers and replaces the digits after the 15th. This jacks up formulas as well as any data synching possibilities. In situations like that, I end up having to export the data as .csv file, and then pull it in to an excel sheet and then do a bunch of conversion options on import (which does not always work, btw). But this is not a practical solution for every day users who are not tech savvy. I should be able to export the data directly to excel without the data being converted. This has been a big problem for every client I have that requires data being pushed to Excel. And this problem has existed for decades. Other spreadsheet programs (like google sheets) do not appear to have this issue. Can someone please explain why Excel continues to cling to this archaic standard? Are there any plans to update Excel to handle more than 15 digits? I know I am not the only one who has run into this problem. I've seen all kinds of posts about it. But trying to let Microsoft know how much of a problem this is has been a challenge. Their article on the subject had a link to give product feedback, and that link led me to this space. So here it is: product feedback for Excel. PLEASE FIX THIS! Thank you. Chris
TOCOL/TOROW Treat Thunks as Errors
I just reported a bug to Excel in which the TOROW and TOCOL functions, if asked to delete errors, will also delete valid thunks. ISERROR returns FALSE when presented with a thunk, so, arguably, TOCOL shouldn't treat it as an error either. I'm running Office 365 on Windows 11 on a Samsung Yoga laptop. Steps to reproduce: Paste this into a single cell in a spreadsheet: =LET(th, VSTACK(LAMBDA(4)), (@TOCOL(th,2))()) Expected behavior: should return 4. (Delete the ",2" and it does exactly that.) Actual behavior: #CALC error Impact: Breaks workflows for SCAN, MAP, BYCOL, and BYROW Workaround: Use FILTER and ISERROR Details: An Excel "thunk" is a degenerate LAMBDA with no parameters. So if we used LET to make f equal to LAMBDA(4), then f() would always return 4. If a thunk is the final result of a function, it generates a #CALC error, but it's fine for intermediate results. This seems useless, but it's the only way to get functions like SCAN, MAP, and BYROW/COL to return anything but a scalar. For example, I have a piece of code where I need to repeatedly square a large matrix and save the values for further processing. If I get a zero value, I can save a lot of processing by "aborting" the operation. Since you can't abort a SCAN, I just return #NA. Then I'd like to use TOCOL(result, 2) to strip off the unnecessary values. But TOCOL discards everything. I can work around this by using a combination of ISERROR and FILTER, but I shouldn't have to.Advanced Excel Formula discussion - Problem with dynamic range
Let's assume I want to rent a house. For each house, if the landlord has an agent, I'll contact the agent; If not, I contact the landlord directly. As below: Landlord Agent House Landlord James Mary W James Linda Michael X David Y Linda Z James Column F: =IFERROR(XLOOKUP(E2:E5,A:A,B:B,E2:E5),E2:E5) This is correct Column G: =XLOOKUP(E2:E5,A:A,B:B,E2:E5) #VALUE! for X-David Column H: =XLOOKUP(E2:E5,A:A,B:B,CHOOSECOLS(E2:E5,1)) Wrong value for X-David, because it returned the first value in range of XLOOKUP([if_not_found]) field My question is: the only difference between G and H is CHOOSECOLS(). If I put =CHOOSECOLS(E2:E5,1) or =E2:E5 in a separate cell, it gives me same result. However, in XLOOKUP, they are recognized differently. What is the logic behind this? Thank you for spending time on reading this.
Issues With PowerQuery Using 2 Tables with different Data Sets
Im having issues using power query when I try to use 2 tables to make a pivot table. I am using a sheet that pulls information from a system I use for maintenance. The first screenshot is a subset that breaks down sub items for a main "work order". The next 2 screenshots are from a separate table that show the main "work order" and has an "Equipment Pool" that shows who owns the vehicle in question. When trying to create a pivot table, a lot of information shows as blank. (see bottom screenshot) I am wondering what I am doing wrong for it not to be able to pull the "Equipment Pool" when it aligns to the "Asset ID". Any help is appreciated. (For security reasons I have not put all information from the tables, only the relevant ones)
