User Profile
AnthonyCappuccio
Copper Contributor
Joined Jan 20, 2020
User Widgets
Recent Discussions
Re: Example - Mechanical Inspection using AQL
SergeiBaklan Thank you for the suggestion. I will most likely end up adding the formula to the sheet where the table is located as the sheet involving the recording of the feature will have to maintain a certain appearance that will need this hidden. The new formula you updated for #2 was exactly what I was looking for. I was trying to ask you (in question 3) if adding the step about switching from yellow to white when data is entered would be difficult based on what you initially provided but I see now how you included it. . . One question I have regarding ALL: As I'm messing around with the file you sent I'm noticing when a value for AQL and Quantity are chosen that correlate to "ALL" in the TABLE - this results in all 20 piece #'s flagging yellow. How would I alter this so that "ALL" in the TABLE tab is reflective of the Quantity entered on TAB 1? [for example] The attached filed I added, the Quantity is 5 and AQL is 0.01%. From the table this is defined as ALL and TAB 2 highlights all 20 piece #'s listed on TAB 2. In this case there are only 5 parts in the quantity so ideally only 5 piece #'s would need to be highlighted on TAB 2. Essentially the connection I am making is if ALL is the chosen cell from the TABLE then the highlighted cells would be reflective of the exact quantity indicated on TAB 1. This is where I mentioned in the original post that I was skeptical about ranges and considered making this table much larger to include one number per lot size in fear of this exact need being to complex.12KViews0likes0CommentsRe: Example - Mechanical Inspection using AQL
SergeiBaklan Sergei - Thank you for taking the time to explain this so thoroughly. I am not nearly this experienced in Excel so to come up with this would be near impossible for me. I really do appreciate the help! A few questions: When you say it would be hard to maintain this as one formula are you referring to excel's ability to maintain it and process the formula within the formatting? Also, I did not mention this on the post but another key aspect of this whole thing is that I need the yellow cells to turn back to white after information is manually inputted. Meaning after the Quantity and AQL are entered on TAB 1, the purpose of this sheet will be for a mechanical inspector to input data into these yellow cells based on a tolerance range for a feature. Upon entering the value into the yellow cell on TAB 2 I would need the cell to then change from yellow to white signifying data has been entered. Is this an easy addition given the way you had written the formula on the excel file you had provided? wumolad Thanks for the heads up. I had taken this into consideration - I didn't want to complicate this example file too much but I definitely will be extending this past 20 pieces!12KViews0likes2CommentsExample - Mechanical Inspection using AQL
Hey Everyone, I am looking to create conditional formatting on a range of cells that will populate said cells based off of an AQL table. I have attached the excel file that shows the data and what I am trying to do. There is a text box on "TAB 2" that explains the goal of what I am trying to achieve. I believe I will need to use an equation to format the cells but based off prior research into excel functions - I think this formula will need to be a little sophisticated. I believe currently that the formula will need to be some variation of a nested VLookup function or possibly a VLookup nested with an HLookup. NOTE: The "TABLE" tab lists 'Lot Size' as ranges. Currently I am skeptical about leaving this as a range because I think it will make the equation needed even more complicated (unless this task can be performed using the approximate return of V/HLookup which I am unsure of but thought it has potential). If necessary I am open to creating a table that has one cell per one lot size (i.e. C5=2, C6=3, C7=4, etc.) in order to make this work.12KViews0likes6CommentsRe: Issues with Conditional Formatting Cells dependent on VLOOKUP
Patrick2788 Thank you for the feedback Patrick. Using the 'Applies To' section will not work for my case because the rule itself would still be checking cells F10 and G10 (I am referencing the equations you wrote out as the original response on this post). By rolling the rule downward to apply to more cells, I would additionally need the mention of F10 and G10 to roll as well (i.e. one row down the rules would need to change to F11 and G11). I know this is very specific and having the rules themselves actually change is the part that may very well be obtainable in excel, wanted to put it out there just in case. Also, thank you again on the equation formatting. That really was a huge help for me.4.3KViews0likes1CommentRe: Issues with Conditional Formatting Cells dependent on VLOOKUP
Patrick2788 Thank you so much for the response. I have a follow up question if you don't mind: I would like to apply these set of rules to a large range of rows (roughly 100), is there a way to "roll" this rule down along multiple cells or would I have to input the rule into each individual cell? The only change in the rules from row to row would be the row number (in the case of your post and my example sheet, the number 10 would be the item in the equations you posted that would be changing per row). I say roll with quotations because I know excel has the feature that if you type a number into a cell, say 1 for example, and drag the little black box in the bottom right corner of the cell and select "Fill Series" it will chronologically list from the number 1, automatically into the corresponding cells. I have been using the brute force method of inputting into individual cells but obviously that is time consuming and tedious so I would like to avoid doing this if possible4.4KViews0likes3CommentsIssues with Conditional Formatting Cells dependent on VLOOKUP
Hey Everyone, I have been having enormous issues trying to resolve a conditional formatting problem within an Excel Sheet because the cells referenced in the rules I create are dependent on the VLOOKUP function. I am running Excel on Microsoft Office Professional Plus 2010. I am attaching a "dummy" file that I put together so anyone could play around with the file to get a better idea of what I am trying to do. . . Tab: IP - Applicable field is the "SELECT PART#" as this is used in my VLOOKUP function Tab: INSP - Row 10 is of interest here, specifically the columns of K and L. - E, F, G will be auto-populated based on the selected part number from the IP tab - K and L are where the conditional Formatting lies. See "KEY" tab for more on what the three rules are for the conditional formatting. Tab: DATA TABLE - Values from this table are the ones being pulled using the VLOOKUP function. Specifically, columns 7 and 8 are important because these columns are referenced directly in the conditional formatting. Tab: KEY - This tab highlights the generic form of the VLOOKUP function I am using. The explanation on the right describes what to change within the formula to adjust for a separate column on the "DATA TABLE" tab (highlighted in blue on said tab). - Additionally, the Goals of the Conditional Formatting are laid out with their generic forms and reasoning for what each rule should be doing QUESTION (Please refer to excel file for specifics): On the INSP tab: The rules for the conditional Formatting are not working how I would like them to. I have them set up to compare the result within the Min/Max column to the Lower/Upper Limit that is auto-populated based on the Part Number chosen on the IP tab. Basically, if the value inputted into Min/Max column is OUTSIDE of the Lower to Upper Limit range; I need the Min/Max cell to format red. Conversely, if the value is INSIDE this range; I need the cell to format to white (no-fill). Since the Lower/Upper Limit cells contain a formula (VLOOKUP), I believe this is breaking the rules I create and causing any value inputted in the Min/Max section (whether inside or outside of Limit Range) to constantly format the cell red. . . I have exhausted so many hours into messing around with the conditional formatting and research online through various websites and help forums, I do not know what else to do. Please Help.4.5KViews0likes5Comments
Recent Blog Articles
No content to show