Glad to help!
Posts by rrexpress


Derk, I appreciate the 'vote of confidence' in the formula. However, a more robust (and more accurate) version is:
=VLOOKUP(1,OFFSET(Calculations!H$1,(ROW()ROW(A$1))*20,,20,32),8,FALSE)
since the original poster requested an initial VLOOKUP block range 20 rows high by 32 columns wide (H1:AM20). In this case, the VLOOKUP() column index number (=8), was well within the 20 column range my original formula had. This revised formula 'restores' the 32 column flexibility the O.P. apparently wants.
Regards,
Tom

Another possibility:
=VLOOKUP(1,OFFSET(Calculations!H$1,(ROW()1)*20,,20,20),8,FALSE)
Tom

Hi Coyent,
Chris's solution is good (I had an alternative formula that worked, but Chris's is shorter and better). However, here is another possible solution:
Don't change your formula, AND enter a " " in column 4 for the cell that is blank, which is what you mentioned as an alternative solution (except that it creates a formula problem elsewhere). You can eliminate this formula problem by using the SUM function to refer to the " " cell. For example, if you have a formula in cell D1 that reads:
=B1*C1
where B1 = 2 and C1 = 3.5, then there are no problems with the D1 formula. A problem arises, however, when B1 has a space in it, that is, it = " ". (You get a #VALUE! error.) This problem is eliminated by using this formula:
=SUM(B1)*C1
You'll note that the SUM function forces text to evaluate to 0 (zero), so entering a space in B1 is the same as entering a 0 using the formula above.
Perhaps you can adapt this approach to your situation . . .
HTH,
Tom
p.s. Here is the formula I worked up:
=IF(ISNUMBER(A1),IF(ISBLANK(VLOOKUP(A1,Data1,4,FALSE)),"",VLOOKUP(A1,Data1,4,FALSE)))
As you can see, it is a little longer and clumsier than Chris's.

Here is an alternative method for incrementing a row reference when copying across columns. (See this forum's thread: http://www.ozgrid.com/forum/viewthread.php?tid=1530).
Use the OFFSET function to achieve similar results by placing this formula in column D and copying across (as needed):
=SUM(OFFSET($A$1,0,0,COLUMN()2,1))
The effect is such that at each column to the right of the previous one, the row number increases by 1, and just like the thread mentioned above, will achieve the same results as if you had entered the following:
=SUM($A$1:$A$2) at Column D
=SUM($A$1:$A$3) at Column E
=SUM($A$1:$A$4) at Column F, et cetera.The values returned are the same as those using the INDIRECT/ADDRESS formula, but this OFFSET function formula is a little shorter (and probably a little faster when dealing with hundreds of formulas, although I haven't tested this).
Regards,
Tom

Thanks, Dennis, worked like a charm!
Tom

What VBA code (or other technique) is necessary to return to the same place in a document when it is opened? In my case, I have several lines of introductory/explanatory remarks, followed by an 'input' table. What I would like to do is have the cursor placed at the first cell in the table every time the document is opened. I know this has probably been discussed before, and is probably trivial, but your help is appreciated. (BTW, I'm using Word 97).
Thanks, Tom

Chris,
Your test results were interesting. However, the better time for my array formula might have been achieved because it doesn't test for no values [i.e., IF(COUNT(C1:IV1)=0,"", . . .]. My formula does return a '0' (zero) when there aren't any values, but that isn't really the correct answer. I'm sure a different test result would have occurred with a "no value present" test, maybe slower than yours. Perhaps a combination of the two formulae would be faster, to wit:
=IF(COUNT(C1:IV1)=0,"",OFFSET(C1,0,253MAX(ISNUMBER(C1:IV1)*(256COLUMN(C1:IV1))),1,1))
As always, there's more than one way to skin a cat.
Tom

Here's another array formula that works. It's slightly more compact, and achieves the same result. Note: if you don't want to copy the formula down through column C, change the "ROW(C1)" to "1".
=INDIRECT(ADDRESS(ROW(C1),256MAX(ISNUMBER(C1:IV1)*(256COLUMN(C1:IV1)))))
I'll be happy to supply an explanation of the formula, if anyone is interested. But for now, it's off to the pub.
Cheers, Tom

Here is a technique for having a temporary color band appear in the current row and column. It uses conditional formatting and just a touch of VBA, and is fairly easy to implement.
First, select a worksheet where you want to use this technique. Next, select all cells on the worksheet. (The easiest way to do this is to click on the box just above row 1 and to the left of column A.) Then select Format/Conditional Formatting from the menu. For Condition 1, choose Formula Is, and then type in the box:
=OR(AND(CELL("row")=ROW(),COLUMN()<=CELL("col")),AND(CELL("col")=COLUMN(),ROW()<=CELL("row")))
[Note: it’s easier to copy this formula to the clipboard, then paste it from the clipboard into the Formula Is box.] Then press the Format button, and choose a color under the Patterns tab. (My first preference is light yellow.) Then press the OK button.
Now press AltF11 to open the Visual Basic Editor. Locate your workbook name in the Project window, and doubleclick it to expand its listing, if necessary. Then doubleclick the item labeled Sheet1 (or whatever name your worksheet is). Then enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End SubSwitch back to the worksheet, and move around. You should now have row and column bands of light yellow (or whatever color you chose) wherever you move the cell pointer!
Applicability and limitations of this technique:
I have used this technique successfully in Excel 97 and Excel 2000, with Windows 98, Windows NT4, and Windows XP operating environments. I don’t know if it will work in Excel 2002, although I hope it does.
First limitation: you can’t use the F8 key to extend a range selection, though ShiftF8 works fine if you hold down the Shift key. Second limitation: you do surrender one conditional format, if this is important to what you are trying to accomplish on a particular worksheet. If so, check out this link (suggested by Ivan Moala): http://www.xcelfiles.com/Excel02.html#AnchorLink1Additional Considerations:
If you want to highlight only the current cell as you move about the worksheet, and not the row and column, modify your conditional formatting statement to read:=AND(AND(CELL(“row”)=ROW(), COLUMN() . . . .
instead of starting with
=OR(AND(CELL(“row”)=ROW(), COLUMN() . . . . .
Also, to turn off the bands (for printing purposes, say), you need to make a couple of adjustments. Select all cells again, and modify Conditional Format 1 to read this way:
=AND($A$1<>””, OR(AND(CELL(“row”)=ROW(), COLUMN() . . . . .)))) [Note the four close parentheses at the end of this formula construction.]
Then adjust the VBA code to read:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If [A1].Value <> "" Then
Application.ScreenUpdating = True
End If
End SubNow, if cell A1 is blank, the cell highlighting is turned off. Of course, you can choose some cell other than A1 if you want, or devise some other test. Just make sure that you change the references appropriately.
Tom McClain
Round Rock, TX