Forum Discussion

ccivitella01's avatar
ccivitella01
Copper Contributor
Feb 05, 2021

Vlookup help

I have a spreadsheet where I need to lookup a value based on a drop down menu that changes for the period and year.   Variance Tab Data Tab I am trying to get a return value of ($23,3...
excel
Formulas and Functions
ccivitella01's avatar
ccivitella01
Copper Contributor
Mar 10, 2021

ccivitella01 

When I tried Hans solution I got a #VALUE! error.  When I tried JMB17 solution, it did not return any results.  I tried the formula in cell D18 on the Monthly Variance Report tab.  

JMB17's avatar
JMB17
Bronze Contributor
Mar 10, 2021

I'll try to explain a little better. Some of excel's functions, like index, offset, xlookup, indirect, are capable of returning data 'by value' or 'by reference'. Meaning, it can return the value of a cell or it can return the cell (as a range object).

So, the index function could be used in the same manner as you would use a range reference.
Instead of writing SUM(D2:D6), you could have =SUM(INDEX(D2:D23,1):INDEX(D2:D23,5)).

In the version I posted, you can see it is testing the typename of the argument passed to it and, if it is a 'Range' (the function passing information to it is capable of passing by reference - index, xlookup, offset, indirect, or even just plain cell reference), then it copies the comment from that cell to the cell calling it and also returns the cell value. Otherwise, if arg is not a range, then it just returns whatever value was passed to it (presumably an error, like #N/A).

  • ccivitella01's avatar
    ccivitella01
    Copper Contributor
    Mar 11, 2021
    I moved the IFERROR statement and I got the results I was looking for. However this brought up another issue. Is there a way to auto-resize the comment box when the different periods are selected. I looked up information and saw that the following VBA code can be run to do that
    Sub FitComments()
    'Updateby20140325
    Dim xComment As Comment
    For Each xComment In Application.ActiveSheet.Comments
    xComment.Shape.TextFrame.AutoSize = True
    Next
    End Sub

    However it is too much to ask my end-users to run the macro because each one has a different level of expertise in using excel. I need some way of doing this automatically or having the box at a preset size.

    thanks,
    • JMB17's avatar
      JMB17
      Bronze Contributor
      Mar 11, 2021

      ccivitella01 

       

      I believe you could build that into the function, if that will work for you?

       

      Public Function IndexMatchComment(arg As Variant) As Variant
     Application.Volatile True
     
     If TypeName(arg) = "Range" Then
          With Application.Caller
               If Not .Comment Is Nothing Then .Comment.Delete
               If Not arg.Comment Is Nothing Then
                    With .AddComment(arg.Comment.Text)
                         .Shape.TextFrame.AutoSize = True
                    End With
               End If
          End With
     End If
          
     IndexMatchComment = arg
     
End Function

       

Resources