Force required cell data

[chadlmiller]

So I have excel 2007, on windows xp.
I have three sheets. Sheet 1 contains raw data, sheet 1 disassembles data and sheet 3 reconfigures or rewrites data based on logic to populate for a .prn file to be upladed into a mainframe system.

I need to ensure that cell E2 has data populated based on the existence of any data in cell B2.

So either a Message pop up that states,
"YOU REQUIRE INPUT IN CELL E2" if E2 was blank but B2 had data.
Or some other mechanism to WARN the user to input data.

 

[MattGreer]

You could use conditional formatting on cell E2, and then use a formula to detect data in cell B2.

If B2 doesn't contain data, then do something like this in cell E2:

=IF(B2="","ENTER DATA",(action if data exists in B2))

Then use the conditional formatting features of Excel to put shading, or bold, or some such indication if the message "ENTER DATA" shows up in E2.

Otherwise, you're going to have to use VBA to get a pop-up box, I think. And even then, the user will have to click a button. I don't know if there is an "ON SAVE" event trigger in Excel to warn the user to fill out the spreadsheet completely.

Thanks!!


Matt

 

[chadlmiller]

The only problem with your solution is that the subsequent sheets read the data and decompose it into different cells. so your solution would then provide on sheet 2 E2 "ENT" and sheet 2 F2 "ATA" and if the analyst fills in down to line 23 and that ends the lines of Data, then I do not want subsequent lines of data in rows 24+ to populate with false data like "ENT" or "ATA"

I cannot afford to have that kind of validation because the sheet would recognize data as existing. and "ATA" in Sheet 2 F2 is valid data. Go figure..


I would require the field to remain blank

 

[SkipVought]

Huh?

You, apparently know what you're talking about.

I, have no earthly idea.

Your original post referred to ONE ROW...

I need to ensure that cell E2 has data populated based on the existence of any data in cell B2.

Now you are referring to...

...the analyst fills in down to line 23 and ...

In fact, the original question seems to have nothing at all to do with any oher sheet in the workbook, although you threw that extra information in for some reason.

Help us out here!

Please be CLEAR, CONCISE and COMPLETE and explain exactly what the problem is and what you need.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[chadlmiller]

Alright, I need to ensure that the analyst enters data into a specific cell and when they exit or save the workbook a prompt or other bold signal occurs either in the cell or a pop up that the cell requires data entry, based on the fact that related row cell has data.

If the primary row cell is blank then the specific row cell will remain blank as well.


So the psuedo code should look like this:
If cell B2 = null value then cell D2 = null value
else
If cell B2 is not null then cell D2 = value
on save
If cell D2 = null and B2 is not null PROMPT USER D2 requires proper data

Hope this helps explain.

 

[SkipVought]

"If cell D2 = null and B2 is not null PROMPT USER D2 requires proper data"
[tt]
=IF(AND(ISBLANK(D2),NOT(ISBLANK(B2))),"D2 requires proper data","")
[tt]
BTW, there is no way to "force" the user to enter data without VBA code, and even then, no one is holding a gun to their head.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oh, yes, and you can also use this part of the formula as a Conditional Format...
[tt]
=AND(ISBLANK(D2),NOT(ISBLANK(B2)))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[chadlmiller]

What,,,,, Are you syaing Microsoft still has no feature with a gun placed directly into the face of a user???

Oh man, I thought that was supposed to be released with DOT NET Framework!!

 

[Gavona]

Are you syaing Microsoft still has no feature with a gun placed directly into the face of a user???

There is no built in feature that does that BUT, you are provided with the tools that enable you to deliver that capability...or at least a picture of a gun appearing dependent on a particular condition.
See Glenn's post in thread68-1542827

Gavin

 

[SkipVought]

A pair might get my attention.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]