Excel Help and Problems

[mancunian]

=IF(TODAY()>=DATE(YEAR(TODAY()),10,25),DATE(YEAR(TODAY()),12,31),DATE(YEAR(TODAY()-1),12,31))

Or just

=DATE(IF(TODAY()>=DATE(YEAR(TODAY()),10,25),YEAR(TODAY()),YEAR(TODAY()-1)),12,31)

[skyguy918]

=IF(TODAY()>=DATE(YEAR(TODAY()),10,25),DATE(YEAR(TODAY()),12,31),DATE(YEAR(TODAY()-1),12,31))

Or just

=DATE(IF(TODAY()>=DATE(YEAR(TODAY()),10,25),YEAR(TODAY()),YEAR(TODAY()-1)),12,31)

This only saves 1 character, but it's a cool trick to know. Month 13, day 0 is also 12/31. So DATE(xx,13,0) is 12/31/xx.

[mancunian]

Saves 2 characters, don't have to write '0'

=DATE(IF(TODAY()>=DATE(YEAR(TODAY()),10,25),YEAR(TODAY()),YEAR(TODAY()-1)),13,)

[etech0]

PSA: vlookup is NOT case sensitive. Please don't learn the hard way like I almost did...

[skyguy918]

PSA: vlookup is NOT case sensitive. Please don't learn the hard way like I almost did...

Yet another reason to always use INDEX/MATCH. I've literally never come across a situation where it made sense to use any of the LOOKUP functions.

[etech0]

Yet another reason to always use INDEX/MATCH. I've literally never come across a situation where it made sense to use any of the LOOKUP functions.

Is Index/Match always going to be an array function? IME those are much slower.

[skyguy918]

Is Index/Match always going to be an array function? IME those are much slower.

Nope. INDEX/MATCH is always at least as fast as the LOOKUP functions, and often much faster. Here's one of many articles google will find you on the topic:

http://exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm

[lubaby]

Yet another reason to always use INDEX/MATCH. I've literally never come across a situation where it made sense to use any of the LOOKUP functions.

CMIIW, but INDEX/MATCH isn't either Case Sensitive.

[skyguy918]

CMIIW, but INDEX/MATCH isn't either Case Sensitive.

Correct, you need to add EXACT in there. The same is true for the LOOKUPS, but that combo will be much slower than it will be with INDEX/MATCH.

ETA: Here is the syntax - https://support.microsoft.com/en-us/kb/214264

[mancunian]

Lookups are used when one array is a variable. If you're looking for a column relative to a reference.

Offhand I can say I've used it when creating permutations.

[mancunian]

Is there any way to make the array in a MATCH function a variable based on a reference? Or something else to accomplish the same
I know I can use INDIRECT but there's got to be a way to use a reference number of column within array (as is done in VLOOKUP for index column, I want to do for lookup column)...

So instead of:
=INDEX($A:$A,MATCH($B1,$C:$C,0))
I want:
=INDEX($A:$A,MATCH($B1,[Lookup Array Column Number],0))

Edit:
This is the INDIRECT. How long do you wait when excel crashes BTW?
=INDEX('[102615.xlsx]FBA1'!$C:$C,MATCH(ROW()-12,INDIRECT("'[102615.xlsx]FBA1'!$"&CHAR(MATCH(LEFT(RIGHT(B$1,6),5),'[102615.xlsx]FBA1'!$N$1:$U$1,0)+77)&":$"&CHAR(MATCH(LEFT(RIGHT(B$1,6),5),'[102615.xlsx]FBA1'!$N$1:$U$1,0)+77)),0))

[skyguy918]

Is there any way to make the array in a MATCH function a variable based on a reference? Or something else to accomplish the same
I know I can use INDIRECT but there's got to be a way to use a reference number of column within array (as is done in VLOOKUP for index column, I want to do for lookup column)...

So instead of:
=INDEX($A:$A,MATCH($B1,$C:$C,0))
I want:
=INDEX($A:$A,MATCH($B1,[Lookup Array Column Number],0))

Edit:
This is the INDIRECT. How long do you wait when excel crashes BTW?
=INDEX('[102615.xlsx]FBA1'!$C:$C,MATCH(ROW()-12,INDIRECT("'[102615.xlsx]FBA1'!$"&CHAR(MATCH(LEFT(RIGHT(B$1,6),5),'[102615.xlsx]FBA1'!$N$1:$U$1,0)+77)&":$"&CHAR(MATCH(LEFT(RIGHT(B$1,6),5),'[102615.xlsx]FBA1'!$N$1:$U$1,0)+77)),0))

If you have to read inside the text of another cell just to figure out which array you want your MATCH to look in, you basically need INDIRECT. What's the issue with the formula you created? It doesn't work?

Also, you're probably better off moving the INDIRECT function into a named range.

[mancunian]

My INDIRECT is actually working like a charm now!
The first time it crashed my excel, but once I restarted its ok.
I never use named ranges. Don't know why but seems to me the same as hard coding variables.

Thanks

[skyguy918]

My INDIRECT is actually working like a charm now!
The first time it crashed my excel, but once I restarted its ok.
I never use named ranges. Don't know why but seems to me the same as hard coding variables.

Thanks

It's very much not the same as hardcoding variables, and if you're using this function more than once, it'll save time in Excel because that value is already calculated. I'm not 100% sure that will hold true for INDIRECT, which is a volatile function, but there are many, many instances where named ranges add calculation speed, clarity of understanding, coding speed, etc.

[etech0]

every so often when I move excel from my main (laptop) screen to my external monitor, I have an issue. Excel itself will be full screen, but the spreadsheet (and any that I open) only take up a portion of the screen.

I tried dragging the corners to make the spreadsheet take up more screen, but they won't drag

Any ideas how to keep this from happening? Closing and reopening excel usually fixes it, but it's a pain to reopen all my files.

Screenshot of the issue: (it's large so I didn't embed it)
http://i.imgur.com/82XAfeg.png

Thanks!

PS: I don't remember this happening before I got Windows 10. Don't know if it's related, though.

[skyguy918]

every so often when I move excel from my main (laptop) screen to my external monitor, I have an issue. Excel itself will be full screen, but the spreadsheet (and any that I open) only take up a portion of the screen.

I tried dragging the corners to make the spreadsheet take up more screen, but they won't drag

Any ideas how to keep this from happening? Closing and reopening excel usually fixes it, but it's a pain to reopen all my files.

Screenshot of the issue: (it's large so I didn't embed it)
http://i.imgur.com/82XAfeg.png

Thanks!

PS: I don't remember this happening before I got Windows 10. Don't know if it's related, though.

Did you try maximizing (Ctrl+F10)?

[tageed-lee]

I tried dragging the corners to make the spreadsheet take up more screen, but they won't drag

Any ideas how to keep this from happening?

Did you try pressing this?

[skyguy918]

Did you try pressing this?

You can see from the picture that those buttons are hidden under the formula bar. It becomes difficult or impossible to move the window to get at them. That's why I suggested the shortcut for maximize current workbook.

[tageed-lee]

You can see from the picture that those buttons are hidden under the formula bar. It becomes difficult or impossible to move the window to get at them. That's why I suggested the shortcut for maximize current workbook.

Ah yes.. now i see... Ya Ctrl+F10 should help...

[skyguy918]

You can see from the picture that those buttons are hidden under the formula bar. It becomes difficult or impossible to move the window to get at them. That's why I suggested the shortcut for maximize current workbook.

Actually, I just tested this out too. If you put your cursor along the sides, you get the horizontal arrows. But if you move as far to the top as possible, you should get the diagonal arrows just before you cross over onto the formula bar. Then you can click and drag downward to bring the maximize/minimize/etc. buttons back into view.