10 replies to this topic

[ChristinaK]

Hello all, I am in need of some assistance with Excel.

I made the decision to have my QA team (myself and 1 other) switch from handwritten logs to electronic logs due to many reasons, the main 2 being illegible handwriting and convenience. My plan is to have the QA Supervisor perform her tests as usual, but then record results into the spreadsheet and email me (QA Director) the completed sheet daily for review and approval. 

 

Anyway...I need help with excel conditional formulas. Right now I'm working on our brine log and I'm stumped on how to achieve what I want, if it's even possible without installing a bunch of widgets. I attached a basic copy below.

 

So date, time, batch # are entered. The tank is chosen from a drop-down list (you can see drop-down lists in the second tab). Then, the Brine recipe (Column E) is chosen from a drop-down menu. Here's what I'd like to be able to do...

When a certain brine recipe is chosen (ex: Brine 1), I want the pH, salt, and brix in that row to have conditional formatting based on what's set for that brine recipe. 

 

Is there a way I can do something like... IF E5="Brine 1", then F5 must be between VALUE1-VALUE2, G5 must be between VALUE3-VALUE4, and H5 must be between VALUE5-VALUE6. And if the data entered are outside of those ranges, the cell turns red. And if E5="Brine 2" then F5, G5, and H5 must be between the values for that recipe or else the cell will turn red.

 

If it's not possible without becoming very complicated, that's ok! It's not necessary, I just thought it'd be nice to have a visual trigger like a cell turning red.

[Scampi]

yes but i think you're going to have to run a macro

[ChristinaK]

yes but i think you're going to have to run a macro

 Ah, darn. I want to avoid using macros to keep the spreadsheet easier to revise for someone without advanced skills.

 

I'll just add instructions to verify measurements against specs then.

Thanks for the reply Scampi! 

[cjfriedman]

so if I understand correctly, you're going to continue to make handwritten checks and then transfer it over to excel. is that correct? why add work when you can make digital forms/checks? digital forms will save you all the extra work and you get a tons of reporting and sorting options

[ChristinaK]

so if I understand correctly, you're going to continue to make handwritten checks and then transfer it over to excel. is that correct? why add work when you can make digital forms/checks? digital forms will save you all the extra work and you get a tons of reporting and sorting options

 

No, we're currently using handwritten logs but are moving toward using digital logs (via Excel). Our current logs were all originally created in Word, so I've been turning them into Excel forms, but wanted to give them a little flair. 

I was looking for advice on how to use conditional formatting to automatically highlight cells based on the data entered in relation to data entered into a cell in the same row. I'm hoping it would be possible without using macros, but it doesn't seem so.

[DFdk]

Its possible in Google sheets - possibly also excel, but i dont know how.

 

Sheets is pretty straightforward. Right click -> format cell. Many different format types, including "if equals to/larger than" etc. is met, the cell turns into a color of your choice.

[ChristinaK]

Its possible in Google sheets - possibly also excel, but i dont know how.

 

Sheets is pretty straightforward. Right click -> format cell. Many different format types, including "if equals to/larger than" etc. is met, the cell turns into a color of your choice.

 

I know basic conditional formatting based on a single cell, but I'm looking for advice on how to set up conditional formatting as I mentioned in the 1st post:

 

 

So date, time, batch # are entered. The tank is chosen from a drop-down list (you can see drop-down lists in the second tab). Then, the Brine recipe (Column E) is chosen from a drop-down menu. Here's what I'd like to be able to do...

When a certain brine recipe is chosen (ex: Brine 1), I want the pH, salt, and brix in that row to have conditional formatting based on what's set for that brine recipe. 

 

Is there a way I can do something like... IF E5="Brine 1", then F5 must be between VALUE1-VALUE2, G5 must be between VALUE3-VALUE4, and H5 must be between VALUE5-VALUE6. And if the data entered are outside of those ranges, the cell turns red. And if E5="Brine 2" then F5, G5, and H5 must be between the values for that recipe or else the cell will turn red.

 

I attached an example of what I'm working on, hopefully it posts this time.

Attached Files

•  brine log example - Copy.xlsx   28.69KB   19 downloads

[DFdk]

Its possible to hide coloumns or remove "pH" from the headline, and only show them when criterias are met, e.g. chosing "brine 2".

 

Does it need to be entered into a excel sheet? - i would recommend looking at paperform.io, a basic form builder, which you can embed on your own website and integrate with google sheets. The forms is much more user friendly and easy for "regular" people to understand. Making it easy to teach new people how to use it.

 

See quick example here: https://zap6jbax.paperform.co/

When choosing "brine 2" pH is needed, when choosing "brine 1" salt% is needed.

[paulwill10124]

I know how to do this but explaining it is beyond my capacity atm (we're having our BRC audit and my brain is fried.  I'll work out how to word the instructions after our BRC and get back to you (remind me if I don't please!) .

[G M]

...

If it's not possible without becoming very complicated, that's ok! It's not necessary, I just thought it'd be nice to have a visual trigger like a cell turning red.

 

It's possible, but would require a few nested functions, probably multiple hidden cells doing checks and cross referencing to get to the multivariable pass/fail, so it's getting into territory that probably doesn't meet the level of effort you were looking for.

 

I've done something similar for an allergen wash guide the production supervisors just plug two material numbers into to figure out which wash procedure is needed when doing a change over.  On the outside it's just a few colored boxes that say to wash or not, but the formulas are 4500 characters long.

[scotty4874]

Hi

 

See attached.

 

Think it basically covers off what you are looking for. Have included note file in document explaining function.

 

Basically hidden column / formula next to each criteria that refers inputted value to a set criteria (is value within set min/ max) then conditional formats according to result of formula (ie ERR - format/ OK dont format).

 

for information Formula a mix of If & vlookup.

Attached Files

•  brine log example - Copy formatted.xlsx   986.2KB   5 downloads