Forum Widgets
Latest Discussions
Vlook up issue related to format of cells between two different files
I want to apply the vlook formula for working purposes, the issue is : I have two files file A Nace codes file B analysis There are 616 rows in file A nace codes while in file B there are 32. When I apply the vlookup formula to find which of these 32 records from file B analysis are found in file A nace code i get no results (always #N/A). I have tried changin the format of the columns still the same results in order to have the same format but still the same results formula applied =VLOOKUP(B2,'[file B analysis.xlsx]Same G diff Code same Name 32'!$B$2:$F$33,5,false)
Script changing dates to text
I"m trying to make a script to transform a column with dates to text. Normally i would just use the TEXT function but the issue is that the new text values need to replace the old date values. The dates are now in three columns with the number notation date and it needs to be a text with the form "dd/mm/yyyy". Since i need to do this for at least 50 files i tought about using a script to make a new column & use the text funcion to get the new values for all three columns Change the date notation on the new values to text copy the new text values to the old column Delete the new column. The issue i meet is at the second stap that the values are in a table and are not all the same length. At the moment i have this for first step in a script function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Insert at range E:E on selectedSheet, move existing cells right selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right); // Set range E2 on selectedSheet selectedSheet.getRange("E2").setFormula("=TEXT([@Geboortedatum],\"dd/mm/jjjj\")"); // Insert at range Q:Q on selectedSheet, move existing cells right selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right); // Set range Q2 on selectedSheet selectedSheet.getRange("Q2").setFormula("=TEXT([@[Datum Resultaat]],\"dd/mm/jjjj\")"); // Set range S2 on selectedSheet selectedSheet.getRange("S2").setFormula("=TEXT([@Startdatum],\"dd/mm/jjjj\")"); } And as second step function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Set format for range E:E on selectedSheet selectedSheet.getRange("E:E").setNumberFormatLocal("@"); // Set format for range Q:Q on selectedSheet selectedSheet.getRange("Q:Q").setNumberFormatLocal("@"); // Set format for range S:S on selectedSheet selectedSheet.getRange("S:S").setNumberFormatLocal("@"); } But i'm stuck at the third step. If there is an easier method that would be fine to
How to protect workbook and still open hyperlinks
Hello, I have a workbook that I want to make available to the entire company that shows the status of tasks for a certain process. I want everyone to be able to see the information but not make any changes. I know I can restrict access to prevent that. However, there are hyperlinks to documents in the workbook that users DO need to be able to open. It appears that restricting access to the workbook does not allow users to open (download) the documents via the hyperlinks. Is there a way to allow users to open hyperlinks and still protect the workbook from anyone making any changes? Thank you!!
How to get all possible permutation in VBA
Hi, We all know Benjamin Franklins Square. How can I Get VBA to export a spreadsheet with all possible permutations for numbers 1-9. Filter out duplicates of the same number Filter out only combinations that the sum matches the criteria below First 3 digits = 15 Second 3 digits = 15 Third 3 digits = 15 1st, 4th, 7th = 15 2nd, 5th, 8th = 15 3rd, 6th, 9th = 15 1st, 5th, 9th = 15 3rd, 5th, 7th = 15 I hope you are following what I'm trying to do. Thanks in advance, Uncle BearNeed help with a nested IF w/ISNA vlookup formula
A formula that I have been using for nearly 4 years has decided to stop working and I don't know how to fix it. Everything I have tried fails miserably. The formula is this: =IF(OR($L5="Vacant",$L5="Unfunded",$L5="Intern"),$L5,IF(ISNA(VLOOKUP($M5,ActiveFTE[Employee ID],1,FALSE)),"Term","Yes")) Where M5 is the employee ID number. an the Active FTE is the current list of employees. In the past if the person was no longer in the Actice list it would put Term and if it was there, Yes. now, it returns #N/A - the ONLY thing that has changed from the last file and this file is the list of active employees. if I remove the first IF/OR part of the formula it works beautifully, but I also need to flag vacant and unfunded positions. I've tried ISNA,. IFNA, IFERROR as well as putting the IF/OR at the end instead of the beginning. otherwise, any suggestion is greatly appreciated cathy
SORTBY newb... Other solutions don't appear to apply??
Hello all! Okay, here is the situation. I have an Excel spreadsheet and each row has a dropdown that allows you to set the priority of the task. The dropdown has approximately six separate items (Urgent, High, Medium, Low, Complete, Review, etc.). I set up a formula to change the row color according to the dropdown (previously they were doing it manually) and it works great. But I also need the entire sheet to sort by the dropdown selection, i.e., it needs to group them by the dropdown selection and order them; all Urgent item rows are at the top of the sheet, High are next, Medium next, etc. SORTBY seems like it can accomplish this, but every example I have seen is dealing with a small range within the sheet and not sorting the order of entire rows. The end result I am looking for is to group rows in a specific order according to what was selected in their individual dropdown and sort those rows. I've seen great examples, but they all seem to just be sorting information in a small range within the sheet and not ordering rows. Thank you for any suggestions / assistance you can provide and I'm looking forward to learning!
