+ Reply to Thread
Results 1 to 3 of 3

formula to return a certain Time Zone depending upon country user inputs

  1. #1
    Registered User
    Join Date
    10-16-2007
    Posts
    9

    formula to return a certain Time Zone depending upon country user inputs

    Hi Guys,


    I was wondering if you could give me a bit of help? (Excel 2007)

    I'm trying to create a formula which will return a certain Time Zone depending upon which country has been inputted.

    This will be populated on 2 seperate .xlsx worksheets, the first being the Master (where all the original data is stored) and the Second being the Validation (where the user will input the data to return the ceirtain Time Zone)

    I have the following formula so far: =IF(A3=""¶""¶IF(ISNA(VLOOKUP(A3¶'K:\Keith McIntyre\Documentation\[Siebel_Time_Zone_Master.xlsx]Sheet1'!$A$2:$A$699999¶3¶0))¶"Time Zone Not Found"¶"Time Zone Found"))

    This basicly returns the Value Time Zone Found or time Zone not Found if the country inputted into the Validation matches the Country in the Master.

    However what i require is a forumla to match the country inputted in Cell A3 of the Validation to match the Country located in the Master, and instead of returning Found or not Found, i require the ceirtain Time Zone to be returned which is located in the cell next to the country.

    PLease see the following links for Images of the Master and the Validation

    Unfortunately my Excel formula knowledge is very limited so i'm struggling to get the last bit

    I'd really appreciate any feedback as this is really frustrating me!!!


    Thank you

    Keith
    Last edited by VBA Noob; 07-31-2008 at 06:18 AM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Your VLOOKUP is flawed because you can't return a value from the 3rd column of a single column range....also I presume ¶ would normally be ,

    Try this version

    =IF(A3="","",IF(ISNA(VLOOKUP(A3,'K:\Keith McIntyre\Documentation\[Siebel_Time_Zone_Master.xlsx]Sheet1'!$A$2:$B$699999,2,0)),"Time Zone Not Found",VLOOKUP(A3,'K:\Keith McIntyre\Documentation\[Siebel_Time_Zone_Master.xlsx]Sheet1'!$A$2:$B$699999,2,0)))

    Do you really have 699999 rows in your table?

  3. #3
    Registered User
    Join Date
    10-16-2007
    Posts
    9
    Thanks Mate! That works perfectly!!!


    No i don't have 699999 rows, but i set it to that just incase i ever went down that far.

    Also the reason my seperators are ¶ instead of , is because i've changed this in the default Windows options.


    Many Thanks Again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1