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
sum function with text
total noob here... after a quick search, i didn't find anything on this... i want to do a sum formula, but i also want to add text to the cell after the sum is completed. is there a way to add this to the formula? do i just do the sum formula, then go back and add the text to the box or will that throw an error? do i just add the text to the cell next to the formula? thanks in advance!
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 correctlySorting dataentries but keeping separation between subheadings
Hello! I would like to sort datasets with three columns, but keep the sets separate with different subheadings, but sort them all within themselves. I find it difficult to explain further with words but will attach images. In the first images the datasets are shown as entered and by default are sorted by the leftmost column, as that's how they're entered. I would however like to be able to sort by the right most column, but keep all the datasets separate. I have made a mockup in the second picture to show what I mean. Is this possible? Thank you in advance!Series 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
BYROW/BYCOL/MAP Variants for Nested Arrays + BENCHMARK
Hey everyone! I made some simple BYROW, BYCOL, and MAP variants that can return nested arrays, and I also made a BENCHMARK function for performance testing. Here's some code for testing: BYROW⊟ = LAMBDA(array, function, [orient], LET( me, LAMBDA(me, seg, LET( n, ROWS(seg), IF( n = 1, function(seg), IF( orient, HSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ), VSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ) ) ) ) ), IFNA(me(me, array), "") ) ); I didn’t put a huge amount of effort into polishing this but In my tests on my device, these performed a lot better than using REDUCE + VSTACK for the same kind of thing, so maybe it’ll be useful to someone. Really curious to see how people use it, and if something looks like it should be optimized or changed, say so. I'll update them regularly, fix bugs whenever I can. You can find the rest of them on my Gist pages: https://gist.github.com/Medohh2120/f565516bc636700adf5ba27fd8f0d19e, https://gist.github.com/Medohh2120/d9d04f56d93694aed9d0c49d516f0fbf.
Conditional formatting cannot be displayed when pasting using the camera method.
When I paste the table into a different area (using the camera method), the formatting for some cells is not properly displayed. These cells have “Conditional Formatting” applied, and their colors change based on their content. When I make changes to the original table, I can see that there is no issue and the colors update correctly. This problem also occurred when I used the table as a “linked image.” Visual Explanation; (The original table is on the left; on the right is the same table pasted using the camera method. Both are in the same file and tab.) When I copy and paste the hidden rows separately (using the camera method again), the conditional formatting appears. In other words, there is no specific issue with the rows where the “formatting styles” cannot be displayed properly: Unfortunately, I didn’t get any results when I tried the “shake” method. When I tried it on a new worksheet, the result remained the same. I also tried the “Repair Office” option, but the problem persists. Could there be a row height limit when copying this type of table using the “camera tool” (when capturing screenshots of conditional formatting) When the total row height in the tables exceeds a certain value, the “conditional formatting” properties of the table I move to another location using the “camera tool” do not display. When I copy 26 rows to another location with each row set to 32.75 units in height, all properties are displayed; however, when I change it to 33 units in height, the formatting starts to break. And this total value (26 × 32.75 = 851) isn’t consistent either. When I try to set individual height values for the cells to reach a total height of 851, the issue isn’t resolved. Sometimes I get proper display when the total height is 777, and sometimes when it’s 661. What do you think is the root cause of this problem I’m facing? How can I solve it? Office Version Information: Ref: https://learn.microsoft.com/tr-tr/answers/questions/5861040/excelde-tabloyu-ba-l-resim-olarak-yap-t-ramama-sor
Alternative to Pivot Table in Dashboard for better UI Experience
Hi, currently I am using Excel to build a dashboard for asset management, in particular it is used to track how many items remain after someone borrow it for a particular date range. The data comes from MS Form and has been populated into Excel. I have cleaned up and transform the original data using Power Query and the final form of my data to build the dashboard look something like this. I wish the dashboard to be able to show the following part bracket in red, which represent the details of items being borrowed. Currently I am using pivot table as my solution, as you can observe above. Is there any idea or solution to enhance the overall look of the dashboard? Basically, what I want is instead of directly on the Excel grid, can this be turned into draggable table pane?
