View Single Post
  #2   Report Post  
bobm
 
Posts: n/a
Default

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