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

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #560 on: March 02, 2016, 05:44:57 PM »
Nah, just did it manualy-  took 5 min
Gotcha. Yeah if it's a one-time thing, it's not such a big deal
Workflowy. You won't know what you're missing until you try it.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #561 on: March 02, 2016, 05:54:55 PM »
Gotcha. Yeah if it's a one-time thing, it's not such a big deal
Yeah. I just thought there might be some cool offset formula..

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #562 on: March 02, 2016, 06:06:09 PM »
Haven't tried it but http://www.mcgimpsey.com/excel/udfs/prevsheet.html
Paste into VBA module, then you have a function called prevsheet.
Type =prevsheet(X20)

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #563 on: March 02, 2016, 06:10:41 PM »
How about this?
=OFFSET(Sheet1!A1,10,0)
It will go to Sheet!A1, and then jump down 10 rows and across 0 columns, and return what it finds.
Workflowy. You won't know what you're missing until you try it.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #564 on: March 05, 2016, 09:29:08 PM »
A clients excel workbook default format for all cells is TIME!

This means that every time I refresh a pivot table that I am working on it defaults to time. - How do I change this?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #565 on: March 05, 2016, 10:13:59 PM »
A clients excel workbook default format for all cells is TIME!

This means that every time I refresh a pivot table that I am working on it defaults to time. - How do I change this?
Which version of excel are you in? My recollection is that there's a Cell Style called Normal, and you have to modify it to change the default.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #566 on: March 05, 2016, 10:20:03 PM »
Which version of excel are you in? My recollection is that there's a Cell Style called Normal, and you have to modify it to change the default.
2013 .
This is only for these workbooks. Rest of my excel is fine.
So I don't want to mess w anything that will affect everything..

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #567 on: March 05, 2016, 10:26:19 PM »
2013 .
This is only for these workbooks. Rest of my excel is fine.
So I don't want to mess w anything that will affect everything..
Lol, so make a copy and test it out.

Offline Mordyk

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2013
  • Posts: 3728
  • Total likes: 681
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Programs: Some of this and some of that.
Re: Excel Problem
« Reply #568 on: March 05, 2016, 10:51:47 PM »
Lol, so make a copy and test it out.
+1  i always make a copy and experiment. i have learned a lot like this
2013 .
This is only for these workbooks. Rest of my excel is fine.
So I don't want to mess w anything that will affect everything..
i believe  that if some cells are time and others are not then you must select only the cells that are time and change them to normal or text. i have had this issue in the past. or try conditional formatting clear rules
#TYH

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #569 on: March 06, 2016, 12:12:36 AM »
Lol, so make a copy and test it out.
Huh? I don't want to make any changes to the default excel template.
I want to change the default settings just for this workbook.

I obviously tried the normal process of ctr a and setting my preferred format.
Problem is that it keeps switching back to time format when ever I refresh pivits etc.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #570 on: March 06, 2016, 12:14:16 AM »
+1  i always make a copy and experiment. i have learned a lot like thisi believe  that if some cells are time and others are not then you must select only the cells that are time and change them to normal or text. i have had this issue in the past. or try conditional formatting clear rules
You're probably right. I think it may be a conditional format set on entire workbook. Il check that when I log back in.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #571 on: March 06, 2016, 06:54:35 PM »
You're probably right. I think it may be a conditional format set on entire workbook. Il check that when I log back in.
Turns out there's like a million styles in these workbooks.. Oh well, il manage.
 

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #572 on: March 06, 2016, 08:08:54 PM »
Turns out there's like a million styles in these workbooks.. Oh well, il manage.
I think it's just that one in the bottom left corner of the screenshot that matters.

I had a similar issue to your's once, except everything was formatted in euros. I ended up finding a script to essentially clear out all the styles and rebuild the standard style database - like this (found [ur=http://www.mrexcel.com/forum/excel-questions/390298-cell-styles-gone-wild-how-do-i-delete-them.htmll]here[/url]):
Code: [Select]
Sub RebuildDefaultStyles()

'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles.
'It rebuilds the default styles by merging them from a new workbook.

'Dimension variables.
   Dim MyBook As Workbook
   Dim tempBook As Workbook
   Dim CurStyle As Style

   'Set MyBook to the active workbook.
   Set MyBook = ActiveWorkbook
   On Error Resume Next
   'Delete all the styles in the workbook.
   For Each CurStyle In MyBook.Styles
      'If CurStyle.Name <> "Normal" Then CurStyle.Delete
      Select Case CurStyle.Name
         Case "20% - Accent1", "20% - Accent2", _
               "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
               "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
               "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
               "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
               "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
               "Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
               "Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
               "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
               "Note", "Output", "Percent", "Title", "Total", "Warning Text"
            'Do nothing, these are the default styles
         Case Else
            CurStyle.Delete
      End Select

   Next CurStyle

   'Open a new workbook.
   Set tempBook = Workbooks.Add

   'Disable alerts so you may merge changes to the Normal style
   'from the new workbook.
   Application.DisplayAlerts = False

   'Merge styles from the new workbook into the existing workbook.
   MyBook.Styles.Merge Workbook:=tempBook

   'Enable alerts.
   Application.DisplayAlerts = True

   'Close the new workbook.
   tempBook.Close

End Sub

ETA: This will only work if new workbooks open fine - with no style/format issues.
« Last Edit: March 06, 2016, 08:11:58 PM by skyguy918 »

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #573 on: March 06, 2016, 09:09:08 PM »
Is there a simple formula for cell A1 to equal cell A1 on the previous sheet (tab)?

Code: [Select]
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"") Will give you the current sheet name. If your sheets are named numerically you might be in luck.

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #574 on: March 09, 2016, 12:38:39 AM »
I have number 1-99 in a column and trying to sort by value, however it's not recognizing double digits as higher value (eg. 55 is going next to 5, 12 is all the way at bottom by 1) how do I need to format this column so it should work properly?

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12697
  • Total likes: 7377
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #575 on: March 09, 2016, 12:41:01 AM »
I have number 1-99 in a column and trying to sort by value, however it's not recognizing double digits as higher value (eg. 55 is going next to 5, 12 is all the way at bottom by 1) how do I need to format this column so it should work properly?
It is probably formatted as text not a number.
Feelings don't care about your facts

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #576 on: March 09, 2016, 12:42:55 AM »
It is probably formatted as text not a number.
I changed it to number already but still not working

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12697
  • Total likes: 7377
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #577 on: March 09, 2016, 12:48:33 AM »
I changed it to number already but still not working
I've sometimes found that to change the format of multiple cells you need to change the format and then go through them one by one and place the cursor in the edit box and hit enter
Feelings don't care about your facts

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #578 on: March 09, 2016, 12:53:11 AM »
I've sometimes found that to change the format of multiple cells you need to change the format and then go through them one by one and place the cursor in the edit box and hit enter
when i do that the number does indeed flip to the right side of the box if that means anything, but I have hundreds of rows....

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #579 on: March 09, 2016, 01:06:51 AM »
I've sometimes found that to change the format of multiple cells you need to change the format and then go through them one by one and place the cursor in the edit box and hit enter
the fix:
Set the column to desired format and then select all the cells in that column. Go to Data-->Text To Columns and press finish. That should get them all in one swoop.