Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

Author Topic: Excel Help and Problems  (Read 298333 times)

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #360 on: October 23, 2015, 11:19:32 AM »
=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)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #361 on: October 23, 2015, 11:42:28 AM »
=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.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #362 on: October 23, 2015, 11:44:27 AM »
Saves 2 characters, don't have to write '0'

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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #363 on: October 26, 2015, 12:23:49 PM »
PSA: vlookup is NOT case sensitive. Please don't learn the hard way like I almost did...
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #364 on: October 26, 2015, 12:53:42 PM »
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.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #365 on: October 26, 2015, 01:00:10 PM »
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.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #366 on: October 26, 2015, 01:42:58 PM »
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

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #367 on: October 26, 2015, 01:48:54 PM »
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.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #368 on: October 26, 2015, 01:52:39 PM »
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

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #369 on: October 26, 2015, 02:37:32 PM »
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.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #370 on: October 28, 2015, 12:00:33 PM »
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))
« Last Edit: October 28, 2015, 12:05:45 PM by mancunian »

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #371 on: October 28, 2015, 12:22:59 PM »
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.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #372 on: October 28, 2015, 12:27:42 PM »
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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #373 on: October 28, 2015, 12:48:32 PM »
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.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #374 on: October 29, 2015, 01:57:22 PM »
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.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #375 on: October 29, 2015, 02:09:26 PM »
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)?

Offline tageed-lee

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2012
  • Posts: 1589
  • Total likes: 129
  • DansDeals.com Hat Tips 73
    • View Profile
Re: Excel Problem
« Reply #376 on: October 29, 2015, 02:15:18 PM »
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?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #377 on: October 29, 2015, 02:19:40 PM »
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.

Offline tageed-lee

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2012
  • Posts: 1589
  • Total likes: 129
  • DansDeals.com Hat Tips 73
    • View Profile
Re: Excel Problem
« Reply #378 on: October 29, 2015, 02:20:57 PM »
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...

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #379 on: October 29, 2015, 02:28:26 PM »
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.