•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sheet not pulling data from tabs with names that have two words in them
Our take
When working on a statistical model for college basketball teams, you may encounter an issue with Excel not pulling data from tabs named with two-word team names, such as "Oklahoma State." Instead, you receive a #REF! error. The formula you're using references the "ALL TEAMS" sheet to match team names, but it appears to only work for single-word names like "Kansas." This issue likely stems from how the MATCH function interprets spaces in the team names. Let’s explore some strategies to resolve this challenge effectively.
I'm working on a statistical model for college basketball teams. I adapted it from a Google Sheet and decided to try Excel.
Each team is in its own tab, and the tabs are named for the teams themselves (Kansas, Oklahoma State, etc.).
I'm hitting a snag where the spreadsheet will pull data from team tabs that have one name (e.g. Kansas, Cincinnati) but not those with a space in the name (e.g. Oklahoma State). Instead I get a #REF! error. An image is below.
The formula in the fifth column from the left looks like this:
=IF(D23="D2","D2",INDEX('ALL TEAMS'!$P$2:$P$366,MATCH(B23,'ALL TEAMS'!$B$2:$B$366,0)))
What am I doing wrong here?
UPDATE from below reply:
Actually, no, It's not trying to reference sheet names. It's referencing names in a column on another sheet called "ALL TEAMS".
The problem I think is with the MATCH function. In the formula I provided, I'm asking it to find cell B23 in the ALL TEAMS tab. That column is where the team names are.
It's pulling the teams that have one word in their name, but not the teams that have more than one word.
Putting single quotes around B23 (e.g., 'B23') does not work. It spits a warning out at me about Excel thinking I'm writing a formula.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Anomalous Sorting of COUNTIF ColumnsEDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it. Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly: https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/ (Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.) I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#) (Where # equal the row) The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns. Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A# The formula works just fine. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort. So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28) After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2) So.... what's the issue and how do I resolve it? Version: Microsoft Excel for Microsoft 365 MSO (v2508) Environment: Desktop My Knowledge: Intermediate submitted by /u/LikeCCClockwork [link] [comments]
- Can excel find data in a sheet, if that data sometimes may not exist/be in the same cells?I am making a sheet at my work that basically sums up stats about staff each week. However, the internal source I pull the raw data from only shows employees who actually worked on a given day. My final sheet will show daily data and weekly stats. I am trying to find a way to create a formula that will match that employee's name with their data on the raw data sheet and pull it into the finished sheet. But it will not be in the same cell each time. I am trying to avoid hand-tranferring the data if possible. What I really want is a formula that will tell it "if you see Jane Doe, put the data two columns to the right of her name into this cell." Excel LTSC MSO Version 2408 English language, Windows desktop, beginner proficiency submitted by /u/pinkysaurusrawr [link] [comments]
- How to link data from specific columns on one sheet to another, then sort it column A alphabetically and match the other columns based on corresponding data from Sheet 1I am a beginner at attempting to write formulas in Excel, and I am currently using Excel on a web browser mainly but can and do occasionally edit in the desktop version. I believe the version is Microsoft Office LTSC Professional Plus 2024 in the desktop version, or at least that's the product activated. I have two sheets on the same spreadsheet, and I want to pull in the data from 4 different columns to the second sheet and have it automatically update when there are changes or additions to the first sheet. I have a couple issues making this happen - the main one is that the data in my first sheet is custom sorted by color, but I want the new sheet to take the data from column B on sheet 1 and sort it alphabetically, and take the other columns and match them correspondingly to the correct values they match to from column B on the first sheet. Secondly, for blank cells, I want it to leave them blank instead of inputting zero, or any dates. I don't know if this is all even possible, or if I should just remove my custom filters from sheet 1 by cell color and just link it that way. I've spent hours trying to find formulas that work. Please see attached images for reference on what I've tried, and for more clarification as to exactly what I'm requesting. Sorry for the copious redacting, I wanted to be safe rather than sorry with identifiable info regarding this. Any help would be greatly appreciated. https://preview.redd.it/6vt2ocasj9kg1.png?width=1199&format=png&auto=webp&s=35a78bf5c6d96437094c04023d3ca24fa6ec3399 https://preview.redd.it/kqbi5za5k9kg1.png?width=791&format=png&auto=webp&s=f80ee9272131546132c6925e5d0a8b9dc3ae74f2 https://preview.redd.it/1947drz7k9kg1.png?width=836&format=png&auto=webp&s=4c07c1354c348ee85b1a556d21dd0c677b1ae659 https://preview.redd.it/8ynxkp39k9kg1.png?width=869&format=png&auto=webp&s=7d69705a4a61702e0f89a49f485f8cf53d230146 https://preview.redd.it/7fd5p2v9k9kg1.png?width=943&format=png&auto=webp&s=cd3c9fe721db57b44f7b5260ac891641a80170a0 submitted by /u/MeretrixRex [link] [comments]
- I am trying to prevent the end user from having to insert data twice to see it on two different sheets, and the formulas I've been trying aren't copy/pasting well.I have been making a CRM spreadsheet for a finance business with over 100 clients. In trying to optimize the formatting, I have developed two different views which have two different uses. Sheet 1 is "2026 List View" where the informaiton is all on one row per client. It is annoying to side scroll to see all of the information, but it gives the best overall view as to who has paid. https://preview.redd.it/1yinsuhafikg1.png?width=3370&format=png&auto=webp&s=7559a5cfc76b671e2bbcc4b4700c446e2531ce46 Sheet 2 is "2026 Grid View" where the informaiton is organized differently to not need side-scrolling, and gives a better quick per-client view. https://preview.redd.it/2oip1qrbfikg1.png?width=2697&format=png&auto=webp&s=7b4f9f29f34db95d14e17df60efca580ff4c546a I am writing formulas for Sheet 2's cells to reference Sheet 1's data. For example, Sheet 2's cell B3 has ='2026 List View'!A2 and it is working well in terms of the formula. However, once I finished two tables refrerencing cells in rows 2 and then 3, copy/pasting made the next table do the correct columns but in the rows 16 and 17 instead of 3 and 4. I tried continuing the pattern longer, but copy/pasting still made it skip several rows though it is recognizing the correct columns. Is there a different formula I could use to make the copy/pasting more successful? Or, am I stuck doing this cell by cell for 100 rows, making it not worth the hassle? submitted by /u/urgrlB [link] [comments]