asked on

locate the cell reference in a changing row scenario

When the web query refreshes, the row which contains the data changes location.

I need to include the cell in my formula. Could you please help and advise how to reference the moving row in my formula ?

In the enclosed sample file the reference cell is highlighted in yellow.
sample-to-locate-date-cell.xlsx

Arno Koster

There does not seem to be a web data connection on the sheet, but in general the question comes down to parsing the results of a query.

As far as I know the web query option does not allow you to reference parts of its output, so the easiest way would be to adapt the web query to only return the row you are looking for.

When this is not possible, you could use macro code to parse the results and identify the row you are looking for.
Identification can be by

last row with data before disclaimer

second row which is encapsulated by empty rows

etc etc

If you have found out a good identification method but have problems implementing it, please let us know so that we can help you.

oldrin

ASKER

I have not included the webquery in the sample file.

From your above 03 suggestions, the first option of "last row with data before disclaimer" would be the solution for me.

Could you please write a macro or a vb code to achieve the above ?

Thanks.

jkasavan

This vba code stores the value of the row number in the variable RowNmbr and the cell address in the variable CellRef

Sub FindMovingData()
'
' FindMovingData Macro
'
Cells.Find(What:="DISCLAIMER", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlUp).Select
RowNmbr = ActiveCell.Row
CellRef = ActiveCell.Address
End Sub

krishnakrkc

Hi

May be

=LOOKUP(9.9999E+307,A:A)

and format the cell

Kris

oldrin

ASKER

Hi jkasavan,

After adding the code to the module, could you please advise how to use it in the excel worksheet ?

jkasavan

What version of Excel?

How is the Web Query refresh initiated - manually? A macro? On a timed basis?

oldrin

ASKER

Version is Excel 2007

Web query refresh is manually initiated.

SOLUTION

jkasavan