Named Ranges??

[razzandy]

I have a database in excel which records all my orders with consecutive numbers i.e. 1, 2, 3 etc down one column. I’ve given the database table a named range called ‘Data’ using the following formula:

=OFFSET(Orders!$A$2,0,0,COUNTA(Orders!$A:$A)-1,17)

What I’m after is some formula to create a named range for just the orders. So when another order is added it looks for that order and gives it name like ON1, ON2, ON3, and so on. I need this facility so I can go back to a userform and enter the order number and bring up the order in a listbox using the rowsource command! Is this possible?

Many Thanks in advance

Ryan UK

 

[Andrew Poulsom]

If you name the first column in your database (just the order numbers) you can use the WorksheetFunction MATCH to return the row containing the entered order number and use this to select the appropriate row in your list box.

 

[s-o-s]

On 2002-10-14 07:27, razzandy wrote:
I have a database in excel which records all my orders with consecutive numbers i.e. 1, 2, 3 etc down one column. I’ve given the database table a named range called ‘Data’ using the following formula:

=OFFSET(Orders!$A$2,0,0,COUNTA(Orders!$A:$A)-1,17)

What I’m after is some formula to create a named range for just the orders. So when another order is added it looks for that order and gives it name like ON1, ON2, ON3, and so on. I need this facility so I can go back to a userform and enter the order number and bring up the order in a listbox using the rowsource command! Is this possible?

Many Thanks in advance

Ryan UK

Ryan,

I think you're there already, almost.

Insert Name Define in the refers to box enter youre offset function BUT change then width from 17 to 1.

=OFFSET(Orders!$A$2,0,0,COUNTA(Orders!$A:$A)-1,1)

Put the Name in as Orders

then you can use match function to search the reference of your order as follows

Assume C1 has your target Order Number (on a seperate sheet otherwise it appears in the middle of your table. )

=Match(C1,Orders,0)

this will return the position in your list.

 

[razzandy]

Does the MATCH formula work on recurring number entries?

The amount of recurring order numbers depends on the amount of products entered on the order. So if my order as 5 products then the orders numbers would appear 5 times!

Many thanks for your reply :wink:

Cheers

Ryan UK
This message was edited by razzandy on 2002-10-14 07:47

 

[Andrew Poulsom]

MATCH will only find the first occurrence. You could find the next by resizing the lookup range to start in the next row.

 

[razzandy]

I've been playing all day since my last post and managed to come up with this as a named range! It works perfect1 Let me know what you think!

=OFFSET(Orders!$A$1,MATCH(Sheet1!$C$1,Orders!$B:$B,0)-1,0,COUNTIF(Orders!$B:$B,Sheet1!$C$1),17)

Thanks for all your help

Ryan UK :wink:

 

[Andrew Poulsom]

Looks great - well done!