| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
named range row offset
I recently discovered naming entire rows -- it makes references super
easy (when your columns line-up). For example, if I name row 2 "MyRow", then in other cells I could just put "=MyRow" and it will reference the cell in MyRow (row 2) that is in the same column where I put the formula. For example, if I put "=MyRow" in cell C6 it returns C2, in D6 it returns D2, E6 -- E2, you get the idea. My question is whether there is an easy way to offset this by one column (so that putting "=MyRow" in cell C6 returns B2 instead of C2). Something like "=MyRow[-1]" would be great. Any ideas? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
named range row offset
Hi!
Try this: =INDEX(MyRow,COLUMN()-1) If you put this formula in column A it obviously can't refer to a column to the left of column A so the value in column A will be returned. Biff wrote in message ups.com... I recently discovered naming entire rows -- it makes references super easy (when your columns line-up). For example, if I name row 2 "MyRow", then in other cells I could just put "=MyRow" and it will reference the cell in MyRow (row 2) that is in the same column where I put the formula. For example, if I put "=MyRow" in cell C6 it returns C2, in D6 it returns D2, E6 -- E2, you get the idea. My question is whether there is an easy way to offset this by one column (so that putting "=MyRow" in cell C6 returns B2 instead of C2). Something like "=MyRow[-1]" would be great. Any ideas? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
named range row offset
P.S.
Also, this will not be able to reference column IV (256). You could do that but then your simple reference method will turn out to be not so simple afterall! Biff "Biff" wrote in message ... Hi! Try this: =INDEX(MyRow,COLUMN()-1) If you put this formula in column A it obviously can't refer to a column to the left of column A so the value in column A will be returned. Biff wrote in message ups.com... I recently discovered naming entire rows -- it makes references super easy (when your columns line-up). For example, if I name row 2 "MyRow", then in other cells I could just put "=MyRow" and it will reference the cell in MyRow (row 2) that is in the same column where I put the formula. For example, if I put "=MyRow" in cell C6 it returns C2, in D6 it returns D2, E6 -- E2, you get the idea. My question is whether there is an easy way to offset this by one column (so that putting "=MyRow" in cell C6 returns B2 instead of C2). Something like "=MyRow[-1]" would be great. Any ideas? Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
named range row offset
Why not use 'Named Formulas' instead?
Try this: Click in *any* cell except in Column A. For demo purposes, say E4. Now, <Insert <Name <Define In the "Names In Workbook" box, type in MyRow Then, in the "Refers To" box, enter =D$2 Then <OK NOW ... enter 100 in say G2. *Anywhere* in Column H enter =MyRow Is that good enough for you? A named formula with relative column reference (relative to the cell in focus during *creation*) and absolute row reference might be what you want/need. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... I recently discovered naming entire rows -- it makes references super easy (when your columns line-up). For example, if I name row 2 "MyRow", then in other cells I could just put "=MyRow" and it will reference the cell in MyRow (row 2) that is in the same column where I put the formula. For example, if I put "=MyRow" in cell C6 it returns C2, in D6 it returns D2, E6 -- E2, you get the idea. My question is whether there is an easy way to offset this by one column (so that putting "=MyRow" in cell C6 returns B2 instead of C2). Something like "=MyRow[-1]" would be great. Any ideas? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
named range row offset
Wow, that's perfect! Thanks so much.
|
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
named range row offset
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Wow, that's perfect! Thanks so much. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you Identify text as a named range in excel | Excel Discussion (Misc queries) | |||
| Named Range Calculations | Excel Discussion (Misc queries) | |||
| Problem with Slow ReCalculation of Dynamic Range Using OFFSET | Excel Worksheet Functions | |||
| How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
| Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Linear Mode
