•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Creating a dynamic reactive sheet
Our take
Are you feeling overwhelmed by the limitations of your current spreadsheet process in the travel industry? You’re not alone. Many professionals find themselves stuck, manually updating quote sheets that lack the flexibility and speed they need. Imagine transforming this tedious task into a dynamic, reactive sheet that adapts to your changing needs. By integrating AI-driven tools and smarter formulas, you can create a streamlined system where your category data automatically populates across tables, saving you time and reducing errors.
I'm sure I may be asking for the moon - but it's worth a shot.
I work in a travel company and they require us to manually type out a quote sheet (format is barely there).
I wanted to make two tables that would speed things up.
The first table would have a table that has the following columns
Day & Date / Category / Arrangement / Unit Price / Quantity / Total / Remarks & Comments
I had C5 and E5 hold start and end dates and then had a macro basically populate the table according to the number of days in the expected tour. Problem I found was that if I ever wanted to go back and edit or change those it would be borderline impossible.
The next table would basically read the category column and if it found for example (Category = Accommodation) it would extrapolate the data and populate it.
Repeat this for all subsequent categories.
My current method is using
=IFERROR(
IF(
FILTER(Quote[Arrangement], Quote[Category]='Information Reference'!A7)=0,
"",
FILTER(Quote[Arrangement], Quote[Category]='Information Reference'!A7)
),
""
)
Any guidance would be great.
I tried using ChatGPT to help guide me through it and started learning about Macros.
I kept running into a wall that it would sometimes update and then nuke the original table - without that table all references are lost and the entire thing implodes.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formula to achieve more complex text to columns?Hi everyone! I'm back with my latest question in my journey to maximize excel's usefulness in my office! Currently, I'm trying to figure out how to autofill a table and from there auto convert to a chart. To explain: The spreadsheet is tracking productivity goals for our employees (specifically the goals are to move x amount of clients into different statuses each month). As the employees complete these tasks, they are posting a message in a central teams chat with the information status, client name, client number, caseload, and the date. Once a week, management goes through the chat and copies and pastes the information into a spreadsheet broken up by employee and status. Every month, they use the data from that sheet to fill in a chart that counts them and calculates percentage of the goals met. Management asked me to find a way to take the monthly task of filling in the chart off their hands by making that automated. The way I was going to do this was to convert their sheet into a table and then use the table to create the chart. However, upon opening the spreadsheet, I see how much management has been just copy and pasting the raw data underneath each caseload's heading. I don't want to make more work for them by making them fill in the table, so I'm trying to find a way to automate this to. I thought about text to columns, but everyone's doing things slightly differently in their posts in the teams chat so that makes this a little difficult. The status is pretty universally first with a dash between that and the name with is almost universally second. After that some people are not including separation between name and client number, some people are using commas, some people are using dashes, some people are using a pound sign, and some people are putting the leading zeros. So it's really messy. Obviously, I can ask management to set the expectation that this be uniform and they would be happy to do that. But I want to see if there's a way we can do this easily without changing the already existing process. Does anyone have ideas? Thank you for reading all of this and helping!! submitted by /u/tashykat [link] [comments]
- Xlookup reference is shifting over each time after I use MacroHey everyone, I have my main table where I want to display the cleaned up data. Essentially I'm using xlookup references from sheet two. When I run my macro in sheet two, for some reason my xlookup on sheet 1 moves over every single time. For example... this is what the xlookup formula looks at before vs after the Macro Before Macro - =XLOOKUP(A3,MTD!$A$3:$A$15,MTD!$B$3:$H$15) After Macro - =XLOOKUP(A3,MTD!$A$3:$A$15,MTD!$B$3:$F$15) Why would it move from H15 to F15? I'm a little lost why that would be. Sub Macro5() ' ' Macro5 Macro ' ' Range("A2").Select ActiveCell.FormulaR1C1 = "Market" Range("B2").Select ActiveCell.FormulaR1C1 = "Date" Range("E2:J16").Select Selection.Cut Range("C2").Select ActiveSheet.Paste Range("A2:H2").Select Selection.Copy Range("A19").Select ActiveSheet.Paste Windows("Overview Bullet-MTD.csv").Activate Application.Left = 1551.25 Application.Top = 147.25 Windows("Weekly Exec Updates - Lead Pacing (Working File) (1).xlsx").Activate Range("A20").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "MO" Range("A21").Select ActiveCell.FormulaR1C1 = "LA" Range("A22").Select ActiveCell.FormulaR1C1 = "AR" Range("A23").Select ActiveCell.FormulaR1C1 = "MS" Range("B20").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-17]C[-1]:R[-4]C[-1],R[-17]C:R[-4]C[6])" Range("B20").Select Selection.AutoFill Destination:=Range("B20:B23"), Type:=xlFillDefault Range("B20:B23").Select Range("B21").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-18]C[-1]:R[-5]C[-1],R[-18]C:R[-5]C[6])" Range("B22").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-19]C[-1]:R[-6]C[-1],R[-19]C:R[-6]C[6])" Range("B23").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[6])" Range("B3:B16").Select Selection.NumberFormat = "m/d/yyyy" Range("E3:E16").Select Selection.NumberFormat = "0.0%" Selection.NumberFormat = "0%" Range("G3:G16").Select Selection.NumberFormat = "0.0%" Selection.NumberFormat = "0%" Range("C20:D23").Select Selection.NumberFormat = "#,##0" Range("F20:F23").Select Selection.NumberFormat = "#,##0" Range("G20:G23").Select Selection.Style = "Percent" Sheets("YTD").Select Windows("Overview Bullet-YTD.csv").Activate Windows("Weekly Exec Updates - Lead Pacing (Working File) (1).xlsx").Activate ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 Range("M2:M16").Select Selection.Cut ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("D2").Select ActiveSheet.Paste Range("D2").Select ActiveCell.FormulaR1C1 = "Actual Leads" Range("K2:K16").Select Selection.Cut Range("E2").Select ActiveSheet.Paste ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 Range("L2:L16").Select Selection.Cut Range("F2").Select ActiveSheet.Paste Range("H2:H16").Select Selection.Cut Range("G2").Select ActiveSheet.Paste Range("I2:V17").Select Selection.ClearContents ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("A2:G2").Select Selection.Copy Range("A22").Select ActiveSheet.Paste Range("A23").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "MO" Range("A24").Select ActiveCell.FormulaR1C1 = "LA" Range("A25").Select ActiveCell.FormulaR1C1 = "AR" Range("A26").Select ActiveCell.FormulaR1C1 = "MS" Range("B23").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[5])" Range("B23").Select Selection.AutoFill Destination:=Range("B23:B26"), Type:=xlFillDefault Range("B23:B26").Select Range("B24").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-21]C[-1]:R[-8]C[-1],R[-21]C:R[-8]C[5])" Range("B25").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[5])" Range("B25").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-22]C[-1]:R[-9]C[-1],R[-22]C:R[-9]C[5])" Range("B26").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-23]C[-1]:R[-10]C[-1],R[-23]C:R[-10]C[5])" Range("A2:G16").Select Selection.AutoFilter ActiveSheet.Range("$A$2:$G$16").AutoFilter Field:=1, Criteria1:=Array("AL", _ "CA", "HI", "NM", "NNE", "OHWVKY", "PA", "TW", "TX"), Operator:=xlFilterValues Range("A3").Select ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort.SortFields.Add2 Key:=Range( _ "A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Sheets("MTD").Select Range("A2:H16").Select Selection.AutoFilter Range("A3").Select ActiveSheet.Range("$A$2:$H$16").AutoFilter Field:=1, Criteria1:=Array("AL", _ "CA", "HI", "NM", "NNE", "OHWVKY", "PA", "TW", "TX"), Operator:=xlFilterValues ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort.SortFields.Add2 Key:=Range( _ "A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub submitted by /u/_mavricks [link] [comments]
- Data Automation - Pulling data from a sheet but also blocking cellsMy main purpose for this is to be able to create a trackable booking spreadsheet, the main input page is where the kids are booked onto sessions this is then checked against when they enrol and leave. I have Data in my 'Input' sheet this is split into 5 sessions per day 5 days. Column A is the name Column B:F is Monday 5 sessions Column G:K is Tuesday 5 sessions and so on I then have a sheet 'Apr 26' which then pulls this data into the monthly sheet The data i have in my input sheet can have anywhere between 1 cell to 12 cells per session, my formula works if there is 2 cells or more per session but doesnt work if only 1 cell it puts that cells data over multiple cell. Excel Formula Code (PasteBin) Excel Doc Link - ignore sheets past May 26 as they dont have the updated code or conditional formatting in. Excel Doc Link (Non Macro) This is my main input data sheet This is the sheet and cell (highlighted) that the formula relates to As you can see in the above images on Monday on the ASC 5pm column it multiples the data (child 1) If I have 2 lots of data in the ASC 5pm Monday column on the input sheet the formula works properly. Input sheet is how sessions per child are booked. sessions can only have 12 in however on certain days due to staffing can only have a certain number in (say 9) the formula im having trouble with is on sheet Apr 26 on column F it repeats the 'child 1' cell multiple times rather than just doing It once but as you can see on column K the formula works but just not when its got 1 cell of data Version Excel 2024 on Mac but needs to work on windows as well submitted by /u/BarrowBluebird [link] [comments]
- Summarise 2D Dynamic Array?Using Excel 365 on Windows 11 This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…) I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year. A simplified version of the sheet currently looks like this: https://preview.redd.it/li08zsp8w7rg1.png?width=584&format=png&auto=webp&s=2d61ac0c1d887bb3b18bb3f08a9542fbc72f439b Data entry in B4:E10 Months dynamic range (pink) calculated in C14 =EOMONTH(D2,SEQUENCE(1,F2,0,1)) and spills C14 to AL14, or wherever the last month is. Categories dynamic range (pale blue) calculated in B15 =FILTER(B4:.B10,B4:.B10<>"")) and spills B15 to B20 here. Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually. The Forecast dynamic range (green) is then =XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0)) All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#. This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula Anyone got a good way to deal with this? Thanks. submitted by /u/sprainedmind [link] [comments]