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

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

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

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

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