Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Cell Outside Of Range
Greetings,
I am trying to get the value from a cell just outside of a named range. This is my formula, it returns the invoice number (column 1): =VLOOKUP(rAInvNo,rInv,1,FALSE) rInv is the range C3:DU839 on the "Input" sheet rAInvNo is the Invoice number on the "Invoice" sheet - which comes from column C on the "Input" sheet (the first column in the range rInv). The cell I am interested in is column B in the same row as the match to the value of rAInvNo which is in column C. I tried to expand the range rInv from C3:DU839 the B3:DU839 - Didn't work at all. Aside from the fact that all of the formulas referencing rInv returned the #N/A error (I would think that since all I did was add a column to the front of the list, that they would all just return the value of the cell next to the one that was requested - Not so!!!) I tried to use this formula as the reference for OFFSET, but debug would not accept it. Anyone have any ideas or suggestions? Any help will be appreciated. -Minitman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Cell Outside Of Range
Try this:
=INDEX(Input!B3:B839,MATCH(rAInvNo,INDEX(rInv,,1), 0)) -- Biff Microsoft Excel MVP "Minitman" wrote in message ... Greetings, I am trying to get the value from a cell just outside of a named range. This is my formula, it returns the invoice number (column 1): =VLOOKUP(rAInvNo,rInv,1,FALSE) rInv is the range C3:DU839 on the "Input" sheet rAInvNo is the Invoice number on the "Invoice" sheet - which comes from column C on the "Input" sheet (the first column in the range rInv). The cell I am interested in is column B in the same row as the match to the value of rAInvNo which is in column C. I tried to expand the range rInv from C3:DU839 the B3:DU839 - Didn't work at all. Aside from the fact that all of the formulas referencing rInv returned the #N/A error (I would think that since all I did was add a column to the front of the list, that they would all just return the value of the cell next to the one that was requested - Not so!!!) I tried to use this formula as the reference for OFFSET, but debug would not accept it. Anyone have any ideas or suggestions? Any help will be appreciated. -Minitman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Cell Outside Of Range
Thanks Biff.
That works great!!! -Minitman On Tue, 15 Apr 2008 00:17:30 -0400, "T. Valko" wrote: Try this: =INDEX(Input!B3:B839,MATCH(rAInvNo,INDEX(rInv,,1) ,0)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Cell Outside Of Range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Minitman" wrote in message ... Thanks Biff. That works great!!! -Minitman On Tue, 15 Apr 2008 00:17:30 -0400, "T. Valko" wrote: Try this: =INDEX(Input!B3:B839,MATCH(rAInvNo,INDEX(rInv,,1 ),0)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding max array value of variable cell range | Excel Discussion (Misc queries) | |||
Functions- Finding and counting specified text in cell range | Excel Worksheet Functions | |||
Finding the bottom non-blank cell in a range | Excel Discussion (Misc queries) | |||
Finding a value associated with a range | Excel Worksheet Functions | |||
finding if name within range | Excel Worksheet Functions |