Results 1 to 9 of 9

Thread: Linking (XP)

  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Linking (XP)

    Good morning
    I've written a spreadsheet for entering orders. The top of the page has all details, At this stage there are 5 suppliers.
    Now I want to add order forms but I would like them to link and populate automatically. The print area is set, so it will only print the order forms.
    So the first form only captures the orders for the first supplier, the second form only the orders from the second supplier.

    I've tried Vlookup but can't get it to work. I've added a stripped down copy of the spreadsheet. The order form is on page 2.

    Thanks heaps
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking (XP)

    I am not sure what you are after could you elaborate?

    What exactly do you want the formula to do?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Linking (XP)

    The top has all the details
    on the first order form I want to list all the products for supplier 1 (in the greenish fields)
    On the second form all the products for supplier 2 - same for supplier 3 - 4 and 5.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking (XP)

    I think Access would be far more suitable for this than Excel. In an Access report, you can have the output grouped by supplier.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Linking (XP)

    Bummer.. I was hoping it could be done.

    Thanks for having a look anyway. Can't use Access as the person who has to do the inputting, barely knows excel, let alone access
    So will have to come up with another solution.

    Cheers

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking (XP)

    An Access database can be made *very* user-friendly - the end-user doesn't have to know anything about Access to use a well-designed database, and will hardly notice that he/she is working in Access.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking (XP)

    If I understand correctly, You can use the VLINDEX function from <post:=395,235>post 395,235</post:>.
    Add the VLINDEX function from the attachment into a module in VB (the post describes it with some detail)

    in A65:C65 enter the numbers -2, -1, 1, respectively (these numbers can be hidden if desired by changing the format to ;;
    in D67-D70 enter the numbers 1,2,3,4, respectively (these numbers can be hidden if desired by changing the format to ;;; and the range expanded if more than 4 items from a supllier will ever be needed)
    in A67 enter the formula:
    =IF($D67>COUNTIF($C$11:$C$40,$B$64),"",vlindex($B$ 64,$C$11:$C$41,A$65,$D67))
    Copy A67 to A67:C70.

    Steve

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking (XP)

    <P ID="edit" class=small>(Edited by wdwells on 26-Apr-08 12:43. Formula error corrected and workbook replaced.)</P>I have modified your file by:
    <UL><LI>Defining B64 as a named range and validating its data to the list at K3:K7
    <LI>Locking all cells with the exception of A11:B41; B2; C5:C7; and B64. I don't think your users need to make changes elsewhere.
    <LI>Adding some code to the "Order 1" sheet
    <LI>Filling the formulae down to the bottom of the Order Range[/list]The Order form is reconfigured when the Supplier at B64 is changed, or a change is made in B11:B41
    If necessary you can add rows to the Order range. But if you do:
    <UL><LI>Ensure the formulae are copied throughout within the Order range;
    <LI>Ensure the formulae within the Print area occupy a the same number of rows as the formulae in the Order range. [/list]If you add new suppliers, you will need to revise the validation at Suppliers
    Do not add or remove rows within the first five rows of the print area
    Note that selecting a different supplier at B64 creates a new order form for thjem.

    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Linking (XP)

    WOW what do I say... That works fantastic...
    Sorry for the delay in answering, but time difference and all that. Depending on where you live in Canada we are just about a whole day ahead of you.

    I will work with this, this is great

    Thanks again

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •