embedded reference cell in offset formula 
Author Message
 embedded reference cell in offset formula

The formula:
=HLOOKUP($L9,(OFFSET($A$14,1,0,2,10)),2,FALSE)*N$5

looks for the value found in cell L9 in a 2x10 array of cells offset one row
below cell A14

I would like to replace the fixed reference A14 with a cell from col 1 (A)
determined by matching the value from an input cell....
ie run the hlookup on an array offset from the cell in col A containing
"text" input in another cell

The formula:
=ADDRESS(MATCH(M4,$A:$A,0),1)

will return the address of the proper cell but if I embed the second formula
in place of $A$14

The formula fails

I hope a simple syntax fix is all I'm missing... any ideas??

-Andy
By the way...
The technique is being used to access data in the form

RECORD ID 1
sub record a    sub record b    sub record c....h
data 1a            data1b
RECORD ID 2
sub.....
data 2a etc.

I can not reformat the data into more traditional rows/columns as it is from
an external source and I'd like to read it directly...

Any suggestions?



Mon, 31 May 2004 07:46:23 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. OFFSET using cell reference in another cell

2. embedded offset function in array formula?

3. Summing using formulas already in cells, not cell references, or values in cells

4. Reference an OFFSET cell

5. Cell Formula Help - Sum/Offset/Match

6. blank cells in offset formula used in chart

7. Reciepient as a cell reference and Relative cell refrence in a formula

8. Need to know if a cell has text or a formula referencing other cells

9. cell reference in a formula based on value in another cell

10. Transposing pasted cells without changing cell references in formulas


 
Powered by phpBB® Forum Software © phpBB Group