Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

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

Online yitzf

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2487
  • Total likes: 4
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1050 on: September 27, 2017, 02:02:30 PM »
Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?

I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]
Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6595
  • Total likes: 100
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1051 on: September 27, 2017, 02:56:48 PM »
I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]
Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Online yitzf

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2487
  • Total likes: 4
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1052 on: September 27, 2017, 02:58:45 PM »
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6595
  • Total likes: 100
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1053 on: September 27, 2017, 03:14:28 PM »
Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.
Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1220
  • Total likes: 8
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Help and Problems
« Reply #1054 on: October 30, 2017, 09:40:21 AM »
Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc
I didn't want to use a macro on the QAT either, so what I did was add the "Paste as Value" button to the Quick access toolbar in the 1st position (needs to be added from settings).

Now I have an easy shortcut: Alt + 1 (or whatever position it is on the QAT).

Bonus is that it works great if I want to copy the value only to a new sheet.

Same goes for pretty much any function that I use often, they end up as a shortcut in the QAT, and I don't need to remember my keyboard shortcuts as excel shows you a hint every time you press just ALT.

Online aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4951
  • Total likes: 500
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1055 on: October 30, 2017, 09:56:05 AM »
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)
THat would require a place to paste to and is numerous manual steps each time. When you are dealing with a large number of sheets which have a column in the middle of many which needs to be switched it becomes a big pain.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6595
  • Total likes: 100
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1056 on: October 30, 2017, 10:03:47 AM »
THat would require a place to paste to and is numerous manual steps each time. When you are dealing with a large number of sheets which have a column in the middle of many which needs to be switched it becomes a big pain.
U paste in place

Online aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4951
  • Total likes: 500
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1057 on: October 30, 2017, 10:53:19 AM »
U paste in place
By dragging and dropping?
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6595
  • Total likes: 100
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1058 on: October 30, 2017, 10:55:57 AM »
By dragging and dropping?
Yeah drag n drop in place. Can also CTR and paste in place... takes me less than 1.5 seconds to do :)

Online aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4951
  • Total likes: 500
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1059 on: October 30, 2017, 11:36:10 AM »
Yeah drag n drop in place. Can also CTR and paste in place... takes me less than 1.5 seconds to do :)
dunno, pasting in place ids not working by me.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Online aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4951
  • Total likes: 500
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1060 on: October 30, 2017, 05:30:45 PM »
I am trying to format a column listing due dates to highlight upcoming dates using conditional formatting 3 color scale but only want it to highlight dates which are within 6 months from now. I am having a difficult time limiting it to within 6 months. I am guessing that I would enter it into the maximum field under conditional formatting but can't get it right.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Online etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8646
  • Total likes: 148
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1061 on: October 30, 2017, 05:48:26 PM »
I am trying to format a column listing due dates to highlight upcoming dates using conditional formatting 3 color scale but only want it to highlight dates which are within 6 months from now. I am having a difficult time limiting it to within 6 months. I am guessing that I would enter it into the maximum field under conditional formatting but can't get it right.
You'll need to use a formula rule.

EX: =A1<DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

Workflowy. You won't know what you're missing until you try it.

Online aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4951
  • Total likes: 500
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1062 on: October 30, 2017, 05:56:24 PM »
You'll need to use a formula rule.

EX: =A1<DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

I am trying to use scaled colors like this


But it does not allow relative references in the formulas which I was trying to put into maximum.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Online etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8646
  • Total likes: 148
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1063 on: October 30, 2017, 05:59:31 PM »
I am trying to use scaled colors like this


But it does not allow relative references in the formulas which I was trying to put into maximum.
Hmm... it looks like with color scales you can only input a starting number/formula and a percentile to switch to the middle color.

What do you want dates after six months from now to look like? No formatting?
Workflowy. You won't know what you're missing until you try it.

Online aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4951
  • Total likes: 500
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1064 on: October 30, 2017, 06:01:21 PM »
Hmm... it looks like with color scales you can only input a starting number/formula and a percentile to switch to the middle color.

What do you want dates after six months from now to look like? No formatting?
Correct no formatting. I am not sure why a maximum of =today()+183 doesn't work.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.