Range reference and return

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Nov 29, 2004

• #2

How does the user "choose" a product? (list box, typed in, etc.)

 

Upvote 0

P

PritiOne

Active Member

Joined

Jun 2, 2004

Messages

357

• Nov 29, 2004

• #3

The user types in a product number - there is a list of over 700 products, but if product from Range 1, Range 2, and Range 3, then the discount should apply... Ranges 1-3 doen not include all products.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Nov 29, 2004

• #4

Hi,

This is one way:

Formula needs to be entered with Ctrl + shift + enter.

Book1
	A	B	C	D	E
1	Shop list		List1	List2	List3
2	a		a	e	k
3	e		b	f	l
4	k		c	g	m
5			d	h
6				i
7				j
8
9		Discount
10		1%
Sheet2

 

Upvote 0

P

PritiOne

Active Member

Joined

Jun 2, 2004

Messages

357

• Nov 29, 2004

• #5

Thanks for the help.. just wondering what you mean by:

Formula needs to be entered with Ctrl + Shift + Enter

also.. I am trying to paste the formula, not exacly reading as a formula (reading as text) Something to do with the above questions?

Thanks again!

 

Upvote 0

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Nov 29, 2004

• #6

If you paste fairwind's formula into the formula bar, you can't just press ENTER to get the value of the formula. You have to hold down CTRL and SHIFT while you press ENTER, as his formula is an array formula.

The "not exactly reading as a formula (reading as text)" could have to do with how your cell is formatted. Make sure your destination cell is formatted as percentage before you put the formula there.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Nov 29, 2004

• #7

Yes!

And when you past it it should be without the curly brackets:

=IF(MAX(--ISNUMBER(MATCH($A$2:$A$10,C2:C5,0)))*MAX(--ISNUMBER(MATCH($A$2:$A$10,D2:D7,0)))*MAX(--ISNUMBER(MATCH($A$2:$A$10,E2:E4,0))),1%,0)

They appear when you press ctrl + shift + enter

If you past with the curly brackets formula will be threated as text.

 

Upvote 0

P

PritiOne

Active Member

Joined

Jun 2, 2004

Messages

357

• Nov 29, 2004

• #8

Ok - I am holding down the Ctrl + Shift + Enter and not getting a result...

I've altered the formula to read the defined ranges:

{=IF(MAX(--ISNUMBER(MATCH(NewProdList,MedMains,0)))*MAX(--ISNUMBER(MATCH(NewProdList,Connect,0)))*MAX(--ISNUMBER(MATCH(NewProdList,Assessment,0))),1%,0)}

Please help!

 

Upvote 0

P

PritiOne

Active Member

Joined

Jun 2, 2004

Messages

357

• Nov 29, 2004

• #9

Thank you.. I got the formula in.. Now I will test it out.

Thanks to both for your assistance

 

Upvote 0

P

PritiOne

Active Member

Joined

Jun 2, 2004

Messages

357

• Nov 29, 2004

• #10

ok - The formula isn't exactly working.

I have a list of products.. some that are included in the defined ranges and some that are not. If my user selects a product from range 1, range 2, and range 3.. (but also can select from products that are not in the ranges) the formula should result in 1%, otherwise result in 0%.

Have I done something wrong?

=IF(MAX(--ISNUMBER(MATCH(NewProdList,MedMains,0)))*MAX(--ISNUMBER(MATCH(NewProdList,Connect,0)))*MAX(--ISNUMBER(MATCH(NewProdList,Assessment,0))),1%,0%)

The defined range of "NewProdList" is where the user inputs his products.

 

Upvote 0