excel
44900 TopicsFormula 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.324Views0likes12CommentsSlicer
Hi Expertises, Need your help again. I have pivot table as below. I need to capture the Product A sales amount under the following scenarios. Filter. Select any one country in slicer NO Filter. Select all country My problem is when I add formula in text box like =B2, under scenario 1, the answer is correct no matter I select which country in slicer. However, for scenario 2, when i did not use filter, the total of Product A Sales amount should refer to B6 instead of B2. So, i got incorrect answer. May i ask your advice in this case? Thanks a lot Regards Lawrence Country Product A Sales Product B Sales AU 100 40 JP 80 60 HK 60 80 KR 40 100 Total 280 28032Views0likes2CommentsCountif problem
Hi - could someone advise please? I have a workbook and am trying count the number of "Apples" in Col F, using $F$3:$F$81,"Apples" as the second criteria range and criteria. The first range and criteria is column B "Column1" is has mmm-yy and goes back a couple of years from Apr-24. I have tried COUNTIFS($B$3:$B$81,[@Column1],$f$3:$f$18,"Apples". Just doesnt seem to want to work. Any ideas? perhaps mmm-yy is an issue? thanks Noyman64Views0likes3CommentsNeed quick help counting values from a list for presentation.
Hi All I could usually work this out by searching google and the forum if I had time but its run out so looking for some quick community assistance to get me through the day. I need to count how many times we went to specific locations that are "In area". there is a list of 47 locations and 13 of them are considered "In area" I have a list of jobs in sperate sheets named based on years (2024, 2025, 2026) in those sheets I have a column that lists the location the job was in, which is pulled from a list of locations as a list in the sheet "DataLists". Each year there are approximately 250 jobs and of that 150 are probably in area. In the DataList sheet where the list of locations are pulled from, next to each location listed in column E I have a 1 or 0, in column D, 1 for "in area" and 0 for "out of area". I also have a "Report" sheet that generates a report based on the selected year as a drop down in cell C2 I want to count how many times we went to jobs the were "in area". I tried a quick solution to get me through today and generated a new list in column Q in the DataList sheet with: =IF(D2=1,E2,"") this made a list of only "in area" locations in column Q. Then in one of the year sheets I tried the following formula =SUMPRODUCT(COUNTIF(H3:H200, DataLists!Q2:Q47)) where H3:H200 is the locations of the jobs and DataList!Q2:Q7 is the list of "in Area" and got 3335 which is very wrong as there are only 97 jobs so far in 2026 and maybe 50-60 of those are "In Area" I was going to then expand it to the following in the "Report" sheet. =SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$2&"'!h:h"),DataLists!Q2:Q50)) so it pulls the data from the selected year entered into cell c2 What am I doing wrong here, am I using the wrong function. Sorry only have a few hours before presentation. Ideally I would like the formula in the "Report" sheet, to sum the total number of occurrences from the "year" sheet column H:H for any match from "Datalist" sheet column E:E but only if D:D = 1 I'll take any quick fix at this stage. Cheers AlSolved65Views0likes2CommentsExcel can't open file from hyperlink after upgrade windows11
Hello, I met a strang issue. After upgrade system from w10 to w11, my excel can't open linkage file which was working normal before. Tested file and folder which located on local and SMB was not working, after click the hyper-lnik nothing happen and no pop-up just turn to mouse to loading icon then nothing happen how can I check which part cause this issue? Also tested on word have same issue. Further, url was working normal.8Views0likes0CommentsA little help on text grouping delimited by nulls in a data set in power query
A have a column in power query: null line of text line of text line of text null The number of rows with lines of text can vary, but each "text block" is delimited by column entries at the top and bottom that contain 'null'. Ultimately, I would like to take each text block and populate a single cell with it. Probably in a new column in an excel table. Can I get a nudge in the right direction?99Views1like4CommentsExcel Functions
Find attached a matrix, and picture below I select Rare (0.5) in a cell and Modera (8) in another cell, i want function to return in a cell from matrix above. Like A (4) with green filled in. My inputs are Rare (0.5) and Moderate (8) and i want output cell to be Green filled in A (0.5) picked from matrix above. Like wise if the input cells are Possible (2) and Critical (50) then the output will be D (100) filled with light orange. I tried Index and Match function but failed. I can send the excel if you want. Please let me know what is the right function i have to use. Send the solution to mailto:email address removed for privacy reasons or email address removed for privacy reasons Regards111Views1like3CommentsExcel Power Query: editing unloaded query refreshes dependent queries
Hi all There has been a change between version 2601 and 2602 on the Current channel which means that when editing a query in Power Query it will trigger a refresh for all dependent queries. This makes Power Query unusable for any complex queries or large data extraction. Take the below example M code. SourceQuery is not loaded to the workbook or data model. LoadedQuery references SourceQuery and is loaded to the workbook. Query: SourceQuery let Source = #table({"Col1"},{{"x"}}) in Source Query: LoadedQuery let Source = SourceQuery in Source In version 2601 and previous versions, you could edit SourceQuery, 'close and load' Power Query and save your workbook almost instantly because no data was refreshed. In 2602 and later versions editing SourceQuery forces a refresh of LoadedQuery which can be very time consuming but more frustrating for all my complex/larger workbooks it triggers lots of refreshes which never finish and result in my having to end task on Excel (note: refreshing these queries takes less than 10 seconds). Doing an end task on all the mashup containers does not allow me to regain control of the Excel workbook. This means all changes to the workbook are lost. I have had to resort to using a VSCode extension to edit my Power Query without crashing Excel. The builds I have tested are 2601 19628.20166 and 2602 19725.20190. Are there any plans to rollback this change? Other than rolling back my Outlook version or using VSCode are there any methods to edit my queries without crashing Excel? It would also be helpful to understand why such a large change in functionality was not captured in the release notes. Per the release notes there were no changes to Excel in 2602, not even 'various fixes to functionality and performance'. Many thanks, Andrew13Views0likes0Comments