Forum Widgets
Latest Discussions
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 3Sorting 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
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,
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.Comparing two ranges of cells in an if function
I have two rows of address information that I have to compare. If cells A-R in row 2 are identical to cells A-R in row three then I want cell S inn row three two be returned if true and blank if false. I tried the below formula and it gives me a VALUE error. =IF(A2:R2=A3:R3,S3,"") It works if I dont compare ranges, for example if I only compare A2 & A3. How do I do this formula with the logical test as comparing ranges of cells in rows. First Name Last Name Grad Year Grade Level Description Dob Address Line 1 Address Line 2 City State Short Postal Code/ Zip District Resides With Address Line 11 Address Line 21 Address Line 3 City1 State Short1 Postal Code/ Zip1 Parent/Guardian First Parent/Guardian Last Jim Smith 2018 12 1/1/2001 123 Any Street x Any City State 10000 ABC TRUE 123 Any Street x x Any City State 10000 William Smith #VALUE! Jim Smith 2018 12 1/1/2001 123 Any Street x Any City State 10000 ABC TRUE 123 Any Street x x Any City State 10000 Mary Smith
