Results 1 to 7 of 7

Thread: Excel Pro Needed

  1. #1
    Senior Member
    Join Date
    Aug 2005
    Location
    Maidenhead
    Posts
    745
    Thanks
    25
    Thanked
    16 times in 7 posts

    Excel Pro Needed

    Hi all,

    I'd appreciate some advice...

    I'm trying to create a spreadsheet with 4 simple columns. Column one is for countries and the other 3 tell me what kind of pricing I can make for that country.

    My question to the forum is that i want to create a textbox where if I type the country into it, it can query the database and spew out the correct pricing column.

    I also want to add future functions to this textbox that can query or add date to the spreadsheet

    Any ideas?

    Thanks

  2. #2
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    use VLOOKUP and create a separate table for pricing. The use data validation on the country column to lookup the range of countries (get that from Data -> Validation)

  3. #3
    Senior Member
    Join Date
    Aug 2005
    Location
    Maidenhead
    Posts
    745
    Thanks
    25
    Thanked
    16 times in 7 posts
    Thank you! I've managed to set up the data validation and seperated the countries and the pricing. However when I go to use Vlookup it wants data from across at least 2 columns and then only allows the value to be found in a row. I was hoping to find the value in a column and a row.

    imagine it something likes this:

    UK International Reduced

    UK Y X X

    US X Y X

    EU X X Y

    Y = Yes X = No

    Note there are over 100 countries.

    I'm looking from the prespective that I can match entire columns (country specific) against other columns (UK, int, redu) and then assign Vlookup to find a value of Y instead of a value of X and then spew out the column title. hope that makes sense.

    Again I appreciate your help

    Sean

  4. #4
    listen to escape fails :) luap.h's Avatar
    Join Date
    Jan 2004
    Posts
    569
    Thanks
    4
    Thanked
    2 times in 2 posts
    If I get what you mean, try this forumula - I've also included and example to see if that's what you need

    Code:
    =OFFSET($A$1,0,MATCH("Y",OFFSET($A$1,MATCH(AB2,$A$2:$A$4,0),1,1,3),0))
    What this does:
    Search down A column from A2 to A4 for the first occurence of the value entered in cell AB2 (eg your country)
    Returns the relative position to A1 of the row the country was found
    Searches for a "Y" in the row given by the last result
    Returns the column position the Y was found
    Looks at the header of the column, and returns it

    Hope that makes sense - to accommodate bigger tables, change this part
    Code:
    MATCH(AB2,$A$2:$A$4,0),1,1,3)
    change $A$2:$A$4 to $A$2:$A$1810 where A1810 has the last country to search through
    change the 3 to 15 for the number of postage types you have

    http://paul.manymoremusic.com/hexus.xls

  5. #5
    One skin, two skin......
    Join Date
    Jul 2003
    Location
    Durham
    Posts
    1,705
    Thanks
    0
    Thanked
    1 time in 1 post
    also, you could try index() and match(). (Put the match() function inside the brackets of the index() function)

  6. #6
    One skin, two skin......
    Join Date
    Jul 2003
    Location
    Durham
    Posts
    1,705
    Thanks
    0
    Thanked
    1 time in 1 post
    If you want to have a database though then surely you should be using Access?

  7. #7
    Senior Member
    Join Date
    Aug 2005
    Location
    Maidenhead
    Posts
    745
    Thanks
    25
    Thanked
    16 times in 7 posts
    Tis all sorted now. Used Access and a friend over on the Kustom forums

    Thanks for the replies, but most went over my n00b head !

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 18
    Last Post: 23-10-2005, 10:58 AM
  2. Help needed badly regarding BIOSTAR K8NHA Pro mobo.
    By tmskilz in forum SCAN.care@HEXUS
    Replies: 22
    Last Post: 28-12-2004, 03:02 AM
  3. Bargain Herc 9800 Pro and 9700 pro!!!!!
    By GuruJockStrap in forum Retail Therapy and Bargains
    Replies: 10
    Last Post: 18-05-2004, 05:34 PM
  4. Radeon 8500 linux driver install problems
    By Dorza in forum Software
    Replies: 0
    Last Post: 22-09-2003, 12:00 PM

Posting Permissions

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