Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
is it possible to enter in text over a formula without deleting the formula?
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!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
is it possible to enter in text over a formula without deleting the formula?
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. wrote: 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!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
is it possible to enter in text over a formula without deleting the formula?
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! On Nov 12, 3:20 pm, JW wrote: 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. wrote: 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter text and formula in a cell | Excel Worksheet Functions | |||
Deleting the "'" character in the text formula | Excel Discussion (Misc queries) | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
HOW DO I ENTER THIS FORMULA TO CONVERT TEXT TO DATE =DATEVALUE(, | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel |