Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference not working
I have this formula:
=INDEX('Sheet2'!BH2:BH94,MATCH(O2&T2,'Sheet2'!BF2: BF94&'Sheet2'!BG2:BG94,0)) (committed w/ Control Shift Enter) and it works great. However, when I try to put in absolute referencing I get a #N/A error =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) Control Shift Enter will not even commit this formula to the cell. What am I doing wrong . Thanks J |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference not working
Iriemon wrote:
I have this formula: =INDEX('Sheet2'!BH2:BH94,MATCH(O2&T2,'Sheet2'!BF2: BF94&'Sheet2'!BG2:BG94,0)) (committed w/ Control Shift Enter) and it works great. However, when I try to put in absolute referencing I get a #N/A error =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) Control Shift Enter will not even commit this formula to the cell. What am I doing wrong . Thanks J Works for me. To eliminate possible typos, when adding the absolute referencing, just click on the cell reference within the formula and hit F4 to cycle through the options. When finished, commit with CTRL+SHIFT+ENTER. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference not working
Must have been the CSE when I changed the references, anyway it worked this
time. Thanks J "Glenn" wrote: Iriemon wrote: I have this formula: =INDEX('Sheet2'!BH2:BH94,MATCH(O2&T2,'Sheet2'!BF2: BF94&'Sheet2'!BG2:BG94,0)) (committed w/ Control Shift Enter) and it works great. However, when I try to put in absolute referencing I get a #N/A error =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) Control Shift Enter will not even commit this formula to the cell. What am I doing wrong . Thanks J Works for me. To eliminate possible typos, when adding the absolute referencing, just click on the cell reference within the formula and hit F4 to cycle through the options. When finished, commit with CTRL+SHIFT+ENTER. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference not working
One last question:
How would this be modified to find the closest or next smaller value? For example, if I am trying to match .625 and 5000 and the table has: ..625 4000 .55 ..625 4750 .65 ..625 5500 .25 ..625 6000 .35 How do I get it to return the .65? "Glenn" wrote: Iriemon wrote: I have this formula: =INDEX('Sheet2'!BH2:BH94,MATCH(O2&T2,'Sheet2'!BF2: BF94&'Sheet2'!BG2:BG94,0)) (committed w/ Control Shift Enter) and it works great. However, when I try to put in absolute referencing I get a #N/A error =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) Control Shift Enter will not even commit this formula to the cell. What am I doing wrong . Thanks J Works for me. To eliminate possible typos, when adding the absolute referencing, just click on the cell reference within the formula and hit F4 to cycle through the options. When finished, commit with CTRL+SHIFT+ENTER. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute reference (F4 Key) not working | Excel Discussion (Misc queries) | |||
Absolute Cell refernce Not working | Excel Discussion (Misc queries) | |||
absolute reference | Charts and Charting in Excel | |||
Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
Absolute Reference (again) | Excel Discussion (Misc queries) |