I need some help
hello how are you? i need some help with some issues that i am having with excel. my goal is send the invoice by email to the vendor, this is the error that i am getting this is the invoice i need to choose the vendor and date to display my order, this info come from Database I will give you full access to take a look, be my guest if you need to change something for better improvement, I will be thankful, thank you so much for your help Jeng Chi Inventory 2025 Template.xlsmWorking with Arrays of Ranges
OK, so strictly speaking an array of ranges is an error in Excel right from the outset. However, I set up an array of tables contained within the named Range 'allocatedRange', here shown with a red outline. I then set up a function that would return the range corresponding to a country SelectDataλ = LAMBDA(country, LET( countries, FILTER(TAKE(allocatedRange, , 1), markers = "Country:"), recordNum, FILTER(SEQUENCE(ROWS(allocatedRange)), markers = "Country:"), recordEnd, VSTACK(DROP(recordNum - 1, 1), ROWS(allocatedRange)), rangeRows, recordEnd - recordNum, countryArrϑ, MAP( recordNum, rangeRows, LAMBDA(start, rows, LAMBDA(TRIMRANGE(TAKE(DROP(allocatedRange, start), rows)))) ), XLOOKUP(country, countries, countryArrϑ)() ) ) The start and row count of each table is determined by using the string "Country:" as a marker and differencing row numbers. A combination of TRIMRANGE/TAKE/DROP picks out each range in turn and assembles them into an array by turning the range references into thunks (TYPE=128). The function SelectDataλ is used to look up any specific country and return the corresponding range. To demonstrate that the function indeed returns ranges, the COUNTIFS function is used to count the number of cities within each country that have a population exceeding 100 000. = LET( countries, FILTER(TAKE(allocatedRange,,1), markers="Country:"), countLarge, MAP(countries, LAMBDA(country, COUNTIFS(TAKE(SelectDataλ(country),,-1), ">1000000") ) ), HSTACK(countries, countLarge) ) The point of this post is to introduce the idea of treating tables as elements of an array, so allowing further tables to be inserted or removed dynamically. TRIMRANGE ensures that each range is sized correctly and MAP is used to analyse each table in turn. Whilst Excel throws hissy fits at being asked to store arrays of arrays, arrays of ranges, or nested arrays, it is perfectly happy to store arrays of functions. When evaluated, each element of the function array is perfectly free to return an array or even a range. The effect is to permit Excel to process 'arrays of Tables' faultlessly.
Modifying Delete Row based on Extract from Text
Column A has two repeating rows that need to be removed.Column A contains: column1 total amount I am not proficient enough to modify the following to delete these rows Sub DeleteRows() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(1).EntireRow.Delete For i = iLastRow To 1 Step -1 If Cells(i, "A").Value = "" Then Rows(i).Delete End If Next i End Sub Appreciate the help LExplore our latest Microsoft Security training on Microsoft Learn
Connect with popular security skill-building offerings and resources from Microsoft Ignite 2024 From simplifying infrastructure management and improving regulatory compliance to better navigating the modern cyberthreat landscape and building a security-first culture with AI, Ignite 2024 covered a ton of ground. Even if you didn’t join this year’s conference, you can explore expert content to inform and foster your security journey on our Security hub on Microsoft Learn. Designed for learners at all levels, the Security hub on Microsoft Learn is your go-to resource for security skill-building offerings, now easier to find, based on your interest and objectives. Find expert guidance aligned to your security journey. Whether you need to build foundational security skills, gain specialized knowledge, or prove your capabilities with Microsoft Credentials, get the guidance you need. Explore the latest resources organized by security focus area. Learn to understand advances in Zero Trust, identity and access, security operations, IT security, and much more. Connect with like-minded communities, partners, and other thought leaders. Join the conversation, and get inspired to level up your skills and knowledge. Validate your cloud skills with Microsoft Learn challenges According to research examined by Science Direct, gaining complementary AI skills can increase wages by an average of 21%. Starting November 19, 2024, at 4:00 PM (16:00) UTC and running through January 10, 2025 at 4:00 PM (16:00) UTC, you can unlock new AI skills the Microsoft Learn Challenge | Ignite Edition. The Challenge includes eight expert-led learning paths: Prepare for next generation data analytics with Microsoft Fabric: Accelerate your career as a data analytics professional. Learn how to connect, ingest, store, analyze, and report on data with Microsoft Fabric. Prepare for Exam DP-600 and your future as a Microsoft Certified Fabric Analytics Engineer. Build trustworthy AI solutions on Microsoft Azure: Gain knowledge on secure, reliable AI with Microsoft Azure. Learn responsible AI practices, content filtering, and model evaluation while mitigating risks to meet the highest safety standards. Innovate & secure your migration to Microsoft Azure: Learn how to migrate and manage your workloads with Microsoft Azure, tools, and services. Bring Azure innovation to your environment with improved scalability, security, and reliability. Create agents in Microsoft Copilot Studio: Learn how to migrate and manage your workloads with Microsoft Azure, tools, and services. Bring Azure innovation to your environment with improved scalability, security, and reliability. Microsoft 365 Copilot for administrators: Master Microsoft 365 Copilot security and compliance. Prepare data, configure tenants, assign licenses, drive user adoption, and optimize Copilot experiences with extensibility options. Secure your data in the age of AI: Learn to adapt security strategies for evolving AI tech. Protect endpoints, data, and apps with Microsoft Purview and Defender. Stay ahead in safeguarding your organization's AI infrastructure. Get started with Microsoft Copilot for Security: Learn how Copilot can help secure your organization at machine speed! Explore generative AI, understand Microsoft Security Copilot features, and gain skills to use in both embedded and stand-alone experiences. Build AI apps with Microsoft Azure services and best practices: Learn to build cloud-native AI apps, create back-end databases, and integrate Azure OpenAI services. Gain practical skills for scaling AI and develop AI solutions for your organization on Azure. Ready to level up on your AI skills journey? Register for the Microsoft Learn Challenge today. Prove your real-world technical expertise with our latest Microsoft Applied Skills Professionals focused on data security and threat protection can demonstrate and differentiate their expertise by earning these new Microsoft Applied Skills: Implement information protection and data loss prevention by using Microsoft Purview: Demonstrate your ability to implement Microsoft Purview Information Protection and DLP, and validate your ability to discover, classify, and protect sensitive data in Microsoft 365, effectively implementing data security by using Microsoft Purview. This assessment is particularly relevant for information protection and compliance administrators, in addition to security operations Analysts. Implement retention, eDiscovery, and Communication Compliance in Microsoft Purview: Earn this Applied Skill by proving your ability to implement retention, eDiscovery, Communication Compliance, and content search in Microsoft Purview. This could be an especially good fit for compliance administrators who are familiar with Microsoft 365 services and Microsoft Purview and have experience administering compliance in Microsoft 365. Defend against threats using Microsoft Defender XDR: Earn this credential by demonstrating your ability to use Microsoft Defender XDR to detect and respond to cyberthreats. Candidates for this credential should be familiar with investigating and gathering evidence about attacks on endpoints. They should also have experience using Microsoft Defender for Endpoint and Kusto Query Language (KQL). Take our most up-to-date Virtual Training Days for free No matter your security skill level, our free Microsoft Security Virtual Training Days will help you gain the technical skills and knowledge you need to enable employees to work securely and achieve more from anywhere. To keep pace with today’s fast-moving security landscape, we updated three of our most popular Virtual Training Days: Modernize your SecOps with Microsoft Sentinel: Learn how to deploy your Microsoft Sentinel SIEM instance, migrate your existing rules, and add content hub solutions including data connectors, analytic rules, hunting queries and workbooks. These solutions enable you to perform detections, investigations, incident management, and threat hunting. Additionally, you can learn how to optimize your security data to maximize your coverage and better manage costs. We will also demonstrate how Microsoft Security Copilot can help security operations teams to move faster with skills like guided response, natural language to KQL translation, and analysis of malicious scripts. Implement data security with Microsoft Purview: Learn how to discover sensitive data, identify critical data security risks, and dynamically tailor DLP controls using Microsoft Purview solutions including Information Protection, Data Loss Prevention, Insider Risk Management, and Adaptive Protection. The session will explore practical use cases for these products, demonstrating how they can secure AI applications and analyze organizational risks. You'll learn to protect data across generative AI tools like Microsoft Copilot for Microsoft 365 and third-party AI applications, implement dynamic protections to prevent data leaks, and ensure compliance in an AI-first world. Defend against threats with extended detection and response: Learn how to perform investigations and remediations with Microsoft Defender XDR and Defender for Endpoint. You will be introduced to the Unified Security Operations Platform (SIEM in XDR), and will see how to deploy the solution and use Microsoft Sentinel capabilities in Microsoft Defender XDR. Microsoft Sentinel SIEM in XDR topics also include SOC Optimization. They will perform advanced hunting using KQL queries, remediate security alerts, and perform detections, investigations, and threat hunting in Defender XDR. You will also learn how attack disruption works with incidents and alerts, and how to use Microsoft Security Copilot to investigate and perform incident management.Extract Same Table from Multiple PDFs
I tried to modify the recorded macro to accept another PDF in table 002 with the next monthly summary table (table001 & table002 confusion). All of the files are in the same folder numbered incrementally, 1-12 (Jan-Dec). There are other similar folders for different accounts requiring the same procedure be performed and are set up in similar increments by account. Would like all of the monthly summaries from the PDF table 001 listed within one worksheet or workbook. How can this macro perform the function described? Sub ExtrctMonthlySum() ' ' ExtrctMonthlySum Macro ' ' ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""T:\Traverse\Church International & Triumphant\DCTC\Financial\Treasurer\2024\Acct 1893\2.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table002,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [Table002 (Page 1)]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table002__Page_1" .Refresh BackgroundQuery:=False End With Range("C5").Select End Sub
Graphing Multiple Series on one Graph
SampleSheet Hello all, I have been asked to Graph a variety of different data series into one graph to see if any may relate to each other. The graph is intended to be a line graph, having 5 different lines, representing 5 different data series. I believe this is possible, but just cannot remember how to do it. An issue that I'm having is that the Data series has such different values, ranging from a series having values of <1.0 to series having values of >20,000. Is it possible to have a 'break' in the y-axis so that the gap between say... 100 on the y-axis to 20,000 on the y-axis is non-existent or would it be recommended to just add a secondary Y-axis for this series? The dates are of significance, and should be on the x axis to show the timeline of these different values. Any help here would be appreciated as my direct report has been asking this of me, and we're both stumped. I've attached a spreadsheet with the 5 series included. Is anyone able to have these 5 series added to the same line graph in a legible manner without adjusting their values? Thank-you so much in advance. Toon
How to delete multiple cells based off the value of a specific cell.
I have an excel file with multiple sheets. I was wondering if there was a way to complete this task. When I delete the value in the column of "Hose"(A), I would like it to automatically delete everything in C9:K9. Looking at the image uploaded, when I delete "811HT-24", it would then delete RCC,x,7458,1x24,2x24,3x32,1x48 if it worked like I'm hoping. In the B column I'm using VLOOKUP tied to an index. All of the sheets are identical as well. Any help would be appreciated.
