offset

L

Larry

I am trying to return the cell reference to be used in an offset function by
looking up the cell based on a specific value. eg. I have a cell with a date
in it on sheet 1. On sheet 2. I have a database of information in which one
column contains dates. I want to use the value of the date on sheet 1 to
find the cell reference with the same value on sheet 2. I will then use that
cell reference in a offset function to return the value of an adjoining cell.
How do I find the cell reference.
 
J

JE McGimpsey

If I understand you correctly:

Assuming your date is in Sheet1, cell A1, your dates are in column A of
sheet2, and your formula is in Sheet1:

=VLOOKUP(A1, Sheet2!A:B, 2, FALSE)
 
L

Larry

Thanks that worked great. Now I need to locate a cell of a specified row
containing a specific value and return the value of the cell 1 column to the
right. The row is specified by a date in the first column.
 
J

JE McGimpsey

That's what VLOOKUP does, so I'm not sure I understand.


Instead of "specified row", "specific value", and "specified by", how
about specifying what references you mean?
 
L

Larry

In my database I have columns for buildings that I work at. eg.
Bldg1,Time,Miles bldg2, time, miles, bldg3, time, miles etc up to bldg 10
among other things. I use this to fill in a specifically formatted timesheet
located on a sheet called time sheet. The timesheet covers 2 weeks. I use
the database to keep a record of all time. While the formula you gave me
worked well in posting daily time on the timesheet now I need to lookup a
particular building and post the time for that building in the appropriate
spot on the timesheet. Here is the formula I have come up with.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
F7 specifies the date or row
The match formula specifies the column to look in
The problem I have is the 6 in A6 and AQ6 specifies a fixed range.
The date should determine that range and float with the date selected.
I haven't figured out how to do that yet.
 
Top