Forum Widgets
Latest Discussions
Conditional format - Highlight Max and Min value for a group of rows
I need to highlight the max and min value for every column for each group of rows that referred to the same day. Rows 5 - 12 as a group, I need to highlight the Max and Min value with different colors for each column (W1,X1,Y1,...) same thing for the group 13- 33 until the last rows Please find the file attachedJihad Al-JaradyFeb 10, 2025Steel Contributor2.8KViews0likes3CommentsTwo way sum lookup (by month & name)
Hi all I am trying to perform a two way lookup from a source table. Basically I have names of colleagues with a number of days per week recorded. I want to then in another table using a formula sum the total amount of days by colleague for each month. I tried using SUMIFS, but it throws a VALUE error as I think the SUM range is not accepted across multiple columns/rows. Any ideas? I attach a sample spreadsheet. Thank you in advancematt0020190Feb 10, 2025Brass Contributor0Views0likes0CommentsIf you thought Spills killed CSE, read this post
I build financial models. All financial models I have ever seen do the same thing - they show one reporting date per column. Every next reporting period evolves from the previous financial close. That is, the calculation order in such models is strictly A1 to An, B1 to Bn, ... N1 to Nn. There may at times arise the need to perform complex calculations that produce more than one result. Today, we can (program those in Lambdas and) spill the outputs across multiple rows (within the same column). Alas, Excel's performance resizing Spills is absolutely atrocious. Deploy such a formula across the columns of your model and you can literally watch the pixels set. One column at a time. Things settle after the initial run. There is then no need to resize ever again. Just so long as the calculation works out alright. Unfortunately, stuff happenz. And when that calculation errors out, you are back to watching pixels set as Excel replaces your multiple result rows with one row of #VALUE!. Once you fix that error, well, then you are back in for another treat of umpteen resize passes... Such a spreadsheet is frankly unusable. Now, I noticed with some astonishment that there is a simple fix for this nonsense: Just enter your spill formula with Control-Shift-Enter to morph it into an array formula. Sure, we all hate those. But all the hassle around editing is a price worth paying for Excel suddenly attaining Warp speed... Post Scriptum: There is another "solution" to this problem. Instead of performing the calculation once to spill 5 results, perform the same calculation 5 times over and return 1 result each time. If you think this is awful, you are of course correct. But ... to my amazement, doing this easily beats the Spill (no surprise - even using an Abacus probably will) and there was no telling a difference to CSE. I was baffled. Then I realized what Excel got up to. Excel understands that these 5 calculations are independent of one another and will happily parallelize them across multiple CPU cores. So, while this insane solution hammers the CPU, the nature of a financial model is such that it mostly cannot exercise more than one core anyway (it cannot be multi-threaded), such that on the occasion that we are being stupid, there are plenty of cores idling around that can bail us out... :) Post Post Scriptum: If for some reason you do not wish to use CSE, make absolutely sure that when stuff happenz your Spill returns 5 bad results. That avoids Excel having to resize that range when an error occurs.ecovonreinFeb 10, 2025Iron Contributor55Views0likes3CommentsMake Excel Chart from multiple sheets
I hope once again for help with excel, since this community was super helpful before. I have multiple sheets in a excel sheet and I would like to create a chart that pulls data from multiple of those sheets. I have Team 1 & Team 2 (on separate sheets within one file) and data on how many people of each team are at any location at any given day of the week. I would like to create a chart that shows the location on the x axis. I would like to show how many people (regardless of team) are at any location each day. If possible it also shows how many people of each team are at Location 1 on Mon, and so on. I am happy to change the data collection sheets as well if that helps.YvonneD2170Feb 10, 2025Copper Contributor23Views0likes2CommentsHow to fill a range of Sheet A with data from Sheet B by repeating this action for each value
Hi, I have two sheets A & B. Sheet A has a table where A1:H1 is the header. The range C2:C9 of this table is blank but there is data in the ranges A2:B9 and D2:H9. Sheet B has data in the range A1:A43. I have to fill the range C2:C9 of Sheet A with data from Sheet B by repeating this action for each value in Sheet B.Dan1ExcelUserFeb 10, 2025Copper Contributor57Views0likes5CommentsFormula assistant
Hi All, Attached the following pic: Im trying to create the yellow cells by formula (I made it manually) the rules: if the column date and the raw date is a match so it should give us column H value (exm:cell J7, the column date is 01/01/2025, same as row 7 date so im getting H7 value) one cell to the right where we got our match I want it to take the left cell divide by minus 4 and multy by 3 (15360/4*-3 = -11520) and than to the next 3 cell to the right, I want to get the -11520/-3 = 3840 after 3 cells I want it to get zeroed, thx all in advanced !SolvedTomerIwanirFeb 10, 2025Occasional Reader76Views0likes5CommentsRepeat list of items for each month in table
Hi all I am looking to have an automatic repeating list of items that repeats based on the amount of months in a table. If however a month is mentioned in the list of items, the item will only repeat for that month only and not all the months in the table. Please see my example spreadsheet with the desired output, which should make it clear. I just need a formula to make this output table. I guess LET would be possible, but can anyone help please? Thank you Mattmatt0020190Feb 10, 2025Brass Contributor61Views0likes5CommentsCheckboxes not automatically updating using COUNTIF
Hey, all, I'm using the following function to set the checkbox boolean of a cell based on the amount of other cells checked: In cell A1: =IF(COUNTIF(A2:A3=2,TRUE),TRUE,FALSE) The formula works, but only when I manually click in and out of the A1 cell. Is there a way to have the cell automatically update like it would in Google Sheets? Using Excel for web build 16.0.18602.42306 here. Appreciate the help!bramleyasFeb 10, 2025Copper Contributor105Views0likes4Comments"OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELECTED
HELLO , I HAVE OFFICE 365 . FROM 1 MONTH AGO THE BUTTON IN DATA TAB THEN PROPERTIES AND IN IT "OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELCTED AUTOMATICALLY AND WHEN WE REFRESH DATA IT GET OVERLAPPED . EARLIER IT WAS WORKING FINE LIKE IN SCREEN SHOT WE HAD SELECTED "OVERWRITE........" TAB BUT WHEN WE PRESS OK AND REOPEN IT IT GET DESELECTED PLEASE HELP ON THISPREETFeb 10, 2025Copper Contributor33Views0likes2CommentsYear View for Dynamic Calendar
Anyone able to assist with a yearly view for a dynamic calendar? I have a month view which pulls dates associated with a deployment from a table (I got this template from someone in the excel community). I figured I could just copy the month calendar 12 times and just change the months in the drop down but it isnt updating the dates and therefore, not pulling the deployment details. Attached is the file. The first table in the year view sheet is working as that was the same table I copied from the month view sheet. All other table do not work.Solvedbarbdd12Feb 10, 2025Copper Contributor46Views0likes5Comments
Resources
Tags
- excel42,178 Topics
- Formulas and Functions24,449 Topics
- Macros and VBA6,348 Topics
- office 3655,931 Topics
- Excel on Mac2,616 Topics
- BI & Data Analysis2,329 Topics
- Excel for web1,878 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,607 Topics