excel
44329 TopicsHow to make excel add or remove rows to fit data and prevent spill error?
Hello! My question is: When using the =FILTER formula to copy cell data from one sheet to another, how can I make excel add or remove rows as needed to prevent a spill error? I am using the =FILTER formula to copy cell data from one sheet to another if a checkbox is checked. My formula uses the cells containing the checkbox (column A) as the reference for columns B, C, D, etc. to the right. I did this by selecting the cell that has the formula on the destination sheet, highlighting this cell and those to the right I wanted to transfer, and then control + r. My formula is: =FILTER('Source Sheet'!B5:B13,'Source Sheet'!$A5:$A13=TRUE) (If there is a way to copy an entire row from the source sheet instead of having to control+r to apply the original formula across select cells please also let me know, thank you!). However, I want the sheet to be able to automatically add rows to fit the data if the selected number of rows is greater than what I have left available on the sheet. Currently, if I leave 3 rows for data to fill and I select three checkboxes, this will fill the space with no problem. However, if I select 4+, I get a spill error (as there are not enough empty rows to put the data). Is there any easy way to have excel add rows as needed? Similarly, is there a way for excel to remove rows as needed? I am going to use this sheet to detail all medications being taken by family members (with medication name, concentration, dose, time to take each in their own column as column B, column C, column D, column E). The names will be listed in column A with ~3 rows below each name which will fill when the checkbox on the source sheet is clicked. I want to ideally fit this on one page when printing without major "fit to page" problems that come up during printing. The layout of the sheet will ideally look like this: The goal for the 'destination sheet' is to pull only the selected data I want to use from my master list on the 'source' sheet.' I want to avoid having a big empty space under each family member where they don't take many medications, and to also not have to manually add rows for family members that take a greater number of medications. Please let me know and thank you!32Views0likes1CommentHelp with data and visuals
I hope i can explain this right, i have data that shows 3 things: person, start date and end date: now i need excel to take this information and represent it as the following in the month tracker: It would look for the charts and figure out the timeline and add them to a row with the timeframe: example is Booy 15-0ct-25 to 23-oct-25 the char would go to the next one and add his information, I hope someone can help me. I just need it to populate the month tracker, Thanks83Views0likes3CommentsDelete cells with 3 consecutive consonants
I submitted a question a little while back asking how to delete cells with exactly 3 consonants. The response I had was a huge help, but now I'm faced with a similar problem: How do I sort words by consecutive consonants? And if that cannot be achieved, how can I instead delete cells from a sheet in which a word has 3 consecutive consonants?40Views0likes1CommentTaking text from a multi-line cell and making each line its own row.
Hello, I have a sheet with several thousand lines that I pulled from my current system, and all of the data was exported into cells like this: What I would like to do is take each line and make that line its own row in the sheet. For example, the scooter would have its own row, the MWC have its own row, so on and so forth. Is there any way this is possible, or should I be looking to see if there is any other way I can export this data?81Views0likes2CommentsExcel jumps to A1 when selecting a range on another sheet during VLOOKUP
Hi everyone, I’m experiencing a strange issue in Excel and I’m hoping someone can help. Steps to reproduce the problem: Open a workbook with at least two sheets (Sheet1 and Sheet2). On Sheet1, start typing a VLOOKUP formula When selecting the table array range, switch to Sheet2 to select the range. Excel unexpectedly jumps the selection or view to cell A1 on Sheet2, losing the intended range selection.90Views0likes2CommentsExcel formula help - vertical and horizontal in same calculation
Hello, I have a set of data that contains resource names and their FTE effort per month. I'm trying to calculate their start month and end month in two cells but can't get my head around the correct formula. Here is an an example data set: A B C D E F G H I J K L M N O P 1 ID Employee Start Month End Month M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 2 1 John ?? ?? 0 0 0 1 1 1 1 0.5 0.5 0 0 0 3 2 Brian ?? ?? 1 1 1 1 0.5 0.5 0 0 0 0 0 0 4 3 Alison ?? ?? 0 0 0 0 0 0 0.5 0.5 1 1 1 1 The result I'm looking for is for John - Start M4, End M9; Brian - start M1, End M6; Alison - start M7, End M12 .. i.e. its linked to non-zero cells I have a formula using match/index that I found on the web that is getting the start date (example of John's row): =INDEX('!E$1:P$1, MATCH(TRUE, '!E2:P2<>0,0)) I can't however amend that formula to work for the end date. I'm more familiar with XLOOKUP than MATCH/INDEX and I'm assuming there is some embedded XLOOKUP (=XLOOKUP("1", A:A, XLOOKUP("<>0", <<second row>>, <<first row>>) - cant get this to work , that could work here, but its outside the limits of my abilities. Suggestions please?Solved72Views0likes4CommentsUpdate Excel formulas between a path in local folder and owncloud in another computer
I have a lot of Excel archives with lots of formulas which reference other Excel files, all in the path H:\Fold\Data\physics on a computer, and at the same time, on another computer, I have an owncloud folder C:\Users\fulanito\ownCloud\Shared\Data\physics. How could I change the paths in formulas to be able to update the data when I open them in the owncloud folder or in the original folder? They share part of the same route: \Data\physics The local folder on one computer (H:\Fold\Data\physics) is synchronized with owncloud, I share the folder Data. Mounted folder in owncloud in another computer: C:\Users\fulanito\ownCloud\Shared\Data\physics I tried to use relative paths but that works only if all archives are in the same folder. In my case for the owncloud path, for example one archive, data_2025.xlsx is in C:\Users\fulanito\ownCloud\Shared\Data\physics\2025 another, data_2024.xlsx, is in C:\Users\fulanito\ownCloud\Shared\Data\physics\2024 the Excel that use formulas with references to these two archives is in C:\Users\fulanito\ownCloud\Shared\Data\physics\summary. So if I want to update the summary archive daily_summary on the computer with owncloud, all the archives have to be in the same subfolder \summary\ Examples of formulas I use in the summary sheet to reference my files in H, in Excel daily_summary.xlsx are ='H:\Fold\Data\physics\2025\[data_2025.xlsx]Horario'!AV1419 and in another cell next to this is: ='H:\Fold\Data\physics\2025\[data_2024.xlsx]Horario'!AV1419 So if a make a change in data_2025 in that cell and I open on the computer with owncloud, it can't update because it references the local computer H:. I prefer not to use macros.19Views0likes0Comments