Forum Widgets
Latest Discussions
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
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!!
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!
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)
Building relationships in data model to leverage power pivot - circumventing unique key ID issue
When building relationships between two data tables in the data model of excel, you require a unique ID / key to relate the two datasets. This makes sense but I find it has an issue: It requires you to reduce the entirety of your dataset down to unique ID's in one of your tables so that it can do the 'one to many' relationship build. If you don't have unique ID's / key, the relationship between two tables often doesn't work. Yes you can use an intermediary table with unique ID's to link the two complete tables, but when using that intermediary table I find you are limited in what additional data rows you can bring into the combined power pivot table to provide context to the relationship. Once you start bringing in contextual rows from the separate tables, there ends up being a lot of repeated values for any numerical figure from the other table, which you are trying to aggregate. An example below of the power pivot table result (data from table 1 in blue, from table 2 in green) Product ID (Unique Key) Service Line Sales Amount COGS 169AKY T Shirts $1,000 $7,500 169AKY Sweaters $2,000 $7,500 169AKY Socks $3,000 $7,500 169AKY Turtle necks $4,000 $7,500 169AKY Pants $5,000 $7,500 COGS from table 2 also has the data broken out by Service Line (T-shirts, Sweaters, etc.), but will only show an aggregate COGS value for all service lines for each unique product ID, instead of breaking it out by Service Line. This is because the Service Line column is being inserted into the power pivot table from table 1, it breaks out sales by Service Line. If you insert the Service Line data column into the power pivot from table 2, COGS would be broken out by Service Line, and only an aggregate for sales would be produced. Ideally the power pivot would display sales AND COGS broken out by Service Line (and Product ID). Is there a best practice for building relationships (unique ID's) between data tables: Without reducing the richness / variety of columns and data in your original tables (when creating the unique ID)? Maybe 'group by' function in power query to obtain unique ID's before building the relationship? With retaining the ability to slice and dice the data by different characteristics (assuming they are present in both source tables) and have that detail presented in the power pivot. Thanks for the help! The humble ApprenticeFooter image cropping
Hi I'm experiencing issues when adding png images into footers. When I add the images into the footer, the bottom and right of each image is cropped when printed, no matter the size of the image. The print preview of the images looks correct. There is an additional oddity to this as follows - if I add a copy of one of the images into a cell in the main boy of the doc, then print the doc, all of the images print correctly. Any advice would be great.
