| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return cell reference instead of text from within lookup() functio
Hi,
I'm trying to use the following formula to find data for a particular day of the month and then sum() a 7 day range of data for that week. =SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap Daily'!$B32:$CP32),0,-6,1,7)) I think the problem is that offset() is expecting a cell reference, but lookup() is returning cell content. How can I search and locate my expected content and pass that content's cell reference to lookup(). Thanks, Earl |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return cell reference instead of text from within lookup() functio
Last line should read:
How can I search and locate my expected content and pass that content's cell reference to offset(). "earls" wrote: Hi, I'm trying to use the following formula to find data for a particular day of the month and then sum() a 7 day range of data for that week. =SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap Daily'!$B32:$CP32),0,-6,1,7)) I think the problem is that offset() is expecting a cell reference, but lookup() is returning cell content. How can I search and locate my expected content and pass that content's cell reference to lookup(). Thanks, Earl |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return cell reference instead of text from within lookup() functio
Try
=SUM(OFFSET(INDEX('Seat Cap Daily'!$B32:$CP32,MATCH('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,0)),0,-6,1,7)) This gives an exact MATCH only, if you're looking for the nearest match in a sorted range then change the first zero (the third argument of MATCH) to a 1 "earls" wrote: Hi, I'm trying to use the following formula to find data for a particular day of the month and then sum() a 7 day range of data for that week. =SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap Daily'!$B32:$CP32),0,-6,1,7)) I think the problem is that offset() is expecting a cell reference, but lookup() is returning cell content. How can I search and locate my expected content and pass that content's cell reference to lookup(). Thanks, Earl |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return cell reference instead of text from within lookup() fun
Works as you describe. Thanks a bunch for your help...
"daddylonglegs" wrote: Try =SUM(OFFSET(INDEX('Seat Cap Daily'!$B32:$CP32,MATCH('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,0)),0,-6,1,7)) This gives an exact MATCH only, if you're looking for the nearest match in a sorted range then change the first zero (the third argument of MATCH) to a 1 "earls" wrote: Hi, I'm trying to use the following formula to find data for a particular day of the month and then sum() a 7 day range of data for that week. =SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap Daily'!$B32:$CP32),0,-6,1,7)) I think the problem is that offset() is expecting a cell reference, but lookup() is returning cell content. How can I search and locate my expected content and pass that content's cell reference to lookup(). Thanks, Earl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find a value in a table and return the cell reference | Excel Worksheet Functions | |||
| formula to lookup value and return value from cell at left of target | Excel Worksheet Functions | |||
| Making cell reference absolute makes cell format text | Excel Worksheet Functions | |||
| Lookup a value and return its cell reference instead of the value | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
Hybrid Mode
