row number formula variable

Question

Hello all,

I have the following formula:

"=VLOOKUP(“MainServices”,'[ExternalFile.xlsx]GeneralData'!J44:V505,MONTH(D1)+1,0)"

In this formula the Vlookup range is J44:V505. I need the first part of this rage to be variable. Anyway, it will always be column J. Row number is the variable. This range should be something similar to J(Variable):V505.

This variable row should be the one with the string “PROFITS”.

So:

In case profits is found on row 10 the range should be J10:V505

In case profits is found on row 15 the range should be J15:V505

I cannot use Named Range because is in an external read only file. I cannot change this external file.

I tried to use Match and index functions but couldn’t make it work.

Any help is most appreciated.

Answer 1

Hi,

As JLLatham pointed out, the INDIRECT function will have to be used to make the row variable.  Since the INDIRECCT() function will not work when the source workbook is closed, the alsternative is to use INDIRECT.EXT.  This is a function available  via a certain Excel addin which you will have o download and install.

Would this be acceptable?

Answer 2

You should be able to use INDIRECT() if [ExternalFile.xlsx] is open, but you'll get a #REF! error as soon as you close it, you'd have to convert the value returned to a 'hard' number using Edit-->Copy followed by Edit --> Paste Special with Values selected.

The formula would look like this:

=VLOOKUP($A$11,INDIRECT("'[ExternalFile.xlsx]GeneralData'!$J$" & MATCH("PROFIT",[ExternalFile.xlsx]GeneralData!$J:$J,0) & ":$V$10"),MONTH(D1)+1,FALSE)

But somehow I think you're going to tell me it's impractical to always have both files open at the same time?

Right now all I can come up with for that situation is a VBA solution that would actually change the formula so that the INDIRECT() is taken out of it.  You'd have to click a button or select a specific cell to get the formula updated.  Would something like that be acceptable?

Answer 3

Here is my macro (VBA) solution.  The way it would work would be like this:

When you select the cell that you need the VLOOKUP() to be performed in, the code creates the formula based on where PROFIT is in column J in the other workbook.  If you happen to already be "in" that cell, then you would need to click another cell on the sheet and then click/select it again to refresh the formula.

There is a Const value that must contain the full path and filename to the ExternalFile.xls and the worksheet that the table is on with the ! ending that information.  You must change that in the code to match your path, filename and sheetname since I've shown the full path on my system here as an example.

The example also assumes the cell needing the formula is C6, which I just picked from thin air, change that also.

To put the code to a test in your workbook, open the workbook that needs the VLOOKUP() formula, select the sheet it needs to go onto.  RIGHT-click on that sheet's name tab and choose [View Code] from the list that appears.  Copy the code below and paste it into that code module and make the needed edits to it.  Close the VBA Editor using its red-x or by pressing [Alt]+[Q] to return to the worksheet.

Make certain that you save the workbook as a Macro Enabled workbook, type .xlsm.

Here is the code with the lines that need to be changed indicated in bold text.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  'This Const value must have the FULL path to the ExternalFile.xlsx

  'file in it along with the worksheet name just as if it were part

  'of a formula on the worksheet

  'I have shown it with my test setup path, you must change it

  'to work with your system setup.

  Const thePath = _   "'Q:\ExcelHelpGiven\for_RaulSousa[ExternalFile.xlsx]GeneralData'!"

  Dim startRow As Long

  Dim tempAddress As String

  'assumes the formula needs to go into cell C6, change as needed

  'the $ symbols must be part of the indicated cell address.

  If Target.Address <> "$C$6" Then

    Exit Sub

  End If

  'first we have to find where "PROFIT" is on the sheet

  Application.ScreenUpdating = False ' prevent flickering

  tempAddress = thePath & "$J:$J"

  Target.Formula = "=MATCH(" & Chr$(34) & "PROFIT" & Chr$(34) & _

   ", " & tempAddress & ",0)"

  startRow = Target.Value ' get returned value for further use

  'now use that row number (where PROFIT was found in column J)

  'to build a new formula for the Target cell

'

  Target.Formula = "=VLOOKUP(" & Chr$(34) & "Main Services" & Chr$(34) & _

   "," & thePath & "$J$" & startRow & ":$V$550,MONTH($D$1)+1,FALSE)"

  'and that should do it for you.

End Sub

Answer 4

Thank you for your replayJLLatham.

In this case, the string profits is located somewhere in collumn J.

The reason i must use row numbers is that there are more than one MainServices string in this row and i want to retreive the value which is below profits string.

Answer 5

If you are using Excel 2010, as indicated by where you've asked the question, try just doing away with the row numbers completely:

=VLOOKUP(“MainServices”,'[ExternalFile.xlsx]GeneralData'!$J:$V,MONTH(D1)+1,0)

The penalty for this is that performance suffers because Excel may have to look at more cells when the match isn't found. This assumes that the string PROFITS is on the sheet somewhere (you didn't mention where that would be at?  Which column?) and would normally be found in an appropriate row to set up the variable row number you would need with your hypothetical formula.