User Profile
shawb
Copper Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Return Address of Max Value in a Range --modification needed
Hi all, I've programmed a worksheet to run a set of quality assurance checks on data from other sheets in the book... In the attached file, the QA assesses data (view sheet notes for details) on the Facility and Project sheets and uses conditional formatting to highlight flags/concerns. Starting in C16 on the QA sheet,I'm using the following set of functions within a larger formula to return the address of the building type on the Facility sheet with the largest percent floor area: INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")) So far, this works fine, EXCEPT in one scenario... MY PROBLEM: Match returns the row number in terms of the lookup array so in order to get "Facility!E17" instead of E1, I believe I need to use the lookup array highlighted above I run into an issue when the max value (aka lookup value) is 1 (100% floor area is max) because participants can enter 1 as a response in Facility!E13:15 (Feel free to play with the Facility data to see how this affects QA) I really don't know what I can do differently so I need help!! How can I modify either the formula (preferable) or the cells on the facility tab so that a 1 is distinguishable from 100%? The building type cells have data validation for decimal 0 and 1 and are formatted for percentages. Since the QA sheet needs to be pasted into 60+ similar files, I'd rather not have to do too much manual manipulation to get this resolved.Solved1.9KViews0likes5CommentsWord crashes when trying to view chart templates
Hi all, Trying to insert a chart using a pre-saved template but every time I click "Templates" in the "Insert Chart" dialogue box, the entire program crashes... Any ideas? Just brought my Office 365 up to date and the problem persists.1.5KViews0likes1CommentCurly brackets added to formulas once workbook is copied and emailed out
[SOLVED] Hi all, I'm having an issue where my formulas change after the workbook is opened on other PCs. For example, here's how a formula appears in my workbook, where I calculate with just the enter key stroke, no CSE ever used: =IFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes"),"Both",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="no",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0)),"Heated Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="No",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0)),"Cooled Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0,OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0),"Fields left blank") The same formula appears as follows after I protect and hide cells, protect sheet, save, copy, attach to email, send: {=IFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes"),"Both",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="no",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0)),"Heated Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="No",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0)),"Cooled Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0,OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0),"Fields left blank")} Where do the curly brackets come from? It returns a VALUE! error because the formula does not compute with CSE shortcut... On the recipient's PC, as soon as you put the formula in edit mode, the curly brackets disappear and the computation happens normally. Plus, I don't think this formula can return an array given that I've populated the width and length returned by the offset function. Any ideas?Solved4.5KViews0likes1Comment
Groups
Recent Blog Articles
No content to show