Forum Widgets
Latest Discussions
Colormatch function in excel (using VBA code)
I am preparing a scoreboard for different projects. 10 KPIs of different weightage to measure each project performance. Each KPI will have either 3 responses (good, bad, ugly). User will update the response as either good or bad or ugly then i will use conditional formatting to color the cell as Green, orange and Red for good, bad and ugly. Once after all the 10 KPIs response received, based on the colormatch function, i need to formulate the scoreboard. For reference, i am giving exactly those 3 colors in some other cells for the formulae reference. But i am facing errors... even though the response of 1 KPI is in green and it is not matching with the green colored cell (reference cell) because, "good" is mentioned in the cell, where as green reference cell is empty. i cannot update these response to reference cells because each kPI have different responses. Can someone suggest me a solution to derive a scoreboard
Dynamically filter table on basis of range of criteria
Hi, I have a large table (150k rows) loaded to Excel from power query and I want to filter the description column (column N) dynamically on a couple of key words. It should be an AND filter so the column should contain any of these words and then result in a table showing column M, N and AH. Anybody any idea how to realize this keeping a mind the size of the table? many thanks, regards,
Can you create a Button that Opens a Pop-Up with Data from Spreadsheets
Good evening, I have moderate knowledge of excel, and some knowledge of visual basic and am trying to develop a personal trading journal log. My intent is to create a pop-up for trades that I can input a lot of detailed data and an image of the trade that will then auto-populate a brief information row for a quick glance, but will also have a button in the row that populates for each trade that, if clicked, will open a pup-up of all the information given including the picture, comments about the trade in reflection and review, etc. I have been searching for if this is possible and haven't really found any information on it, only things like pop-ups for inputting data. Can anyone help me determine if this is possible, and if so how? Thank you.Rename Cell to Various Different Sheet Titles
Hi, I am trying to create a formula that allows me to have cells equal to the names of different sheets. I have read several interesting posts that created solutions for having a cell automatically change to the name of whatever its sheet is, however, I am trying to create a table that automatically fills out the rows as I add new tabs. For example: I add a new sheet and name it "March", the next column title in my table will be "March". Having to do it manually at the scale required would be quite difficult. I believe this may require the use of VBA, and my attempts to teach myself were not very fruit-full. Is this possible? Thanks in advance.Sorting Problem
I build computers for myself and for friends, and I built a spreadsheet for comparing builds. I have a well developed system of determining a standardized point value to each component based on price and performance, but that's not part of the issue. I am trying to get a list of the builds in order of their rank based on totals of the points. I am 95% of the way there, when there are multiple builds with the same point value, the list shows the correct number of entries for that point total, but it lists the first build name of that total for all the entries. It's not listing the different names for each of the builds with the same total. I'm including a screenshot of a simplified version of this setup (excluding all the calculations of the point values, just their totals) as well as a link to the simplified spreadsheet if it's useful. Cell K2 uses the formula =LARGE($F$2:$F$16,$J2) Cell L2 uses the formula =INDEX($A$2:$A$16,MATCH(LARGE($F$2:$F$16,$J2),$F$2:$F$16,0)) If anyone has a suggestion how to adjust or rewrite the formula to solve this issue it would be greatly appreciated! Lee https://www.dropbox.com/scl/fi/28e5jr65lbov3yp1u64v7/SortingProblem.xlsx?rlkey=rq50ubzz4x2w0yr3870i3gasn&dl=0
Substituting Text in Excel Lookup Formula
I'm keeping game weekly statistics in Excel. I have the Lookup formula below: =LOOKUP(7,[WeekX.xlsx]Game!$A:$A,[WeekX.xlsx]Game!$B:$B) The "7" refers to a player number. I'm using Week1 in the first week, Week2 in the second week, etc. I have many of these Lookup formulas in the one spreadsheet. I would like to substitute "WeekX" where the "X" can point to a spreadsheet cell value (i.e. 1 or 2 or 3, etc.) I've tried double quotes "" and Concat in the Lookup formula. I have not been successful. Any suggestions?
Replace all formulas based on a pattern
I have several formulas in my worksheet that follow a similar pattern. The formula is as follows. =IFERROR(INDEX('Data Tab'!O:O,MATCH(TEXT(A350,"0"),'Data Tab'!E:E,0))*C350,0) I want replace all of these formulas to exclude the TEXT function. The new formula should be as follows. =IFERROR(INDEX('Data Tab'!O:O,MATCH(A350,'Data Tab'!E:E,0))*C350,0) How can I achieve this using Find and Replace?
Sumifs or other solution for both vertical & horizontal criteria
Hi all, I would like to sum values from a matrix of vertical critiera (e.g. names) and horizontal ones (dates as well as other criteria). I am not sure the sumifs formula can actually do this. So far I am getting errors. Hence, I would be very grateful if someone had another simple way/ formula to have the results calculated. Example is as such: Criteria 1 (vertical): Names Criteria 2 (horizontal): Date (1st row) Criteria 3 (horizontal): "Plan" (only) i.e. (2nd row) Sum-up values: In the empty cells in the matrix of the 3 criteria 1st May 25 1st Jun 25 1st Jul 25 1st Aug 25 1st Sep 25 Actual Actual Plan Plan Plan Name 1 Name 2 Name 2 Name 3
