User Profile
LilYawney
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Slicer showing blank data when there isn't (multiple slicers)
I have no idea why I'm having issues with my slicer but I am. I've attached a screenshot of the issue at the bottom of this post. Problem: when I filter the vendor, there is only this one expense and the SBU is SW/WW/C; why is it showing that there is an entry that's blank? The slicer settings (for all of them) are to hide options that don't have any data. I already know it's not including any data that's been deleted. How can I fix this?!Re: External data referencing returns #REF! error
I didn't use INDIRECT. This is the formula that I use: =SUMIFS('2022 Expenses PivotTable.xlsx'!Income[Amount],'2022 Expenses PivotTable.xlsx'!Income[In. Stmnt. G/L Groups],$B$8,'2022 Expenses PivotTable.xlsx'!Income[Date],E$7,'2022 Expenses PivotTable.xlsx'!Income[SBU],$B$9) When the other file is closed, the reference sheet does turn into a url link to the file.External data referencing returns #REF! error
I am working on some spreadsheets with my company's expenses and financial information. One workbook, 2022 Expenses PivotTable, has information on all expenses, the income, and salaries. In a different workbook, 2022 Pro Forma, I am referencing information from the former workbook into the Pro Forma due to the complexity of how my boss wants it set up. At one point, I can't remember when exactly, all of the formulas in the 2022 Pro Forma returned a #REF! error; I checked the formulas and there isn't a #REF! in it (which would've resulted from deleted reference cell). When I opened the 2022 Expenses PivotTable to see if there was a problem in that workbook, I noticed that all the #REF! errors in the Pro Forma were suddenly fixed. (I've attached an image of the #REF! error from Excel online since my Excel app is too slow right now from a company wide SharePoint sync; the problem appears on both applications though) I understand that this can happen is the Trust Center settings and/or macro settings aren't configured properly. I have added my entire C Drive to the Trust Center to try and fix this problem but it didn't work. Can someone PLEASE help me with this!? I shouldn't have to open both spreadsheets to view the information on just one of them. Unrelated note: While searching for an answer, I came across this post from January of 2023 asking the same question. https://techcommunity.microsoft.com/t5/excel/reference-link-to-external-workbook-field-value-using-path-with/m-p/3715403/highlight/true#M176894 Since there was no response to ScholiSG 's post, I'm tagging them in this one in hopes that we will BOTH get an answer!Excel Table Auto Formatting Issue
I have a table in my expenses spreadsheet. To add new rows, I type in the row below the last one in the table and it automatically adds it to the table range; my problem however, is that the new rows are automatically formatted to look like the second row in the image below. I want to know if there is anyway to change the auto formatting to look like the first row in the image below.Auto populating in table #SPILL! error
I am working on an expense spreadsheet. All expenses and their info are dumped into a sheet called "Itemized Expenses"; on another sheet called "Monthly Expenses", I want it to auto populate the G/L Code, SBU, and Category into the table. The way I've been doing that is by having the following codes in the G/L Code column, SBU, and Category, respectively: =IF([@SBU]="","",IFS([@SBU]=Sheet1!$U$2,Sheet1!$T$2,[@SBU]=Sheet1!$U$3,Sheet1!$T$3,[@SBU]=Sheet1!$U$4,Sheet1!$T$4,[@SBU]=Sheet1!$U$5,Sheet1!$T$5,[@SBU]=Sheet1!$U$6,Sheet1!$T$6,[@SBU]=Sheet1!$U$7,Sheet1!$T$7,[@SBU]=Sheet1!$U$8,Sheet1!$T$8,[@SBU]=Sheet1!$U$9,Sheet1!$T$9,[@SBU]=Sheet1!$U$10,Sheet1!$T$10,[@SBU]=Sheet1!$U$11,Sheet1!$T$11,[@SBU]=Sheet1!$U$12,Sheet1!$T$12)) =IF(D6<>"", UNIQUE(FILTER('ITEMIZED EXPENSES'!$G:$G, 'ITEMIZED EXPENSES'!$J:$J=D6, 'ITEMIZED EXPENSES'!$G:$G<>"")), "") =IFERROR(INDEX(UNIQUE(FILTER('ITEMIZED EXPENSES'!J:J, 'ITEMIZED EXPENSES'!J:J<>"")), MATCH(0, INDEX(COUNTIF($D$5:D5, UNIQUE(FILTER('ITEMIZED EXPENSES'!J:J, 'ITEMIZED EXPENSES'!J:J<>""))),,), 0)), "") Explanation of formulas' desired functionality: G/L Code - return the G/L Code of the matching category from "Itemized Expenses" (should return values on the same row) SBU - return a unique list of SBU's based on the category within each SBU from the "Itemized Expenses" sheet Category - return a unique list of Categories from the "Itemized Expenses" sheet The problem I'm running into is that each SBU has their own category; but some SBU's share similar categories. (i.e., Corporate Services and General and Admin SBU's both have their own "Office Expenses" category). (G/L Codes and "Requested By" have been hidden due to sensitive information; "Check Amount" isn't hidden since true numbers were changed for this image) Because of this, I get some '#SPILL!' errors since the formula is trying to return a list of all SBU's that match that category. The whole reason why the expenses need to be organized this way to calculate the monthly expenses; there will be multiple expenses for each SBU Category so we want them to be divided out (hopefully that makes sense) Is there a way to fix this '#SPILL!' error by fixing my formulas? Or should I populate the information differently?Re: Formula for calculating employee work hours when the list is constantly changing
rzaneti So the main problem is in rows 158 and 159; they are referencing the numbers from different sheets. When a new employee is added or removed from one month to the next, the referencing cell for that employee isn't the same anymore. When we start a new WIP month, there's also the problem of having to go back and edit the formulas to include the previous month in the Billable and Productivity hours average. Hopefully that makes sense! (Also, I know how fix the dividing by zero errors so there's no need to help with that problem!)
