Excel Help
Is there a way (either formula or macro /something) for the following. I have 2 different spread sheets (files) open at the same time. First is called Time Card Second is called Wages Time Card will have a staff members details as well as dates and shift times. (20 Tabs for different staff members and their details) Wages will have Tab 1 - Summary of all staff names, hourly rates, hours worked for each day and gross amounts to be paid Tab 2 - 20 payslips with the above mentioned details, but payslip style. Lets call the first person Joe Deer I need something in Time Card next or close, to this person's name, when clicking it it will jump to his payslip in Wages sheet As mentioned. Formula or macro.. Guess anything will do. Thanks
Macros blocked in shared OneDrive Excel workbook as untrusted source
I have a macro-enabled Excel workbook (.xlsm) stored in OneDrive and shared with another user. When they open the shared workbook in Excel Desktop, they receive the error: "Microsoft has blocked macros from running because the source of this file is untrusted." We've already tried enabling macros in Trust Center, adding Trusted Locations, and opening the file from a synced OneDrive folder rather than Excel Online, but the error persists. Since downloading the file creates a separate copy and defeats the purpose of a shared workbook, what is the correct way to allow VBA macros to run in a shared OneDrive-hosted workbook while keeping everyone working from the same file?
Pivot Table
Hi everyone, I have an issue with the pivot table. There are filters from slicers and row labels in the table; when I double-click on any category from the table to see the filtered data, Excel fetches all data, not just what I filter on. Like below, I filtered from the slicer, and from the row labels, (Bills) should be between 100,000 and 200,000. I would like to see the (Bills) for (Central) in the (Start), but it gives me 632,478 and bills less than 100,000 and 200,000, not the 3 clients. Even if I tried from (In Progress), it's the same; it brings all data. The issue is only with the Bills column, but other filters come up correctlySeries fill a formula down a column automatically skipping a set number of rows
I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it from "spilling" I need the formula to increment every 6 rows, having one blank row between each section. I have been able to accomplish "copying" it each 6th row with VBA #1 and filling the series with VBA #2 but can't figure out how to combine the two. Any help would be appreciated. VBA #1: Sub FillEvery6thCell() Dim ws As Worksheet Dim startRow As Long, lastRow As Long, col As String Dim formulaText As String Dim r As Long ' Set your sheet and parameters Set ws = ThisWorkbook.Sheets("Top 5 Employees") col = "A" ' Column to fill startRow = 10 ' First row to start filling lastRow = 60000 ' Last row to fill ' Get the formula from the starting cell formulaText = ws.Range(col & startRow).Formula ' Fill every 6th cell For r = startRow + 6 To lastRow Step 6 ws.Range(col & r).Formula = formulaText Next r Range("A2:A60000" & iRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 MsgBox "Formula copied to every 6th cell in column " & col End Sub VBA #2 Sub FillFormulasDown() Dim sourceCell As Range Dim fillRange As Range ' Define the cell containing the formula Set sourceCell = Range("A2") ' Define the target range Set fillRange = Range("A2:A5000") ' Fill formulas down sourceCell.AutoFill Destination:=fillRange, Type:=xlFillSeries End Sub
How to split Excel file into multiple files?
I’m facing an issue with a very large Excel workbook and need some help. The file contains thousands of rows of data, and now it has become extremely slow to open, edit, and share through email. Sometimes Excel even freezes while working on it. Because of this, I want to split the Excel file into multiple smaller files, but I’m not sure how to do it properly without losing formatting or data. I tried manually copying rows into separate files, but it is taking too much time and there are chances of missing important records. I also searched online for solutions, but most methods seem complicated or only work for small datasets. This Excel file is very important for my office work, and I need a reliable way to divide it into multiple files based on rows or column values. If anyone knows an easy method, VBA solution, or any trustworthy tool that can split Excel files automatically, please share the steps. Any help would be greatly appreciated!
A new Excel Think Tank
After nearly 30 years of using Excel commercially, I am now coming to retirement. But before I finally hang up my Excel boots, I have setup a small Excel think tank. The idea being people can send me their issues and I will work with you to build your permanent solution in Excel. I have created a number of solutions from Email Validator, Automatic dashboard creators, Fraud analysis, Auto resume makes, Music Syns (All in Excel), so if give it try.
