Show Posts
201
General Software Discussion / Re: Test cell value for BLANK in Excel and take action (or not) based on it
« on: September 10, 2015, 09:30 AM »
Thanks Target >> (note: see end of this before proceeding)
But the values used for A and B really COULD be almost anything as long as:
1. It MUST not be blank and
2: Each set of Ax Bx must be unique in the group of 30 entries.
I hate to admit but i am still a little confused about how to use this in the formula presented to either stop the calculations of preceding columns or at the very least end up filling in the C and all other calculations with a BLANK if either A or B are BLANK. It seems that it would still continue just using the value returned of 'XXXX' if both were blank.
The statement i am looking for would be one to completely stop the calculation if either A or B are blank. Would not returning 'XXXX' in that case simply plug right in to the next step as a value and create a result which would go into C and the sheet would move on to the next row and so on. Since the values are Auto-calculated, nothing stops them from proceeding to the end of the sheet. The statement I need is one that says if they are blank then put BLANK into C so the entire sheet stays blank until a valid entry is input into both A and B.
Or else a way to prevent the "auto-calculation" from proceeding at all
To keep things simple i did not go into the who thing because if i could find a test that would work, i col use it everywhere this occurs but there are 8 more columns using similar auto calculations to assign additional codes on the same row. If i don't stop it before it starts, i end up with a sheet full of numbers that all are created from the initial use of (BLANK) for either Ax or Bx ll the way from A1 B1 to A30 B30
If I had known how to prevent anything from happening if either A or B were blank by using a (test) on A and a (test) on B that would simply stop the precession that would make more sense but again, i don't know the method.
The results obtained could be valid for what is needed as long as they put something in A and B and as long as they do not use the exact same input for A and B. But that part I can legitimately say is out of my control, the user is supposed to ensure that this is never the case.
The intent is to create a set of semi-random 8 digit "codewords" that can be recreated if needed as long as the same inputs are used. And the only rules involved is that both A and B must have a "non-empty" and non-identical value. I cannot control the non-identical (or probably could if i wanted to really get into it) but it seems i should be able to control it such that i can hand them a BLANK spreadsheet to start with which , at this point, is impossible.
This was the best I could come up with on the spot to make the code with a specific formula which could easily be modified for subsequent groups and unless someone knew what formula was used they would not have a clue as what codes went with which entries I hope
.
That is why the codes are hidden and auto-calculated. Only the person tho sets up the initial "rules" for those calculations would know how to recreate it.
There are probably better ways to do this but it has now become a challenge (to me) to find a way to not end up with a sheet full of the same repetitions of codes all based on A and B being blank. If A and B were both XXXX it would still make a sheet full of hash codes.
I am currently telling them to ignore the lines where there is nothing in A or B but Murphy's Law guarantees that sooner or later someone will assign those values created from BLANK A and BLANK B to something. And truth be known it is such a glaring deficiency i cannot believe there isn't a simple way to prevent it from happening. The only entries made by the user of the sheet are to put values in A and B and ensure there are no duplicates in the set of 30 or less.
It is just annoying to me that i cannot prevent that error.
PS: PLEASE excuse my ignorance and if your reply would in truth give me BLANK cells for all calculations using that test, I apologize and it means I am simply not using it as you intended. As i said, Excel is not my forte'. 
But the values used for A and B really COULD be almost anything as long as:
1. It MUST not be blank and
2: Each set of Ax Bx must be unique in the group of 30 entries.
I hate to admit but i am still a little confused about how to use this in the formula presented to either stop the calculations of preceding columns or at the very least end up filling in the C and all other calculations with a BLANK if either A or B are BLANK. It seems that it would still continue just using the value returned of 'XXXX' if both were blank.
The statement i am looking for would be one to completely stop the calculation if either A or B are blank. Would not returning 'XXXX' in that case simply plug right in to the next step as a value and create a result which would go into C and the sheet would move on to the next row and so on. Since the values are Auto-calculated, nothing stops them from proceeding to the end of the sheet. The statement I need is one that says if they are blank then put BLANK into C so the entire sheet stays blank until a valid entry is input into both A and B.
Or else a way to prevent the "auto-calculation" from proceeding at all
To keep things simple i did not go into the who thing because if i could find a test that would work, i col use it everywhere this occurs but there are 8 more columns using similar auto calculations to assign additional codes on the same row. If i don't stop it before it starts, i end up with a sheet full of numbers that all are created from the initial use of (BLANK) for either Ax or Bx ll the way from A1 B1 to A30 B30
If I had known how to prevent anything from happening if either A or B were blank by using a (test) on A and a (test) on B that would simply stop the precession that would make more sense but again, i don't know the method.
The results obtained could be valid for what is needed as long as they put something in A and B and as long as they do not use the exact same input for A and B. But that part I can legitimately say is out of my control, the user is supposed to ensure that this is never the case.
The intent is to create a set of semi-random 8 digit "codewords" that can be recreated if needed as long as the same inputs are used. And the only rules involved is that both A and B must have a "non-empty" and non-identical value. I cannot control the non-identical (or probably could if i wanted to really get into it) but it seems i should be able to control it such that i can hand them a BLANK spreadsheet to start with which , at this point, is impossible.
This was the best I could come up with on the spot to make the code with a specific formula which could easily be modified for subsequent groups and unless someone knew what formula was used they would not have a clue as what codes went with which entries I hope
.That is why the codes are hidden and auto-calculated. Only the person tho sets up the initial "rules" for those calculations would know how to recreate it.
There are probably better ways to do this but it has now become a challenge (to me) to find a way to not end up with a sheet full of the same repetitions of codes all based on A and B being blank. If A and B were both XXXX it would still make a sheet full of hash codes.
I am currently telling them to ignore the lines where there is nothing in A or B but Murphy's Law guarantees that sooner or later someone will assign those values created from BLANK A and BLANK B to something. And truth be known it is such a glaring deficiency i cannot believe there isn't a simple way to prevent it from happening. The only entries made by the user of the sheet are to put values in A and B and ensure there are no duplicates in the set of 30 or less.
It is just annoying to me that i cannot prevent that error.
PS:
PLEASE excuse my ignorance and if your reply would in truth give me BLANK cells for all calculations using that test, I apologize and it means I am simply not using it as you intended. As i said, Excel is not my forte'. 
Just like the newest Tesla Roadster there is always a tiny piece of Henry Ford's Model 'A' hidden somewhere inside. Legacy coding will probably always be lurkng in the corners of every super-program for many years to come 
