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 287153 times)

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1080 on: October 30, 2017, 08:50:24 PM »
Days works without a function, and years you'd get by just multiplying the number by 12 and entering it as that number of months.
How would you do days without a function?
Feelings don't care about your facts

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1081 on: October 30, 2017, 08:53:02 PM »
Days works without a function, and years you'd get by just multiplying the number by 12 and entering it as that number of months.
Cool!
Workflowy. You won't know what you're missing until you try it.

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1082 on: October 30, 2017, 10:57:26 PM »
How would you do days without a function?
EDATE takes a date, and moves forward or backward x number of months from a specific date (keeping the day of the month static). If you want to take a date and move forward or backward x number of days, just add or subtract that number.

If A1 contains 11/1/17, putting =A1+10 into another cell would give you 11/11/17.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1083 on: October 30, 2017, 11:04:17 PM »
EDATE takes a date, and moves forward or backward x number of months from a specific date (keeping the day of the month static). If you want to take a date and move forward or backward x number of days, just add or subtract that number.

If A1 contains 11/1/17, putting =A1+10 into another cell would give you 11/11/17.
Oh so you meant that you still need the TODAY. That is what I did.
Feelings don't care about your facts

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1084 on: November 07, 2017, 07:55:25 PM »
I recorded a macro to do all of these. Now I need to get rid of the extraneous parts, but it works if my personal.xlsb is open, but it seems to not always open with excel.

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?
Text to columns, (option in data ribbon ) but don't select anything ( tab, space etc) as the deliminator, so it all stays in same column.
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.
Thanks for your help!

One thing I realized was that the minimum value is important as well because without it if there are a few overdue items than some will have scaled down color even though they are overdue. With a minimum of today they will all be max color (I did today+35)





Here is the macro as it is now.

Code: [Select]
    Columns("G:G").Select
       Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.NumberFormat = "m/d/yyyy"
       
       
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(1).Value = "=TODAY()+30"
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = "=TODAY()+95"
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(3).Value = "=TODAY()+190"
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 5296274
        .TintAndShade = 0
    End With
    Columns("G:G").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=G1>DATE(YEAR(TODAY()),MONTH(TODAY())+10,DAY(TODAY()))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub
Feelings don't care about your facts

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1085 on: November 07, 2017, 08:02:56 PM »


I recorded a macro to do all of these. Now I need to get rid of the extraneous parts, but it works if my personal.xlsb is open, but it seems to not always open with excel.

Here is the macro as it is now.

Code: [Select]
    Columns("G:G").Select
       Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.NumberFormat = "m/d/yyyy"
       
       
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(1).Value = "=TODAY()+30"
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = "=TODAY()+95"
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(3).Value = "=TODAY()+190"
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 5296274
        .TintAndShade = 0
    End With
    Columns("G:G").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=G1>DATE(YEAR(TODAY()),MONTH(TODAY())+10,DAY(TODAY()))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub

It always should open in the background in Excel. It's not there when you select unhide?

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1086 on: November 07, 2017, 08:06:26 PM »

It always should open in the background in Excel. It's not there when you select unhide?
nope. Unhide is greyed out.
Feelings don't care about your facts

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Feelings don't care about your facts

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1089 on: November 07, 2017, 08:34:43 PM »
In the end this is what worked
https://answers.microsoft.com/en-us/office/forum/office_2007-excel/1-personalxlsb-will-not-load-automatically-2-the/97dd3d4a-7176-e011-8dfc-68b599b31bf5

To (1):

- Remove anything from the XLSTART folder
- Open Excel
- Start recording a macro and set "Personal Macro Workbook" as destination.
- Select some cells (we must record something!)
- Stop recording (the file PERSONAL.XLSB is created)
- Open your old PERSONL.XLS
- Open the VBA editor
- Move the modules with Drag&Drop to the PERSONAL.XLSB file
- Save the PERSONAL.XLSB file.
Feelings don't care about your facts

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1090 on: November 07, 2017, 10:26:00 PM »
In the end this is what worked
https://answers.microsoft.com/en-us/office/forum/office_2007-excel/1-personalxlsb-will-not-load-automatically-2-the/97dd3d4a-7176-e011-8dfc-68b599b31bf5

To (1):

- Remove anything from the XLSTART folder
- Open Excel
- Start recording a macro and set "Personal Macro Workbook" as destination.
- Select some cells (we must record something!)
- Stop recording (the file PERSONAL.XLSB is created)
- Open your old PERSONL.XLS
- Open the VBA editor
- Move the modules with Drag&Drop to the PERSONAL.XLSB file
- Save the PERSONAL.XLSB file.
This is what I meant btw with macros messing everything up and not being worth the time and effort most times.

I almost never had a good long lasting experience with my self recorded/edited macros saved in personal...

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1091 on: November 07, 2017, 10:27:24 PM »
This is what I meant btw with macros messing everything up and not being worth the time and effort most times.

I almost never had a good long lasting experience with my self recorded/edited macros saved in personal...
I just saved a backup of this one right here in this thread.
Feelings don't care about your facts

Offline chayal101

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2013
  • Posts: 917
  • Total likes: 137
  • DansDeals.com Hat Tips 2
  • Gender: Female
    • View Profile
Re: Excel Help and Problems
« Reply #1092 on: November 23, 2017, 01:57:30 PM »
I'm trying to filter a long list of Linked in connection to only ROWS (contacts) that include certain words (professions), F.E. Attorneys, Real Estate.. Anyone know an easy way to do that?
Chaya L.

Offline Z56

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2014
  • Posts: 2420
  • Total likes: 100
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: NYC, TLV
  • Programs: SPG Gold, Marriot Gold, Hilton Gold, National EE, Hertz GP, Avis PP
Re: Excel Help and Problems
« Reply #1093 on: November 23, 2017, 01:59:30 PM »
I'm trying to filter a long list of Linked in connection to only ROWS (contacts) that include certain words (professions), F.E. Attorneys, Real Estate.. Anyone know an easy way to do that?

is everything in one line ?
“I have nothing in common with lazy people who blame others for their lack of success. Great things come from hard work

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1094 on: November 23, 2017, 02:01:28 PM »
I'm trying to filter a long list of Linked in connection to only ROWS (contacts) that include certain words (professions), F.E. Attorneys, Real Estate.. Anyone know an easy way to do that?
If all those values would be in the same column, you can accomiplish this very easily by enabling AutoFilter (on the home ribbon, click Sort & Filter and then click Filter). Then click the arrow at the top of that column and check off the values you want to keep.
Workflowy. You won't know what you're missing until you try it.

Offline chayal101

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2013
  • Posts: 917
  • Total likes: 137
  • DansDeals.com Hat Tips 2
  • Gender: Female
    • View Profile
Re: Excel Help and Problems
« Reply #1095 on: November 23, 2017, 02:19:59 PM »
I actually want to search all columns that contain specific words, such as Real Estate, Attorneys, Acquisitions, etc... Any "IF" formula I can use?
Chaya L.

Offline Z56

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2014
  • Posts: 2420
  • Total likes: 100
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: NYC, TLV
  • Programs: SPG Gold, Marriot Gold, Hilton Gold, National EE, Hertz GP, Avis PP
Re: Excel Help and Problems
« Reply #1096 on: November 23, 2017, 04:05:23 PM »
I actually want to search all columns that contain specific words, such as Real Estate, Attorneys, Acquisitions, etc... Any "IF" formula I can use?

so use the filter ?

or are looking to create a formula ?
“I have nothing in common with lazy people who blame others for their lack of success. Great things come from hard work

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2623
  • Total likes: 52
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1097 on: November 23, 2017, 08:38:30 PM »
I actually want to search all columns that contain specific words, such as Real Estate, Attorneys, Acquisitions, etc... Any "IF" formula I can use?

Create a new column that strings together all the columns that might have relevant keywords (=A1&" "&B1&" "&C1). Then create a list of keyword elsewhere. Then select the column with the merged strings and goto Data>Advanced and for the criteria range but in the section with your list of keywords.


Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1098 on: November 26, 2017, 12:47:57 PM »
I think theis was discussed here previously, but I couldn't find it. I have a list with one column of addresses and multiple utility accounts in the columns to the side of the addresses. I need to change this to have a separate row for each account. Is there any automated way to do this?
Feelings don't care about your facts

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1099 on: November 26, 2017, 01:07:16 PM »
I think theis was discussed here previously, but I couldn't find it. I have a list with one column of addresses and multiple utility accounts in the columns to the side of the addresses. I need to change this to have a separate row for each account. Is there any automated way to do this?
You can definitely do it with VBA. Question is if there's an easier way.

Two questions:

What's the max number of accounts per address?
Does the sort order matter?
Workflowy. You won't know what you're missing until you try it.