What data type does the excel offset function returns ?
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: What data type does the excel offset function returns ?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Greece
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am am trying to write a custom vba (a=excel) funtion with the following syntax : 'Choose_PVC_Speed(Service as String, Access_port as integer) as ???' .. so that when I apply Data Validation to a cell by entering to the Validation List Source field the above function i.e "=Choose_PVS_Speed(A1;B1)" it will provide me with a conditional range of values in that particular cell with a pull down menu type of action whose values are determined by the passed variables A1="Service" and B1="Access_Port". What data type should the function be defined as ? I have a hunch that it should return something simillar to the standard excel offset function, which I got it working for a limited number of inputs (unfortunately got the nested if limitation kicking in), but if only I knew what that is Note also that (preferebly) the full set of values that I want to choose from via the pull down menu is in a different worksheet of the same workbook.

    Thanks in advance

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ps_yannis,

    If I understand your problem correctly (and I'll have to admit I'm not sure I do), you want your UDF to return an array of values, which is the array (range of cells) to be used as the list in a cell validation input list so that it becomes the cell's dropdown data entry list. If this is correct, the function must be typed As Variant, and you should assign an array (presumably of strings) to the function name before the end of the function. Then use the function to fill the worksheet range by entering the function as an Excel Array function, using Ctrl-Shift-Enter.


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    To quote the Excel Help topic for the OFFSET worksheet function...

    "Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned."

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com