is it possible to enter in text over a formula without deleting the formula?

[Danie.Bright]

Here's the situation.

I have a drop down menua of customers in B11, when a customer is
selected, thier company information is automatically populated in J11,
J12 & J13 (via a VLOOKUP formula).

There are 2 parts to this question.

1.) in the drop down menu of B11 we have a blank option (so when
selected B11 appears blank), but the cells associated with B11, [which
are J11 (formula is: "J11=B11"), J12 (formula is:
"IF(J11="","",VLOOKUP($J11,'Databases'!$A$4:$C$55,2,0)) ") and J13
f(ormula is: "IF(J11="","",VLOOKUP($J11,'Databases'!$A$4:$C$55,3,0))
")] appear as J11 "0", J12 "#N/A" and J13 "#N/A". Is there anyway
that J11-13 can just appear blank as well? rather than have the
formula results in them? without deleting the formula itself?


3.) on the occasion that we have a random customer that is not in our
database and therefore not in the dropdown menu in cell B11, how can i
type in this customers information in B11 without a.) getting an error
because it is not in the drop down menu's required options, or b.)
deleting the drop down menu altogether???


Basically i want the end result to be:
In B11
-> if i choose a customer from our database in the drop down menu,
that customers information will continue to populate in J11-13 (as
specified above - which i am able to do now)
->if i choose the blank option "nil" in the drop down menu, therefore
leaving B11 (unfilled), then J11-J13 will also remain (unfilled)
WITHOUT the "0" & "#N/A" resulting from the formulas stored in those
cells.
-> and i want to be able to manually type the customers name into B11
& information in J11-J13, without deleting the aforementioned settings/
formulas in these 4 given cells.



Any help is awesome!!!

 

[JW]

For the first part, just place something like the below formula in the
J11:
=IF(ISBLANK(B11),"",B11)

As for the second part, IMO, the best way to handle this would be to
add an option to the validation list that says "Add New Record". You
could then display a userform to have the user enter the information
and have that information appended to the list source. That would be
done via VBA.

 

[Danie.Bright]

i would be interested in trying that last part - however i know
NOTHING about VBA, in fact i hadnt even heard of it till i began
messing with this spreadsheet for my new job. if you know how to do
this in VBA, i am interested. how would the "new record" be entered
into the database, because the "database" is just a simple list on a
"hidden" worksheet in the same workbook that i have feeding to the
drop down menu's. --- or if you know where i can find this kind of
information----


any help is great - thanks!

For the first part, just place something like the below formula in the
J11:
=IF(ISBLANK(B11),"",B11)

As for the second part, IMO, the best way to handle this would be to
add an option to the validation list that says "Add New Record". You
could then display a userform to have the user enter the information
and have that information appended to the list source. That would be
done via VBA.

Here's the situation.

I have a drop down menua of customers in B11, when a customer is
selected, thier company information is automatically populated in J11,
J12 & J13 (via a VLOOKUP formula).

There are 2 parts to this question.

1.) in the drop down menu of B11 we have a blank option (so when
selected B11 appears blank), but the cells associated with B11, [which
are J11 (formula is: "J11=B11"), J12 (formula is:
"IF(J11="","",VLOOKUP($J11,'Databases'!$A$4:$C$55,2,0)) ") and J13
f(ormula is: "IF(J11="","",VLOOKUP($J11,'Databases'!$A$4:$C$55,3,0))
")] appear as J11 "0", J12 "#N/A" and J13 "#N/A". Is there anyway
that J11-13 can just appear blank as well? rather than have the
formula results in them? without deleting the formula itself?

3.) on the occasion that we have a random customer that is not in our
database and therefore not in the dropdown menu in cell B11, how can i
type in this customers information in B11 without a.) getting an error
because it is not in the drop down menu's required options, or b.)
deleting the drop down menu altogether???

Basically i want the end result to be:
In B11
-> if i choose a customer from our database in the drop down menu,
that customers information will continue to populate in J11-13 (as
specified above - which i am able to do now)
->if i choose the blank option "nil" in the drop down menu, therefore
leaving B11 (unfilled), then J11-J13 will also remain (unfilled)
WITHOUT the "0" & "#N/A" resulting from the formulas stored in those
cells.
-> and i want to be able to manually type the customers name into B11
& information in J11-J13, without deleting the aforementioned settings/
formulas in these 4 given cells.

Any help is awesome!!!- Hide quoted text -

- Show quoted text -

 

[Gord Dibben]

If you turn off the error alert on the DV dropdown you can enter any data you
want in the DV cell.

Data>Validation>Error Alert tab.

It won't be appended to the list however.

To do that would require VBA.

See Debra Dalgleish's site for a sample workbook with code.

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0012 - Update Validation List -- type a new value in a cell that contains data
validation, and it's automatically added to the source list, and the list is
sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb


Gord Dibben MS Excel MVP

i would be interested in trying that last part - however i know
NOTHING about VBA, in fact i hadnt even heard of it till i began
messing with this spreadsheet for my new job. if you know how to do
this in VBA, i am interested. how would the "new record" be entered
into the database, because the "database" is just a simple list on a
"hidden" worksheet in the same workbook that i have feeding to the
drop down menu's. --- or if you know where i can find this kind of
information----


any help is great - thanks!

For the first part, just place something like the below formula in the
J11:
=IF(ISBLANK(B11),"",B11)

As for the second part, IMO, the best way to handle this would be to
add an option to the validation list that says "Add New Record". You
could then display a userform to have the user enter the information
and have that information appended to the list source. That would be
done via VBA.

Here's the situation.

I have a drop down menua of customers in B11, when a customer is
selected, thier company information is automatically populated in J11,
J12 & J13 (via a VLOOKUP formula).

There are 2 parts to this question.

1.) in the drop down menu of B11 we have a blank option (so when
selected B11 appears blank), but the cells associated with B11, [which
are J11 (formula is: "J11=B11"), J12 (formula is:
"IF(J11="","",VLOOKUP($J11,'Databases'!$A$4:$C$55,2,0)) ") and J13
f(ormula is: "IF(J11="","",VLOOKUP($J11,'Databases'!$A$4:$C$55,3,0))
")] appear as J11 "0", J12 "#N/A" and J13 "#N/A". Is there anyway
that J11-13 can just appear blank as well? rather than have the
formula results in them? without deleting the formula itself?

3.) on the occasion that we have a random customer that is not in our
database and therefore not in the dropdown menu in cell B11, how can i
type in this customers information in B11 without a.) getting an error
because it is not in the drop down menu's required options, or b.)
deleting the drop down menu altogether???

Basically i want the end result to be:
In B11
-> if i choose a customer from our database in the drop down menu,
that customers information will continue to populate in J11-13 (as
specified above - which i am able to do now)
->if i choose the blank option "nil" in the drop down menu, therefore
leaving B11 (unfilled), then J11-J13 will also remain (unfilled)
WITHOUT the "0" & "#N/A" resulting from the formulas stored in those
cells.
-> and i want to be able to manually type the customers name into B11
& information in J11-J13, without deleting the aforementioned settings/
formulas in these 4 given cells.

Any help is awesome!!!- Hide quoted text -

- Show quoted text -