Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 25, 2020

AnthonyCappuccio 

I'd do that with helper ranges/cells. For the main table

add percents as numbers, not as texts on the top, and on the left low boundary of ranges.

Here

in B1 how we interpret ALL, and in C1 formula

=IFERROR(
   --INDEX(TABLE!$D$5:$S$19,
      LOOKUP('TAB 1'!$E$3,TABLE!$A$5:$A$19,(ROW(TABLE!$A$5:$A$19)-ROW(TABLE!$C$4))),
      MATCH('TAB 1'!$E$5,TABLE!$D$3:$S$3,1)),
'TAB 2'!$B$1)

and conditional formatting rule formula as

=(ROW()>ROW($C$2))*(ROW()<=($C$1+ROW($C$2)))

It could be combined al together in one formula, but it will be hard to maintain such.

AnthonyCappuccio's avatar
AnthonyCappuccio
Copper Contributor
Apr 25, 2020

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:

 

  1. 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?
  2. 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.
  3. 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!

Resources