I recently posted up a question as to which type of formula I should use to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of USB products.
Basically, when I have selected what size (capacity) that I want to quote, I
then type in the quantity. From here, the formula then needs to look up what
capacity I am offering in the capacity cell and then the quantity in the
quantity cell before looking to a cell range which shows quantity breaks (ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is then
calculated.

So, for example, if I select a 128MB device, and the customer wants 200pcs,
it should look up within the cell range and see that 200 comes between the
column of 100pcs and 250pcs and offer what is in the 100pc column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd numerous ways to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I should use to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of USB
products.
Basically, when I have selected what size (capacity) that I want to quote,
I
then type in the quantity. From here, the formula then needs to look up
what
capacity I am offering in the capacity cell and then the quantity in the
quantity cell before looking to a cell range which shows quantity breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is then
calculated.

So, for example, if I select a 128MB device, and the customer wants
200pcs,
it should look up within the cell range and see that 200 comes between the
column of 100pcs and 250pcs and offer what is in the 100pc column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd numerous ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

OK. Basically, once I've selected the capacity and entered in the required
quantity, the cell should look to this cell range - here I have copied the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the quantity
entered (which maybe exactly those in the row headings, or in between), it
should display what is in the the corresponding cell underneath (so for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells addresses and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying it ??

I have put that formula in, selected a capacity and then typed in a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I should use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of USB
products.
Basically, when I have selected what size (capacity) that I want to
quote,
I
then type in the quantity. From here, the formula then needs to look
up
what
capacity I am offering in the capacity cell and then the quantity in
the
quantity cell before looking to a cell range which shows quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is then
calculated.

So, for example, if I select a 128MB device, and the customer wants
200pcs,
it should look up within the cell range and see that 200 comes between
the
column of 100pcs and 250pcs and offer what is in the 100pc column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have copied the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the quantity
entered (which maybe exactly those in the row headings, or in between), it
should display what is in the the corresponding cell underneath (so for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells addresses and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying it ??

I have put that formula in, selected a capacity and then typed in a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of USB
products.
Basically, when I have selected what size (capacity) that I want to
quote,
I
then type in the quantity. From here, the formula then needs to
look
up
what
capacity I am offering in the capacity cell and then the quantity in
the
quantity cell before looking to a cell range which shows quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is
then
calculated.

So, for example, if I select a 128MB device, and the customer wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

Hi Bob,

Thanks for that - I tried replicating this but just need to know which cells
reflect what in the formula you did so I know which ones to adjust in the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have copied the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the quantity
entered (which maybe exactly those in the row headings, or in between), it
should display what is in the the corresponding cell underneath (so for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells addresses and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying it ??

I have put that formula in, selected a capacity and then typed in a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of USB
products.
Basically, when I have selected what size (capacity) that I want to
quote,
I
then type in the quantity. From here, the formula then needs to
look
up
what
capacity I am offering in the capacity cell and then the quantity in
the
quantity cell before looking to a cell range which shows quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is
then
calculated.

So, for example, if I select a 128MB device, and the customer wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the quantity
entered (which maybe exactly those in the row headings, or in between),
it
should display what is in the the corresponding cell underneath (so for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying it
??

I have put that formula in, selected a capacity and then typed in a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I want
to
quote,
I
then type in the quantity. From here, the formula then needs to
look
up
what
capacity I am offering in the capacity cell and then the quantity
in
the
quantity cell before looking to a cell range which shows quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the quantity
entered (which maybe exactly those in the row headings, or in between),
it
should display what is in the the corresponding cell underneath (so for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying it
??

I have put that formula in, selected a capacity and then typed in a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I want
to
quote,
I
then type in the quantity. From here, the formula then needs to
look
up
what
capacity I am offering in the capacity cell and then the quantity
in
the
quantity cell before looking to a cell range which shows quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben

As I said, it works fine for me, so I'm out of ideas.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800
say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined
with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know
which
cells
reflect what in the formula you did so I know which ones to adjust
in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in
the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath
(so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore
clarifying
it
??

I have put that formula in, selected a capacity and then typed
in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be
gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula
I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale
of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then
needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the
price
is
then
calculated.

So, for example, if I select a 128MB device, and the
customer
wants
200pcs,
it should look up within the cell range and see that 200
comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve
this
??

Many Thanks,
Ben

Maybe it's time for you to write down:

1. The address of the table.
2. The address of the column that contains the headers for the rows.
3. The address of the cell that has to match one of the row headers.
4. The address of the row that contains the headers for the columns.
5. The address of the cell that has to match one of the column headers.


Ben wrote:

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben

















--

Dave Peterson

Hi Dave / Bob,

I have since tried this instead -

I created a drop-down box with a cell link which in this case is cell V19.
Depending on what I select it comes up with anything from 1 to 9 so I then
created this formula
=IF(V19=1,INDEX(AM20:AS20,B19) and I have then copied this for each time V19
equals 1 to 9. However, once it gets to V19=7, it comes up with problems !

B19 is where the quantity is typed in.

I then do a test run and select it so V19 does equal 1 and type in quantity
of 100 and it changes from "FALSE" to #REF.

What do you think I maybe doing wrong here ??

Cheers,
Ben

"Dave Peterson" wrote:

Maybe it's time for you to write down:

1. The address of the table.
2. The address of the column that contains the headers for the rows.
3. The address of the cell that has to match one of the row headers.
4. The address of the row that contains the headers for the columns.
5. The address of the cell that has to match one of the column headers.


Ben wrote:

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben

















--

Dave Peterson

That doesn't look like the formula that Bob posted.

Is there a reason you checked V19, but used B19 in the =index() portion.

And if you're copying the formula, you may want to use $am$20:$as$20 so that
this range won't change.

Ben wrote:

Hi Dave / Bob,

I have since tried this instead -

I created a drop-down box with a cell link which in this case is cell V19.
Depending on what I select it comes up with anything from 1 to 9 so I then
created this formula
=IF(V19=1,INDEX(AM20:AS20,B19) and I have then copied this for each time V19
equals 1 to 9. However, once it gets to V19=7, it comes up with problems !

B19 is where the quantity is typed in.

I then do a test run and select it so V19 does equal 1 and type in quantity
of 100 and it changes from "FALSE" to #REF.

What do you think I maybe doing wrong here ??

Cheers,
Ben

"Dave Peterson" wrote:

Maybe it's time for you to write down:

1. The address of the table.
2. The address of the column that contains the headers for the rows.
3. The address of the cell that has to match one of the row headers.
4. The address of the row that contains the headers for the columns.
5. The address of the cell that has to match one of the column headers.


Ben wrote:

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben

















--

Dave Peterson


--

Dave Peterson

V19 displays numbers 1 to 9 depending on what is selected inthe drop down box
B19 is where the quantity is entered into.

"Dave Peterson" wrote:

That doesn't look like the formula that Bob posted.

Is there a reason you checked V19, but used B19 in the =index() portion.

And if you're copying the formula, you may want to use $am$20:$as$20 so that
this range won't change.

Ben wrote:

Hi Dave / Bob,

I have since tried this instead -

I created a drop-down box with a cell link which in this case is cell V19.
Depending on what I select it comes up with anything from 1 to 9 so I then
created this formula
=IF(V19=1,INDEX(AM20:AS20,B19) and I have then copied this for each time V19
equals 1 to 9. However, once it gets to V19=7, it comes up with problems !

B19 is where the quantity is typed in.

I then do a test run and select it so V19 does equal 1 and type in quantity
of 100 and it changes from "FALSE" to #REF.

What do you think I maybe doing wrong here ??

Cheers,
Ben

"Dave Peterson" wrote:

Maybe it's time for you to write down:

1. The address of the table.
2. The address of the column that contains the headers for the rows.
3. The address of the cell that has to match one of the row headers.
4. The address of the row that contains the headers for the columns.
5. The address of the cell that has to match one of the column headers.


Ben wrote:

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB ÀšÃ‚£0.01 ÀšÃ‚£0.02 ÀšÃ‚£0.03 ÀšÃ‚£0.04 ÀšÃ‚£0.05 ÀšÃ‚£0.06 ÀšÃ‚£0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben

It still doesn't look like the formula that Bob suggested.

Ben wrote:

V19 displays numbers 1 to 9 depending on what is selected inthe drop down box
B19 is where the quantity is entered into.

"Dave Peterson" wrote:

That doesn't look like the formula that Bob posted.

Is there a reason you checked V19, but used B19 in the =index() portion.

And if you're copying the formula, you may want to use $am$20:$as$20 so that
this range won't change.

Ben wrote:

Hi Dave / Bob,

I have since tried this instead -

I created a drop-down box with a cell link which in this case is cell V19.
Depending on what I select it comes up with anything from 1 to 9 so I then
created this formula
=IF(V19=1,INDEX(AM20:AS20,B19) and I have then copied this for each time V19
equals 1 to 9. However, once it gets to V19=7, it comes up with problems !

B19 is where the quantity is typed in.

I then do a test run and select it so V19 does equal 1 and type in quantity
of 100 and it changes from "FALSE" to #REF.

What do you think I maybe doing wrong here ??

Cheers,
Ben

"Dave Peterson" wrote:

Maybe it's time for you to write down:

1. The address of the table.
2. The address of the column that contains the headers for the rows.
3. The address of the cell that has to match one of the row headers.
4. The address of the row that contains the headers for the columns.
5. The address of the cell that has to match one of the column headers.


Ben wrote:

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

"Bob Phillips" wrote:

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ben" wrote in message
...
Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with
the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

"Bob Phillips" wrote:

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19, AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that - I tried replicating this but just need to know which
cells
reflect what in the formula you did so I know which ones to adjust in
the
actual workbook itself.

Thanks,
Ben

"Bob Phillips" wrote:

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1) )

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ben" wrote in message
...
OK. Basically, once I've selected the capacity and entered in the
required
quantity, the cell should look to this cell range - here I have
copied
the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB ÀšÃ‚£0.01 ÀšÃ‚£0.02 ÀšÃ‚£0.03 ÀšÃ‚£0.04 ÀšÃ‚£0.05 ÀšÃ‚£0.06 ÀšÃ‚£0.07

So, basically, based on the capacity selected (32mb) and the
quantity
entered (which maybe exactly those in the row headings, or in
between),
it
should display what is in the the corresponding cell underneath (so
for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben



"Bob Phillips" wrote:

I mean to present a sample of the data in a message, cells
addresses
and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Ben" wrote in message
...
Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying
it
??

I have put that formula in, selected a capacity and then typed in
a
quantity
but it still comes up with #N/A

Thanks,
Ben

"Bob Phillips" wrote:

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19, AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Ben" wrote in message
...
I recently posted up a question as to which type of formula I
should
use
to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of
USB
products.
Basically, when I have selected what size (capacity) that I
want
to
quote,
I
then type in the quantity. From here, the formula then needs
to
look
up
what
capacity I am offering in the capacity cell and then the
quantity
in
the
quantity cell before looking to a cell range which shows
quantity
breaks
(ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price
is
then
calculated.

So, for example, if I select a 128MB device, and the customer
wants
200pcs,
it should look up within the cell range and see that 200 comes
between
the
column of 100pcs and 250pcs and offer what is in the 100pc
column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

A guide to the cells a

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd
numerous
ways
to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this
??

Many Thanks,
Ben











--

Dave Peterson

And I've lost track of what you want to do.

Dave Peterson wrote:

It still doesn't look like the formula that Bob suggested.

<<snipped