User Profile
Jn12345
Brass Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: Data Validation
Starting to get somewhere, however, no spill range is produced. Im completely unfamiliar with the reduce function so I am probably getting something wrong when I pump this in to my workbook. however another difference from my report to the one you have worked on is that my B1 range in this form is on B2 in the real form. I tried to just type in B2 where you had B1 but still doesnt work.Re: Data Validation
Hey! I think you are onto exactly what im looking for. However, when I add the formula in to my sheet, change the tables to the correct tables and ranges and then shange the sheet ranges from sheet 1 (has a different name since all sheets are always changing) to HIDDEN TABLES (the sheet at the end that will act in place of the end sheet we had on your example) and it doenst work. it keeps adding [HIDDEN TABLES] in front of HIDDEN TABLES in the formula. like this =IFERROR(DROP(REDUCE("",DROP(HSTACK('Sheet1:[HIDDEN TABLES]HIDDEN TABLES'!B2),,-1),LAMBDA(p,q,HSTACK(p,VSTACK(q,FILTER(ChargesTable[Charge Title],ChargesTable[Client]=q,""))))),,1),"")Re: Data Validation
Hey Hans, Thanks for the reply. The hidden column idea is an option that I explored (I should have explained that also), however, in the actual spreadsheet there are multiple filtered spill ranges and multiple lists. I will resort to that if I cannot figure out a way to build the formula into the data validation formula bar but ideally I stay away from that since I would need 10 to 15 hidden columns per page and part of me worries about hidden ranges becoming an issue in the future. If there isnt a simple solution then I will definitely go that route though. Thanks for the reply!Data Validation
Hello All, I have a data validation question. I created a costing spreadsheet that can pull data from a master spreadsheet so that multiple copies of the costing spreadsheet can always be current / up to date. This was created when there was only one potential client and I set it up in a way where the table on HIDDEN PAGES (sheet) is filtered into an adjacent filter function a few columns to the right of the table and that spill range is then named and used to make a data validation list on sheet 1. this is fin if there is only ever going to be one client because when I copy the first page to make more sheets (has to be done this way) the list on sheet 2 is still based off of the entry on sheet 1. See example spreadheet. Is there a way to make the data validation list dynamic for new sheets that are created by copying the sheet forward so that each new drop down list (column E on the sheet pages) are based on column B of those individual sheets?Table with multiple Header Rows to make a SUMProduct formula work?
Hello smart excel users, Tables are not my strong point and I have made one that is essentially a cost estimating spreadsheet. -The row above the rainbow coloured columns (the row in grey and white) is the actual table header. -The white cells are the editable cells that are in turn the lookup value for the Xlookup formula in the UOM and Rate rows so that my rates and units of measure can be calculated from a hidden table. -The reason the editable cells aren't the table headers in the "Hour Count" portion of the table are because A: the names are too long to fit in a single row header and B: there are 3 types of hours for each rate (ST OT and OT2) (I have the table header names partially hidden and are actually written as (ST_1, OT_1 etc. but have greyed out the _# portion of the name to look better) -The DTR Total column then does a sum product calculation for each row where it multiplies the sum of the units in each column by their corresponding rates in the rates row. My questions are - A: Is there a way to get the rates and units of measure etc. to be a part of the table to make life easier when I have to add or subtract columns to the table? At this point I always have to tweak a bunch of stuff every time I make a change. B: Is there a better way that I should be doing things?Re: Removing Screen flicker and adding efficiency to Copy Paste Macro
NikolinoDE Thanks for your help! It helped a lot. So i have another part of my code that updates from tables in one workbook into the destination workbook and I have noticed that when I do something similar to what you showed me, everything works except since the tables can be varying in size from source to destination that sometimes If the source table isnt the same size as the destination then I either get values going outside of the table and not making the table bigger (if destination table size is smaller in row count) and if the destination table is bigger than the source table then I get #N/A in the blank cells in the table (SEE ATTACHED). I didnt get this issue when I was doing copy paste but when I copy paste I then get a big screen flicker so im trying to make this work. Please see attached and If there is any insight into how to make it work that would solve everything! This is the code: Sub ImportMasterData() Dim OpenBook As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False Set OpenBook = Application.Workbooks.Open(Environ("userprofile") & "\360 NDE Inc. Dropbox\`Technician Reference\~Source Codes\SourceCodes") 'Clear Contents from the tables ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_Equipment").DataBodyRange.ClearContents ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_ConsumablesTable").DataBodyRange.ClearContents ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_AcceptanceStandards").DataBodyRange.ClearContents ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_InHouseProcedures").DataBodyRange.ClearContents ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_OurTechs").DataBodyRange.ClearContents ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_ClientsAndAddresses").DataBodyRange.ClearContents ThisWorkbook.Worksheets("HIDDEN TABLES").ListObjects("TABLE_ClientPersonnel").DataBodyRange.ClearContents 'Put new values into the new tables ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_Equipment[Equipment Sub Category]:TABLE_Equipment[Thickness (mm)]").Value = OpenBook.Sheets("Equipment").Range("MasterEquipmentListTable[Equipment Sub Category]:MasterEquipmentListTable[Thickness (mm)]").Value ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_ConsumablesTable[NDE Method]:TABLE_ConsumablesTable[Product Number]").Value = OpenBook.Sheets("Consumables").Range("ConsumablesTable[NDE Method]:ConsumablesTable[Product Number]").Value ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_AcceptanceStandards[Acceptance Standard(s):]:TABLE_AcceptanceStandards[Acceptance Standards Revs]").Value = OpenBook.Sheets("Procedures & Codes").Range("AcceptanceStandardsTable[Acceptance Standard(s):]:AcceptanceStandardsTable[Acceptance Standards Revs]").Value ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_InHouseProcedures[NDE Method:]:TABLE_InHouseProcedures[Technique Rev]").Value = OpenBook.Sheets("Procedures & Codes").Range("ProceduresAndTechniquesTable[NDE METHOD:]:ProceduresAndTechniquesTable[Technique Rev]").Value ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_OurTechs[Lead Techs]:TABLE_OurTechs[CWB Expiry]").Value = OpenBook.Sheets("Personnel & Contact Info").Range("OurTechsTable[Lead Techs]:OurTechsTable[CWB Expiry]").Value ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_ClientsAndAddresses[Unique Clients]:TABLE_ClientsAndAddresses[PostalZIP]").Value = OpenBook.Sheets("Personnel & Contact Info").Range("ClientsAndAddresses[Unique Clients]:ClientsAndAddresses[PostalZIP]").Value ThisWorkbook.Worksheets("HIDDEN TABLES").Range("TABLE_ClientPersonnel[Client Personnel & Excavation Contractor Company Name]:TABLE_ClientPersonnel[Position]").Value = OpenBook.Sheets("Personnel & Contact Info").Range("ClientPersonnel[Client Personnel & Excavation Contractor Company Name]:ClientPersonnel[Position]").Value OpenBook.Close True Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End SubRe: Removing Screen flicker and adding efficiency to Copy Paste Macro
Niko! thanks! Looks like it works well. I have tested it in multiple scenarios. The only thing i am wondering is if there are any scenarios where it wouldnt be good to continue with opening the source book if it is already open. like if I am working on the source book and I go to upload into this report and it closes without saving and I lose data. Is it better to just close with saving or maybe if its already open have a pop up saying it is open and to close and try again??Removing Screen flicker and adding efficiency to Copy Paste Macro
Hello smart excel users, I have a macro that I've made that pulls data from other reports. I typically get to use .range = .value2 because I use table 99% of the time, however, in the case of my stand alone reports I cannot use tables and I have to use merged cells. because of this, I have not been able to come up with a good macro that removes the need to copy and paste data a bunch of times between workbooks and it causes the screen to flicker like crazy when going back and forth between the two workbooks (disabling screen updating will not work as its two separate windows) and unfortunately I need a bunch of copy pastes instead of just one due to the limitations revolving around copy paste of merged cells and paste area size differences..... I'm sure there's a way to get all the data that I require to be pulled over without the extensive back and forth but I have not found a solution yet. I have attached two sample workbooks. The one which is TEST REPORT has a button that can be clicked. It prompts you to select the source data that I have also attached and should pull the data from the source report. Please let me know If you have any ideas on how it could run smoother and potentially remove the horrendous screen flickers.
