Forum Widgets
Latest Discussions
Data info in separate date columns
I need to put data information in separate date columns in the same excel project. Let me give you an example. The project have 4 quarters lasting 3 months each. I need to put data information on each quarter column. People who use our service and we put information in quarterly report column. See this example below. Name. - age - gender - race Quarter 1 (Jan to March). Quarter 2 (April to June) Quarter 3 (July to Sept), Quarter. 4 (Oct to Dec) I need to separate the group of data into each quarter column so it won't repeat in other quarters using the formula on excel. I have used COUNTIF to calculate the group for each quarter column. I tried DATE but I got error, "#NAME?"Davidbass67Jun 18, 2025Copper Contributor88Views0likes3CommentsSummarize multiple columns of varying row lengths into one column.
I am copying old data into into Column B and add my own findings to Columns D and G. I'm looking to summarize the data as depicted in columns P&Q. What I've got making the M&N columns is M: =IF(NOT(ISBLANK(A4)),A4,"") N: =IF(NOT(ISBLANK(B4)),B4, IF(NOT(ISBLANK(D4)),D4, IF(NOT(ISBLANK(G4)),G4,""))) I don't know how to account for a variable number of rows between sample ID for column and multiple items from the columns B,D,G printing on different rows in the desired data set. Thanks in advance.mrs070Jun 18, 2025Occasional Reader98Views0likes2CommentsFilter formula for looking up data in a table
Hi, I,m looking for a formula that can lookup and return results when I select the name and return the vehicles at the top eg - Andy Scooter Bicycle Car Bus Thanks for any help Scooter Bicycle Motorbike Car Bus Lorry HGV Andy x x x x Brian x x x x Carl x x x x x Dave x x xBazzaP87Jun 18, 2025Copper Contributor56Views0likes2CommentsHow do i filter from a table
1 2 3 4 5 6 7 8 9 Andy x x x x Bob x x Moh x x x x Stu x x x x Zee x x Hi, I want to know if I have a table similar to this - I want to be able to filter from the names and show which categories are outstanding for that person Example - Moh - 1,3,5,8 Thanks for any helpBazzaP87Jun 18, 2025Copper Contributor58Views0likes2CommentsUnable to Embed OneDrive Shareable Link in iFrame due to CSP Restrictions
Hi, Objective: We want to embed an Excel file (hosted on OneDrive for Business) within an <iframe> inside our Angular web application. The user should be able to: View the Excel file inside the iframe. Edit the content directly in-place. Save changes back to the original file in OneDrive. Architecture & Authentication Flow: We explored two approaches: Client-Side (Angular) Direct Graph API Access via Application Permissions We attempted to obtain a Microsoft Graph access token directly from the Angular app using https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token. However, this call fails with a CORS (Cross-Origin Resource Sharing) error, which prevents the token exchange in the browser. This is due to Microsoft not allowing CORS access to token endpoints for confidential flows from client-side applications for security reasons. We found no configuration or setting in Azure Entra (Microsoft Entra ID) that allows bypassing this. 2. Server-Side Graph API Access via Application Permissions To work around the CORS limitation and meet our goal of centralized company-owned file management, we shifted to the application permission flow on the backend: Our server (Node.js/.NET) uses a client credentials flow to acquire an app token. It performs the following tasks: Uploads the Excel file to a company OneDrive location. Generates a shareable editable link via the Microsoft Graph API (/createLink or /createUploadSession). Sends that link to the Angular client to render the file. Issue: iFrame Embedding Blocked by Microsoft CSP When we attempt to embed the shareable Excel URL into an <iframe> on the Angular page, the browser blocks the request with the following error: Refused to frame 'https://some-link.my.sharepoint.com/' because an ancestor violates the following Content Security Policy directive: "frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com teams.cloud.microsoft *.office365.com goals.cloud.microsoft *.powerapps.com *.powerbi.com *.yammer.com engage.cloud.microsoft word.cloud.microsoft excel.cloud.microsoft powerpoint.cloud.microsoft *.officeapps.live.com *.office.com *.microsoft365.com m365.cloud.microsoft *.cloud.microsoft *.stream.azure-test.net *.dynamics.com *.microsoft.com onedrive.live.com *.onedrive.live.com securebroker.sharepointonline.com". Is there any way to achieve our goal?Nirmal-MudaliarJun 18, 2025Occasional Reader50Views0likes1CommentRun Multiple sets of data through set formula's
Hello, First off thank you for taking the time to look at this question though I would not be at all surprised if I am completely wrong about what I actually need. One of my colleagues and I have been working on a calculator of sorts for and we're able to get the result we wanted when you run one set of information through it at a time. However, what we want to be able to do is run multiple sets of data through it otherwise it is not the time saver we are looking for. Here are some screen shots: This is not real data as I am not sure if that would get me in trouble and I made the layout basic just to illustrate what I am trying to do. The Orange boxes are the information you would enter, so the artice, site and so on. The blue information are the result we receive back from the formula's we have already entered obtained from a data sheet we have setup. I think the tricky part is when you fill in the orange information, data is pulled from our other sheet which fills out this table: That's what make it hard to do multiple at once as the table as far as I can figure out, can only be populated one-by one. The goal would be able to upload any number of sets of those article, site, vendor and QTY's groups, and be able to simultaneously run them all to gather the needed information to vastly speed up this process. If providing more details on any of the other formula's we have will help, please let me know and again, any advice would be appreciated.Elindel1121Jun 17, 2025Copper Contributor81Views0likes2CommentsVba
You can achieve this by writing a VBA macro to open the source workbook, copy the second row from the specified sheet, and paste it into the next available row in the destination sheet. Here's the VBA code for your requirement: VBA Macro to Copy and Paste Data Sub CopySecondRow() Dim srcWb As Workbook Dim srcWs As Worksheet Dim destWb As Workbook Dim destWs As Worksheet Dim lastRow As Long Dim srcFilePath As String Dim destFileName As String ' Define file paths srcFilePath = "C:\Users\YourUsername\Desktop\new folder 16\1-2days allocation.xlsx" ' Update with the correct path destFileName = "y1737.xlsx" ' Open the source workbook On Error Resume Next Set srcWb = Workbooks.Open(srcFilePath) If srcWb Is Nothing Then MsgBox "Source workbook not found!", vbExclamation Exit Sub End If On Error GoTo 0 ' Set source worksheet Set srcWs = srcWb.Sheets("21 allocation") ' Activate the destination workbook Set destWb = ThisWorkbook ' Assuming the macro runs from 'y1737' workbook Set destWs = destWb.Sheets("allocation") ' Find the last used row in the destination sheet lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1 ' Copy the second row (A2: last column) srcWs.Rows(2).Copy ' Paste into the next available row in the destination sheet destWs.Cells(lastRow, 1).PasteSpecial Paste:=xlPasteValues ' Enter "y1737" in the adjacent column destWs.Cells(lastRow, destWs.Cells(1, Columns.Count).End(xlToLeft).Column + 1).Value = "y1737" ' Close the source workbook without saving srcWb.Close False ' Clear clipboard to release memory Application.CutCopyMode = False ' Notify user MsgBox "Data copied successfully!", vbInformation End Sub Steps to Use the Macro 1. Open the "y1737.xlsx" workbook. 2. Press ALT + F11 to open the VBA Editor. 3. Go to Insert > Module. 4. Paste the above VBA code. 5. Update the srcFilePath with the actual path of your "1-2days allocation.xlsx" file. 6. Run the macro (CopySecondRow). This macro will: Open the "1-2days allocation.xlsx" workbook. Copy the second row from "21 allocation". Paste it into the next available row in "allocation" of "y1737.xlsx". Enter "y1737" in the adjacent column. Close the source workbook without saving changes. Let me know if you need any modifications!Rajesh2519Jun 17, 2025Occasional Reader77Views0likes2CommentsMacro Formula Relative Referencing
Hi there I am trying to create a macro to use at the end of every month to convert a report into a format the client requires. The amount of rows in the report will differ every month so using relative referencing. I cannot figure out how to get the formulas in Cells G2 & H2 (which I have added while creating the macro) to reproduce regardless of the amount of rows in the report? They always stop at row 14. Thanks for any help. Sheet attached. Quit new to this. Microsoft® Excel® for Microsoft 365 MSO (Version 2505 Build 16.0.18827.20102) 64-bit PC Windows 11 BusinessSolvedSusan1085Jun 17, 2025Copper Contributor154Views0likes9CommentsExcel will not let me click into cells or incorrect cell is selected randomly
Hi, This is an ongoing issue that I've been having for years across platforms, computers, versions of excel and workbooks. Basicially, one of two things happen: 1-I click on a cell but nothing happens, the cell is not selected 2-I click on a cell but the wrong cell is selected (Example: Click on cell B2 but excel will select C2) This will happen as frequently as a few times a day (across multiple workbooks) to every month (again, across multiple workbooks) In ALL cases cells are NOT protected Saving the workbook, closing then restarting excel fixes the problem. As I mentioned before, this has been ongoing for years now across multiple computers, workbooks, operating systems. All systems and versions of excel were fully up to date at the time of the error. Because this is happening over several different workbooks, I do not belive it is a workbook issue (especially when it doesn't happen 100% of the time to ANY workbook) Systems used: Macbook Pro (circa 2015), OS up to date - No longer have this computer Windows 8 (again, old computer) Windows 10 (old computer Windows 11 (2 different computers, currently owned) I have always kept excel and the OS fully up to date and I'm currently using the most recent version of Windows and Excel. In all cases, I have used built in trackpad and a 3 different wireless mice (with full batteries). Still randomly occurs. It's really just an annoyance and like I mentioned before, saving the workbook and restarting excel solves the issue... until next time (which could be later that day or a few weeks...It's very random.)CPeterson710Jun 17, 2025Copper Contributor204KViews9likes112CommentsStringing together formula in Excel to create one formula that looks at multiple factors
Hi, I am trying to write a formula that returns a YES or NO in one cell, based on several different sets of criteria for values in 2 other cells. I know how to write the formula for each different set of criteria, but i cannot work out how to string them all together so that all are considered for the YES/NO result. I have tried multiple ways of putting them together and it's just going way over my head and past my formula knowledge so would really appreciate some help in how to do it. (I've never managed to get my head around more complex OR functions which is what i think is needed here?) I have listed the separate formulas below: =AND(D10>=1, D10<=31, E10="Days NET") – YES or =AND(D10<=30, E10="Days after receipt") – YES or =AND(D10<=30, E10="Days after EOM") – YES or =AND(D10>=29, E10="Days after EOM") - NO or =AND(D$0>=29, E10="Days after receipt") - NO or =IF(D10>=30) - no In explanation: If the value in D10=30 AND the value in E10='Days NET', OR the value in D10 is less than 30 (regardless of value in E10), result is YES. Or If the value in D10=30 AND the value in E10 is 'Days after EOM' or 'DAYS after receipt', result is NO If the value in D10 is over 30, result is always NO (regardless of value in E10). D10 is an empty cell that can have any 2 digit number entered. E10 is a drop down list of 3 options only (Days NET, Days after EOM or Days after Receipt) Result formula is going in cell H10. I would really appreciate any guidance on how to create this formula. Thanks in advance!SolvedJenstarzieJun 17, 2025Copper Contributor62Views0likes4Comments
Resources
Tags
- excel42,821 Topics
- Formulas and Functions24,847 Topics
- Macros and VBA6,439 Topics
- office 3656,078 Topics
- Excel on Mac2,666 Topics
- BI & Data Analysis2,397 Topics
- Excel for web1,936 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,654 Topics