IMO, the most dangerous thing about INDIRECT is that they blow up (essentially) the trace functionality when they’re present.
Ok, I have to try this now, I didn’t know this was a thing. Definitely have been using separate tabs for creating unique lists (via remove duplicates).
I don’t like getting files and there is junk formatting in cells such that ctrl + end goes way past what I need. I’d like there to be a version of ctrl + end that stops at the bottom right cell with a value/formula
You may be interested in this code which I got from an AO poster, and I apologize that I forgot the name of who it was. As I recall, he wrote it at the behest of his boss who was used to the way things worked in lotus 123.
Copy/paste this into the vba module of your personal.xlsb and every time you hit ctrl+end it will take you to the current-last-cell of your worksheet.
The penultimate row with “F1”, that’s for something else. Include it at you own discretion. It will just reassign F1 to “nothingness” so that when you hit “F1” instead of “F2” or “Esc” nothing will happen…literally, nothing will happen. That I got from some random person on the internet. It’s not AO-related.
If you are the AO person that gave me this code, come on in and take a bow. It’s been a boon to my Excel-productivity for the last ~20 years.
personal.xlsb
Sub zzz_FindLastCell()
’ Application.Calculation = xlCalculationManual
Dim LastColumn As Integer
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="", After:=[a1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
'MsgBox Cells(LastRow, LastColumn).Address
Application.GoTo Reference:=ActiveSheet. _
Range(Cells(LastRow, LastColumn).Address), _
Scroll:=False
End If
’ Application.Calculation = xlCalculationAutomatic
End Sub
Sub Auto_Open()
Application.OnKey “^{END}”, “zzz_FindLastCell”
Application.OnKey “{F1}”, “”
End Sub
2 Likes
There should be an animatronic hand that reaches out and slaps you in the face for nesting IF statements.
2 Likes
I would probably have no skin left on my face.
1 Like
I think my deepest nested if is like 12 … I’d probably be dead.
2 Likes
-------heavy sigh--------
okay.
so, we used to have a thread for “confusing functions” on the old AO, and yes, I backed that thread up before AO went kaput. Let’s see what I can dig up.
1 Like
Okay, I will start with a short one.
=IF(F2>0,$F$1,IF(G2>0,$G$1,IF(H2>0, $H$1, IF(I2>0, $I$1, IF(J2>0, $J$1, IF(K2>0, $K$1, IF(L2>0, $L$1, IF(M2>0, $M$1, IF(N2>0, $N$1, IF(O2>0, $O$1, IF(P2>0, $P$1, IF(Q2>0, $Q$1, 0))))))))))))
That wasn’t due to any AOians, but they had inherited a spreadsheet with that formula in it.
and @Mountainhawk – I’ve got one post where you claim to have 4 consecutive columns with 4 nasty formulas. I can share them… but they’re not nested IFs.
it’s a bunch of INDEX (MATCH)s
here’s another short-ish nested IFs:
=IF(K3=0,0,IF(J3=0,0,IF(J3=25,1,IF(J3=50,2,IF(J3=1 00,3,IF(J3=250,4,IF(J3=500,5,0)))))))
supposedly in the same spreadsheet
=IF(L3=0,IF(K3=0,0,IF(K3=100,19,IF(K3=300,20,IF(K3 =500,21,IF(K3=800,22,23))))),IF(K3=25,IF(L3=25,1,2 ),IF(K3=50,IF(L3=25,3,IF(L3=50,4,IF(L3=100,5,6))), IF(K3=100,IF(L3=25,7,IF(L3=50,8,IF(L3=100,9,10))), IF(K3=250,IF(L3=25,11,IF(L3=50,12,IF(L3=100,13,IF( L3=250,14,15)))),IF(K3=500,IF(L3=100,16,IF(L3=250, 17,18)),0))))))
dear god
okay, no more nested IFs in the old thread (I went through all 9 pages), but there were some truly hideous formulas in there
I got a couple articles out of that thread for the SOA, btw. Unfortunately, the SOA has been through a few web revamps since then and my backup of my articles haven’t done too well:
Hah — most of my nested ifs aren’t that confusing, they are just cases before I used index match.
add some ANDs and ORs in the mix
AND(OR(A,B),C)
1 Like
Or some min/max’s.
=min(max(A,B),C)
1 Like
That thread taught me to use (Alt-Enter) to clean up nested ifs. Basically when writing a formula, Alt-Enter creates a line break. So you can write:
IF(X,something,
If(Y, something,
If(Z, something,
otherwise whatever
))))
1 Like
Unfortunately… for some reason… (Alt-Enter) doesn’t always work for me in the latest version. And I have no idea why. It’s been driving me crazy for a year now.
Anyone know what’s going on?
Is there a difference between the alt-right and the alt-left?
:rimshot:
2 Likes
Actually, for some of us, the right-alt key does have different behavior than the left-alt key.
(On certain non-US and the US-International keyboard layouts, the right-alt key is a sort of shift key to access additional characters.)
Finally remembered to try it out…and yes, it does.
Now, if only I could get IT to upgrade Excel on my server…