Thanks, Paul. But unless there's something I'm not understanding, that doesn't work, and it doesn't work in the sense than when I copy the formula down column C, the indexes don't adjust as they do when I'm not using range names. Example s/s attached.
Jim ________________________________ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Paul Schreiner Sent: Wednesday, June 24, 2009 1:13 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Using range names in formulas I'm sure that your example is a very SIMPLIFIED version of what you want to do... I'm also going to assume that you know (or have a method to figure out) which rows/columns you want to use from the named range. The problem is that your named range is exactly that, a range. To select a specific row of a named range, use INDEX so, if you want to add B3 and A2, given the named range "Odd" is from A1:A5 (including header) and "Even" is "B1:B7", then =B3+A2 would be =index(Even,3)+index(Odd,2) hope this leads you to something helpful. Paul ________________________________ From: Gaurav Bansal <aca.gau...@gmail.com> To: excel-macros@googlegroups.com Sent: Wednesday, June 24, 2009 12:41:03 PM Subject: $$Excel-Macros$$ Re: Using range names in formulas On Wed, Jun 24, 2009 at 4:48 PM, jander <jim.ander...@bankofamerica.com> wrote: (I've been told that my previous post was confusing, so I'll try again.) Suppose I have 2 columns, one contains odd numbers, the other even numbers. They are named ranges "Odd" and "Even", respectively. A B C 1 Odd Even 2 1 2 3 3 4 4 5 6 5 7 8 I want to create a formula in cell C3 which adds the contents of B3 to A2, but which uses the range names instead of B3 and A2. I can do this if I'm trying to add values from the same row. For example, instead of B3 to A3 I can use Odd + Even. But I don't see how to do this for cells in different rows, eg, B3 + A2. I hope this is clear. Thanks, Jim --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
test.xls
Description: test.xls
