•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Output spilling over and I can't seem to fix it
Our take
Are you frustrated with output spilling over in your spreadsheet? You're not alone. Many users face challenges when trying to automate data retrieval in complex layouts. In your case, you want to match employee names with their respective departments seamlessly. Instead of manual entry, leveraging a formula can save you time and streamline your reporting process. In this guide, we’ll explore a more effective formula to return department names in a single cell, eliminating the spillover issue and enhancing your spreadsheet’s efficiency. Let’s dive in!
I'm having a problem with a spreadsheet:
In A2:A200 I've got a list of employee names
In C1:H1 I've got a list of departments (Finance, HR, Payroll, Maintenance, Grounds, Housekeeping, etc.) with employees parsed out in their respective columns ('John Smith' in C2 under 'Finance', for example.
I'm trying to get a formula to reference values in the A column vs. where they're listed in the C through H columns and then return the department in the respective B cell (IE "John Smith" in A2 will return "Finance" in B2)
I've tried =IFS(A2=C$2:C$200,"Finance",A2=D$2:D$200,"HR",A2=E$2:E$200,"Payroll") and so on with the remaining logical tests for the other departments but my output keeps spilling into A3 and down, which prevents me from using it in other cells.
I can brute force my way through by putting in each employee and their department manually but I'd rather automate the process to speed it up next time I have to run this report. Any help is appreciated.
Edit: I'm using Office 365 Enterprise
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- Excel seems to think cell isn't big enough so 2 digit number looks crazy, no adjustment fixes issueI have multiple sheets that add together to make a total on the first page. Easy. The numbers are small - 3, 5, 10, nothing big. On the totals sheet, all the two digit sums are wrapped so that they are very small. I do not have wrapped text on but when I DO wrap the text, it stops wrapping them BUT there's this giant space between the two digits like "1 0" for 10. I tried everything I could think of - rewriting the formula as a SUM function (rather than this sheet + this sheet + this sheet), making sure each cell is formatted as a number, running "text to column" on each column of data, switching the font, switching the font size, changing the column size manually, double clicking on the columns to have them auto size, turning off shrink to fit, turning on shrink to fit again after, playing with the alignment.... Does anyone know what is going on and how I can solve it? Adding images (w/ some edits for privacy for my company). Will include the formulas (both versions). https://preview.redd.it/6n05pqeer4og1.png?width=1654&format=png&auto=webp&s=64729e1558bfba595d7d043b18d777b3e33e4ecf https://preview.redd.it/ydydstbfr4og1.png?width=1664&format=png&auto=webp&s=27e3d7cd18c2c380a5cf1a48853060a4bcd17c7c https://preview.redd.it/7dcraf9gr4og1.png?width=1106&format=png&auto=webp&s=4c93c635fbc20762a18cc8e137b6a40ee37c470e submitted by /u/catincombatboots [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [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]
Tagged with
#Excel alternatives for data analysis#financial modeling with spreadsheets#natural language processing for spreadsheets#generative AI for data analysis#enterprise-level spreadsheet solutions#rows.com#AI formula generation techniques#modern spreadsheet innovations#machine learning in spreadsheet applications#digital transformation in spreadsheet software#Excel compatibility#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#real-time data collaboration#automation in spreadsheet workflows#no-code spreadsheet solutions#spreadsheet#employee names#departments