Good Morning,

I am having huge problems trying to find the formula I need.

I have a large spreadsheet and another document which is the Bill.
What I need to do is to look up Column M on the Spreadsheet between
cells 10 to 500. and if there is a figure in any cells to return
column A.

if there is more than one figure between that range then I want the
second to appear in the cell below in the bill and so on.

Hope this makes sense, If anyone can help I will be so grateful.

Kind Regards, Vicky


--
VickyC
------------------------------------------------------------------------
VickyC's Profile: http://www.excelforum.com/member.php...o&userid=27391
View this thread: http://www.excelforum.com/showthread...hreadid=516823

can u provide more detail

Is the data in column M unique? and is it numeric?

Is it the data in column A unique

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=516823

Hi Dav

Column M are Cost Amounts, Column A is the Ref: Number which is unique
and Column B is the name of the customer.

In a seperate document I have a bill template.
Column A called Ref:
Column B called Details
Column C called Costs

I need to fill this information in from the Spreadsheet, so I need a
formula to look up Column M in the Spreadsheet and if there is an
amount in there then enter the amount into Column C of the Template but
I then will need to do a simular Formula to relate Column A and B to
column A and B in the Spreadsheet?

Thanks,


--
VickyC
------------------------------------------------------------------------
VickyC's Profile: http://www.excelforum.com/member.php...o&userid=27391
View this thread: http://www.excelforum.com/showthread...hreadid=516823

=IF(SUMPRODUCT(LARGE(($M$10:$M$5000)*(ROW($M$10:$ M$500)),ROW(A3)-2))0,OFFSET($A$1,SUMPRODUCT(LARGE(($M$10:$M$5000 )*(ROW($M$10:$M$500)),ROW(A3)-2))-1,0),"
")



Not an elegant solution and it returns the invoice lines in the reverse
order.
I have assumed that the formula will start in the 3rd row of the
spreadsheet hence A3
To return, the values from column M use the amendment below
Each range starting $M will need to be superceded by your sheet name
of the invoice, but I do not know what you are calling it!


=IF(SUMPRODUCT(LARGE(($M$10:$M$5000)*(ROW($M$10:$ M$500)),ROW(A3)-))0,OFFSET($M$1,SUMPRODUCT(LARGE(($M$10:$M$5000) *(ROW($M$10:$M$500)),ROW(A3)-2))-1,0),"
")

It would be nice to have used a similar formula with the small
function, but you would need the smallest excluding 0 as that is what
is returned when the criteria are nor met, this would insure the order
on the invoice would be the same as ir appears on the other sheet. I am
sure someone will know how to do it.

At least it is a start!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=516823

If the following is entered as an Array Ctrl Shift Enter, it will return
the invoice in the correct order! Again make the changes as in the
previous post
=OFFSET($A$1,SMALL(IF(($M$10:$M$5000)*($M$10:$M$5 00),(ROW($M$10:$M$500))),ROW(A3)-2)-1,0)

However when a match can not be made it will return #num! On the sheet,
to overcome this conditionally format the sheet to make the cells with
this format to go white!
So if the formula is in cell A3 enter in the conditional formating
=ISERR(a3) and set the point colour to white!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=516823