Home |
Search |
Today's Posts |
#1
|
|||
|
|||
offset
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. |
#2
|
|||
|
|||
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) In article , "Larry" wrote: 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. |
#3
|
|||
|
|||
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. "JE McGimpsey" wrote: 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) In article , "Larry" wrote: 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. |
#4
|
|||
|
|||
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? In article , "Larry" wrote: 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. |
#5
|
|||
|
|||
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. "JE McGimpsey" wrote: 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? In article , "Larry" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help using the offset function differently | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
dynamic offset | Excel Discussion (Misc queries) | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Passing Cell Address to Offset | Excel Worksheet Functions |