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. |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com