hello some more information on the function i attempting to use...
=Cell("row",VLOOKUP(B2,sheet1!A:A,1,FALSE)) 'lookup date
=Cell("col",HLOOKUP(A2,sheet1!A:G,1,FALSE)) ' lookup name
but get alert that formula contains an error
"bobm" wrote:
hello,
i am sure this is a pretty simple question but cannot work out.
i have two sheets - sheet1 and sheet2
in sheet1 i have names in Row 1 and dates in Column A
what i want to do from sheet2 is do a vlookup on a date in sheet1 and return
the cell reference (row number) of that lookup value.
and do the same with the name, do a hlookup on sheet1 and return the cell
reference (col number) of the lookup value.
This will then give me a range to plot to colour of the intersecting cell in
sheet2
i have tried using the lookup functions can only return the value, not the
cell reference. is there a better way to do this?
appreciate any help or other ways to do this.
bobm
|