| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prompt value in formula
I am using this formula in my company, but the procedure has now changed and
therefor the values ("204";"0"; etc.) has to be changed from time to time. Can someone show me a script so that the formula prompts for these values when i click on the cell??? I hope my question is understable and clear! :o) =SUMPRODUCT(('import part'!$A$2:INDEX('import part'!$A$1:$A$50000;TÆL('import part'!$E$1:$E$50000))="204")*('import part'!$B$2:INDEX('import part'!$B$1:$B $50000;TÆL('import part'!$E$1:$E$50000))="0")*('import part'!$G$2:INDEX ('import part'!$G$1:$G$50000;TÆL('import part'!$E$1:$E$50000))=8)*('import part'!$F$2:INDEX('import part'!$F$1:$F$50000;TÆL('import part'!$E$1:$E$50000)) <=2)*('import part'!$E$2:INDEX('import part'!$E$1:$E$50000;TÆL('import part'! $E$1:$E$50000))<27)*'import part'!$D$2:INDEX('import part'!$D$1:$D$50000;TÆL ('import part'!$E$1:$E$50000))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200902/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Prompt value in formula
Why don't you replace 204 and 0 with cell references like A1 and A2.
"micro1000 via OfficeKB.com" wrote: I am using this formula in my company, but the procedure has now changed and therefor the values ("204";"0"; etc.) has to be changed from time to time. Can someone show me a script so that the formula prompts for these values when i click on the cell??? I hope my question is understable and clear! :o) =SUMPRODUCT(('import part'!$A$2:INDEX('import part'!$A$1:$A$50000;TÆL('import part'!$E$1:$E$50000))="204")*('import part'!$B$2:INDEX('import part'!$B$1:$B $50000;TÆL('import part'!$E$1:$E$50000))="0")*('import part'!$G$2:INDEX ('import part'!$G$1:$G$50000;TÆL('import part'!$E$1:$E$50000))=8)*('import part'!$F$2:INDEX('import part'!$F$1:$F$50000;TÆL('import part'!$E$1:$E$50000)) <=2)*('import part'!$E$2:INDEX('import part'!$E$1:$E$50000;TÆL('import part'! $E$1:$E$50000))<27)*'import part'!$D$2:INDEX('import part'!$D$1:$D$50000;TÆL ('import part'!$E$1:$E$50000))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200902/1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Prompt value in formula
A good reason not to put this data in the formula.
Use this opportunity to put your values in a cell, and change your formula accordingly. For example, format a1 as text, enter 204, then change your formula to: .....$E$1:$E$50000))=A1... You would also be better off if you were consistent in using text or numbers. You have "204", "0", but 8 and 27. Your formula will be easier to maintain if you used either numbers or text, but not both. Regards, Fred. "micro1000 via OfficeKB.com" <u48676@uwe wrote in message news:9252e3e853b36@uwe... I am using this formula in my company, but the procedure has now changed and therefor the values ("204";"0"; etc.) has to be changed from time to time. Can someone show me a script so that the formula prompts for these values when i click on the cell??? I hope my question is understable and clear! :o) =SUMPRODUCT(('import part'!$A$2:INDEX('import part'!$A$1:$A$50000;TÆL('import part'!$E$1:$E$50000))="204")*('import part'!$B$2:INDEX('import part'!$B$1:$B $50000;TÆL('import part'!$E$1:$E$50000))="0")*('import part'!$G$2:INDEX ('import part'!$G$1:$G$50000;TÆL('import part'!$E$1:$E$50000))=8)*('import part'!$F$2:INDEX('import part'!$F$1:$F$50000;TÆL('import part'!$E$1:$E$50000)) <=2)*('import part'!$E$2:INDEX('import part'!$E$1:$E$50000;TÆL('import part'! $E$1:$E$50000))<27)*'import part'!$D$2:INDEX('import part'!$D$1:$D$50000;TÆL ('import part'!$E$1:$E$50000))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200902/1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Prompt value in formula
Sorry for my late reply, but I have been away from my desk for a while. I
succeded using you suggestion. Thank you very much. Fred Smith wrote: A good reason not to put this data in the formula. Use this opportunity to put your values in a cell, and change your formula accordingly. For example, format a1 as text, enter 204, then change your formula to: ....$E$1:$E$50000))=A1... You would also be better off if you were consistent in using text or numbers. You have "204", "0", but 8 and 27. Your formula will be easier to maintain if you used either numbers or text, but not both. Regards, Fred. I am using this formula in my company, but the procedure has now changed and [quoted text clipped - 17 lines] part'!$D$1:$D$50000;TÆL ('import part'!$E$1:$E$50000))) -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Prompt value in formula
Glad I could help. Thanks for the feedback.
Regards, Fred. "micro1000 via OfficeKB.com" <u48676@uwe wrote in message news:929e5e5b67134@uwe... Sorry for my late reply, but I have been away from my desk for a while. I succeded using you suggestion. Thank you very much. Fred Smith wrote: A good reason not to put this data in the formula. Use this opportunity to put your values in a cell, and change your formula accordingly. For example, format a1 as text, enter 204, then change your formula to: ....$E$1:$E$50000))=A1... You would also be better off if you were consistent in using text or numbers. You have "204", "0", but 8 and 27. Your formula will be easier to maintain if you used either numbers or text, but not both. Regards, Fred. I am using this formula in my company, but the procedure has now changed and [quoted text clipped - 17 lines] part'!$D$1:$D$50000;TÆL ('import part'!$E$1:$E$50000))) -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Read Only Prompt | Excel Discussion (Misc queries) | |||
| save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
| How do I enable Excel's "formula prompt?" | Excel Discussion (Misc queries) | |||
| Save Prompt | Excel Worksheet Functions | |||
| Help on Prompt | Excel Worksheet Functions | |||
Linear Mode
