Forum Widgets
Latest Discussions
Input 1 to 7 SKU numbers to return groups of rows.
My actual document has 300 cases of product listed in rows, I trimmed it down to 20 cases of product for this example and replaced the names for confidentiality. I want to be able to input these three components 9800006353, 9800006357, 9800006356 and return rows 3-6, 12-15, 24-27. Because the Case SKU in column A has more than just those three components. I want to be able to do this with any of the hundred or so components exist and then return the Case SKU with all of the Component rows. First image is what it would like to start with, then I would input the three components 9800006353, 9800006357, 9800006356 and the second image is what I am looking for. Second image below.robodan70Nov 07, 2024Occasional Reader35Views0likes3CommentsHow to get excel to display values conditional to current Month
Hello I have a personal billing excel sheet to keep track of monthly charges Some charges are every 3 months, some are every 4 months, so the monthly expectation is different each month I want to find a way to have a cell display the expected charges based on the month I figure i can use current Month reference using =MONTH(TODAY()) but dont know how to create a condition to have the MONTHLY $ to display the value for the Month (how to write a formula which will display the Dec value of 63.63 in the Monthly $ when the Month calculates to 12) 11 AUDIBLE 17.00 BULLYMAKE 46.63 Feb 17.00 Mar 46.63 Apr 17.00 June 63.63 Aug 17.00 Sept 46.63 Oct 17.00 Dec 63.63 monthly $ Thanks for any helpPathologistExtraordinaireNov 07, 2024Occasional Reader11Views0likes2CommentsFilter, Sortby...help with formula needed!!
I wish to create a table on Sheet 2 extracted from a table on Sheet 1. I only wish certain columns to be used, driven by whether Col 5 is populated. These will then need to be columnised into a different order and then sorted by e.g. Col 5. Is there a formula that i can use?StokieACNov 07, 2024Occasional Reader19Views0likes2CommentsFILTER Function - "include" parameter as string from another cell
Hi All I have a table on Worksheet A, unimaginatively named "Table1", with fields including "CODE". On Worksheet B, in cell C1, I have this formula: =FILTER(Table1,(Table1[CODE]="T1") + (Table1[CODE]="P1")) This works fine i.e. it shows a new table with all the rows where CODE = "T1" or "P1". Lovely. Also on Worksheet B, in cell A1, I have the following string: (Table1[CODE]="T1") + (Table1[CODE]="P1") I have created this string using logic based on the data in Table1. On another day, the string literals might change and there may be more OR-ed elements, perhaps: (Table1[CODE]="ENG002") + (Table1[CODE]="BBBB") + (Table1[CODE]="Z YW") Essentially this string is volatile and I don't want to hard-code it as in the first example. How can I use successfully the string in cell A1 as the 'include' parameter to the FILTER function? I tried: =FILTER(Table1,A1) but this gives #VALUE! I thought INDIRECT might work but: =FILTER(Table1,INDIRECT(A1)) gives a #REF! I think I am missing something obvious but can't see it. Can you help, at all? Thanks VM. PeterpaejunkNov 07, 2024Copper Contributor275Views0likes6CommentsXLOOKUP Not Finding Associated Value After Cleaning Up Data With Power Query
I am trying to find a file that is associated with a link using XLOOKUP. In the source document I am using, the links have hex characters in the URL and the file name listed in the report does not. I'm using Power Query to replace the hex characters with the actual characters so they can match the file names. For example, %20 will be replaced with a space. The issue I'm running into right now is that replacing %C2%A0 with a space is causing problems when running the XLOOKUP. There is an associated file with the link but it is resulting in a not found. Link:website.com/Example%20File%C2%A0Name%C2%A0Services.pdf After Power Query cleanup:website.com/Example File Name Services.pdf File name: website.com/Example File Name Services.pdf I tried to create dummy data but was unable to replicate my issue so that leads me to believe that the issue lies with my source document, which is autogenerated and emailed out by IT. I cannot share that document so I'm hoping there is some checks or fixes I can do to solve this issue. I've attached the dummy data document to show what I am trying to accomplish and my Power Query code to see if anything is wrong there. Also, if there is a better way to consolidate the Links steps, please let me know!renee_crozierNov 07, 2024Copper Contributor319Views0likes10CommentsWarning in Excel when trying to use dynamic array
Hi there, I did a search on this before posting, but no luck yet. Consequently, if this has already been answered, please point me there. With that said, I am a "seasoned" user of Excel (aka old), and I am used to the old school CSE array formula. So now I am supposed to use dynamic arrays. OK I like learning new things, so I gave it a spin. I got a message from Excel about spilling an array that read like a warning or otherwise suggesting I did a Bad Thing. Looking that up brought me here. USE CASE (in case it matters) What I wanted to do was use a SUMIFS function to sum over a date range that was in another sheet arranged vertically (in a table), but put the result in a grid (i.e., not a table) that had the dates horizontally. The objective is to subtotal the amount for each day over a list that had many entries for each date. So maybe it was the transposition that made Excel throw a warning, but using the CSE formula works fine. -----/END USE CASE I can easily resolve this using a Pivot Table, but that's not my question. Really, I want to know why is MS complaining about doing something its own documentation has said is preferrable to the legacy method? I don't know who prevails in this disagreement. Is the documentation or the Excel internal messaging dominant in this case? Either way it undermines the usefulness of these warnings and tips, which I am turning off because they are worse than useless.SolvedapshearerNov 07, 2024Copper Contributor175Views0likes8CommentsCross-Workbook Formatting
Hello MS World. This is my first post and I admit that it comes from a place of hope. I am an avid Excel user (amongst other MS platforms) and have become quite the stickler for formatting. For me, the sheets I create become works of art for me and the peers that utilize them. Aside from copy-&-pasting tables and the like from one workbook to another, could Microsoft please implement the ability to save themes/formatting styles within the application itself???? There is very limited "New Workbook" formatting preset functionality and I'd love to see something like a 'Theme Manager' in future updates! Does anyone else share this opinion? Please let me know, so we can let MS know there is a popular request!!! Thanks for reading.MrOsirisNov 07, 2024Occasional Reader5Views0likes0CommentsConditional Formatting Help
Hi everyone, I am in need of some Conditional Formatting / Formula Help. When you open the attachment, I have 2 tabs, Contact Info (which is the main tab) and Invoicing. When you select the invoicing tab, this is where I keep track of all the invoices. What I want this spreadsheet to do is, when a Due date is on or past the due date, I want the cell B3 on the contact Information tab to turn red for that contact. I want that same cell to turn orange when the due date is 7 days out. When the date paid is entered on the Invoicing Tab, I want the cell B3 back on the Contact Information tab to go back to white. Note There will be a lot of Invoices entered into the invoicing tab. So essentially I want the contact on the Contact Info tab to alert me of any upcoming or past due invoices by changing a color and go back to white if they have paid or are not coming up to the due date. any help with this will be extremely helpful. thank youStevenP2205Nov 07, 2024Copper Contributor10Views0likes0CommentsHow to reference a cell value and use it as the value of a row in a SUMIFS formula
I have the following table that I want to reference in a SUMIFS formula to determine the start and end of the rows in the sum. row/column A B C D 1 1 6 16 29 2 7 15 28 55 I want to use a SUMIFS formula such as SUMIFS(SheetA!$DX:$DY, SheetA!$BX:$BY, "PAY") Where X = references the value of cell A$1 Where y = references the value of cell A$2 Is there a way to do this? Excel newbie here and I appreciate all the help Thanks!CCUserNov 07, 2024Occasional Reader7Views0likes0CommentsHelp changing cell fill if a cell is populated.
Hi all, I'm currently writing a new schedule for our planned maintenance. I need to be able to change cell fill for the next four cells to green, once a cell has the value Y in it. For example: Y is entered into A1, so I need A1, A2, A3 and A4 to turn green. Thank you in advance for your help.SolvedNewtonutiNov 07, 2024Occasional Reader29Views0likes6Comments
Resources
Tags
- Excel41,533 Topics
- Formulas and Functions24,040 Topics
- Macros and VBA6,239 Topics
- Office 3655,789 Topics
- Excel on Mac2,568 Topics
- BI & Data Analysis2,268 Topics
- Excel for web1,835 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,576 Topics