Results 1 to 2 of 2
2002-01-06, 00:30 #1
- Join Date
- Nov 2001
- MI, USA
- Thanked 0 Times in 0 Posts
Function NAME() to return Named Range (XL2000)
I've looked at a number of posts but... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Rather than using INDEX, INDIRECT or MATCH functions, which is making me dizzy--certainly for anyone brave enough to try to decipher my work after I'm gone. Any suggestions on creating a new custom function i.e. NAME() that takes the content of a cell and returns it as a literal named range??
This would allow me to add functionality to the usual (V/H)LOOKUPs and other functions where a number or reference is required. In most of my ranges, I have assigned names and in this example, instead of the following statement
where the Lookup_value; A2 is a labor category index(#1-53),
the Table_array; 00_08_Straight refers to a range on another sheet that contains titles and straight time wage rates among others. This sheet is laid out the same way & format for 3 other sheets with ranges 00_08_Overtime, 00_08_Straight_Overseas & 00_08Overtime_Overseas rates, and
Col_index_num: 2000Rates is the named range for a column in the 00_08_STRAIGHT range, which BTW, I'm going to use the tips I found on relative named ranges--thanks.
With the new function, the formula would now be as follows;
Now whenever the text values in G2 or H2 change, the result changes accordingly. From where I sit, I think this function would have plenty of uses where it would be otherwise inconvenient to edit formulas or spend a lot of time verifying formulas and for cases I haven't even thot up yet.
I guess the key is to get rid of the quotation marks that usually surround a text value.
Have a nice weekend and looking forward to some ideas on Monday
2002-01-06, 13:45 #2
- Join Date
- Jan 2001
- West Long Branch, New Jersey, USA
- Thanked 9 Times in 7 Posts
Re: Function NAME() to return Named Range (XL2000)
I also had a fear of INDIRECT until I started using it alot. As far as those who come after you knowing what you did...it's all in the documentation. INDEX isn't all that bad either.
Although you wanted a macro, my macro-writing skills are not that good. So attached is a workbook using INDIRECT to illustrate what I think you're after. If you can get thru it, I think you can adopt it to your situation.
Many functions, such as VLOOKUP, take various arguments that can be named as cells or as ranges. But, as you observed, things can be much more powerful if you can decide, based on some cell, what those arguments should be. That is exactly what INDIRECT was intended for.
The attached workbook creates 2 tables in the same locations on Sheet1 and Sheet2. I also give these tables range names (trivially named range_sheet1 and range_sheet2 but don't take the presence of "sheetx" in the name as anything significant). I put the tables in the same location in each sheet for a reason, which I'll mention in a moment.
Now also keep in mind this is a trivial example. So I'm only using indirection for the table to get the result from. I know you also wanted to use some approach for the col from which the result is obtained. But if you can understand this, you'll be able to get that yourself.
Now for the key part. You'll see there are 2 approaches (labeled Method 1 and Method 2) to using the dif tables on Sheet1 and Sheet2. Both use INDIRECT but are slightly different
Method 1: uses the range names for the tables, where the name is stored in cell H9.
Method 2: uses just the sheet number in H10 and builds in the formula in F10 the complete input to the INDIRECT function
These 2 methods are pretty much the same when you come down to it. With #1, you have to change the contents of the cell to another range name to use the other table. I've named my ranges almost identically except they differ in the last character ("...1" vs "...2"). But with this approach, your range names can be anything - you just have to edit the name in H9 to identify which table/range you want.
In Method #2, I used explicit cell references. The identification of which cells to used is done with the INDIRECT in F10. The INDIRECT builds up the cell reference by concatenation, recognizing that the "Sheetx" part of the reference is the same except for the x. So "Sheet" is a constant. It is then concatenated with the number of the sheet in H10, an exclamation mark (part of the syntax for an off-sheet reference) and the specific cells found in I10.
[In case you don't know what Concatenation is, let me know - that's what the & does]
A few observations to help along:
- if you've given your sheet names, Method 2 would have to be changed slightly. H10 would have to have the complete sheet name, not just a number.
- given that my table is always in A2:B4, I didn't need I10. Method 2's INDIRECT could have read
- given that the tables are in the same place on each sheet and my sheets are named Sheet1 and Sheet2, methods 1 and 2 are very similar
Now to Method 3, which is more powerful.
Methods 1 and 2 suffer in that when I want to change the table I'm referencing, I have to change the identification of where the table is - in either H9 or H10 (and maybe I10). So I'm constantly editing the tables. Method 3 avoids that.
With Method 3, I put in all the range names where the table could be in H13 and H14. I gave these 2 cells a range name of sheet_refs (table_locations would probably have been a better name). G13 is then used to say which range I want now. Since I only have 2 ranges, G13 should only be 1 or 2 (and I could create a Data Validation to ensure that). G13, used in conjunction with INDEX, returns 1 of the 2 ranges. Then input this into the INDIRECT and you have the argument to VLOOKUP.
If you might add more tables in the future, it might actually be better to use OFFSET rather than INDEX. INDEX would give an error if you exceed 2 since our sheet_refs was defined as being only 2 entries (although we could get into dynamic ranges - but that's a story for another time). OFFSET would use 0 for the entry in H13 and 1 for the entry in H14 (rather than the 1 and 2 of INDEX). I won't provide the complete syntax here.
One could also adopt Method 2 to Method 3. That is, you'd have a Sheet number/name and cell reference for each possible table. Then the Index would be used twice - once for the sheet name and once for the cell reference. I won't provide this here either.
You can also extend any of the above for which col of the table to return.
My personal opinion would be to try to see if you can use Method 3. This way, you list all the possibilities once. All that a person now has to do is change G13. I'd put a validation in on G13. Even with a macro, I believe that you'd have to list the locations (sheets, ranges, cells) of where things are at. Given an input like G13, a macro could then fix the VLOOKUP formula directly to have the direct refs.
One other thing you can do to convince yourself that things are working. Select cell F9. Now go to the Formula Bar and select the H9 (just these 2 characters). Press the function key F9 (sorry about that-just a coincidence that I used cell F9 and you need function key F9). You should see "range_sheet1" appear in the formula bar since that is what H9 has. Now hit the escape key to restore "range_sheet1" to H9. Now change "range_sheet1" to "range_sheet2" and repeat the above. This is a trick I learned from Bob Umlas - using function key F9 kind of updates the formula to reflect the current value of any cell refs used in the formula.
Hope this is of some help.