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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3379
  • Total likes: 555
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #860 on: April 26, 2017, 04:08:16 PM »
It is still including all cells with blank references.
Got it to work. I forgot that COUNTA looks for non-empty, as opposed to non-blank.

You can use this instead:
=OFFSET(LOE!$A$1,0,0,140-COUNTBLANK(LOE!$A$21:$A$140),10)
[I also adjusted it to account for the blank cells in the first 20 rows.]

The second thing, and this might be a dealbreaker, is that it requires that you keep the LOE tab in Normal view, not Page Layout or Page Break view [ETA: Scratch that, Page Break seems to work too. Apparently it's just Page Layout view that's a problem]. Switch this before entering your new formula for Print_Area, otherwise the formula will get converted to a non-dynamic reference.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #861 on: April 26, 2017, 05:05:28 PM »
Got it to work. I forgot that COUNTA looks for non-empty, as opposed to non-blank.

You can use this instead:
=OFFSET(LOE!$A$1,0,0,140-COUNTBLANK(LOE!$A$21:$A$140),10)
[I also adjusted it to account for the blank cells in the first 20 rows.]

The second thing, and this might be a dealbreaker, is that it requires that you keep the LOE tab in Normal view, not Page Layout or Page Break view [ETA: Scratch that, Page Break seems to work too. Apparently it's just Page Layout view that's a problem]. Switch this before entering your new formula for Print_Area, otherwise the formula will get converted to a non-dynamic reference.
This works great!

BTW, it seems that if it is switched to page layout view after creating the print area it will show everything in excel but when printing or saving to a PDF it will still only include the print area.
Feelings don't care about your facts

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12206
  • Total likes: 2212
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #862 on: April 26, 2017, 05:10:27 PM »
Workflowy. You won't know what you're missing until you try it.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #863 on: April 26, 2017, 05:14:13 PM »
Awesome!
Now I need to see if I can put together a godd macro for the more extensive spreadsheet which we use within the office.
Feelings don't care about your facts

Offline dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1183
  • Total likes: 59
  • DansDeals.com Hat Tips 23
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #864 on: April 26, 2017, 05:52:04 PM »
http://www.ozgrid.com/Excel/autocomplete-validation.htm
http://stackoverflow.com/questions/7989763/excel-2010-how-to-use-autocomplete-in-validation-list
I had gotten that, although I was screwing something up that I've since fixed.

Is it possible to have it not auto complete, but instead show me list of names matching what I've already typed. Meaning, if I have 5 names that all start with Mar, can I have it show me those and as I keep typing, it narrows it down to the ones that still fit, then once there's only one, have it auto complete?
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
-Albert Einstein

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #865 on: April 26, 2017, 06:09:13 PM »
I had gotten that, although I was screwing something up that I've since fixed.

Is it possible to have it not auto complete, but instead show me list of names matching what I've already typed. Meaning, if I have 5 names that all start with Mar, can I have it show me those and as I keep typing, it narrows it down to the ones that still fit, then once there's only one, have it auto complete?
With VBA I think you can
Feelings don't care about your facts

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #866 on: April 26, 2017, 07:31:46 PM »
Got it to work. I forgot that COUNTA looks for non-empty, as opposed to non-blank.

You can use this instead:
=OFFSET(LOE!$A$1,0,0,140-COUNTBLANK(LOE!$A$21:$A$140),10)
[I also adjusted it to account for the blank cells in the first 20 rows.]

The second thing, and this might be a dealbreaker, is that it requires that you keep the LOE tab in Normal view, not Page Layout or Page Break view [ETA: Scratch that, Page Break seems to work too. Apparently it's just Page Layout view that's a problem]. Switch this before entering your new formula for Print_Area, otherwise the formula will get converted to a non-dynamic reference.
This works great!

BTW, it seems that if it is switched to page layout view after creating the print area it will show everything in excel but when printing or saving to a PDF it will still only include the print area.
Upon further review it did not work on page view because when adding it did not add to the printable area as Skyguy918 wrote that it is not dynamic.
THat leads me to the question if there is a way to have a letterhead show. I now have it in the header but if it is better some other way that is also fine.
Feelings don't care about your facts

Offline dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1183
  • Total likes: 59
  • DansDeals.com Hat Tips 23
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #867 on: April 26, 2017, 07:35:59 PM »
With VBA I think you can
I was hoping to avoid VBA because then all the workbooks I'll be adding this formula to will need to be macro enabled.
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
-Albert Einstein

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #868 on: April 26, 2017, 07:37:13 PM »
I was hoping to avoid VBA because then all the workbooks I'll be adding this formula to will need to be macro enabled.
or
So you can store the macro in Personal.xlsb and then the files they use won't have to include macros. Assuming that you're the only one who will want to run it.
Feelings don't care about your facts

Offline dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1183
  • Total likes: 59
  • DansDeals.com Hat Tips 23
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #869 on: April 26, 2017, 07:38:57 PM »
or
It's something everyone in the office uses everyday. It's how we do billing and the list I want is the billing code list.
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
-Albert Einstein

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12206
  • Total likes: 2212
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #870 on: April 26, 2017, 07:41:31 PM »
It's something everyone in the office uses everyday. It's how we do billing and the list I want is the billing code list.
I may be wrong, but I think there's a way to add macros to a workbook on a server (I'm assuming you have one), and then set it up so everyone in the office can access those macros when they open Excel. Maybe as an add-in or something?
Workflowy. You won't know what you're missing until you try it.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2657
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #871 on: April 26, 2017, 10:22:14 PM »
I may be wrong, but I think there's a way to add macros to a workbook on a server (I'm assuming you have one), and then set it up so everyone in the office can access those macros when they open Excel. Maybe as an add-in or something?

There is but you still need to enable macros in the workbook you are using.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #872 on: April 26, 2017, 10:24:28 PM »
There is but you still need to enable macros in the workbook you are using.
But does it need to be saved as macro enabled?
Feelings don't care about your facts

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2657
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #873 on: April 26, 2017, 10:35:28 PM »
But does it need to be saved as macro enabled?

It can be .xlsx.

You don't get the typical yellow bar to enable macros rather you get a warning so I wonder if you can trust the location an not get the warning.
« Last Edit: April 26, 2017, 10:38:39 PM by yitzf »

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12206
  • Total likes: 2212
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #874 on: April 26, 2017, 11:32:33 PM »
You don't get the typical yellow bar to enable macros rather you get a warning so I wonder if you can trust the location an not get the warning.
I'm pretty sure there's a way
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: 289
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #875 on: April 27, 2017, 08:13:30 AM »
@aygart, haven't seen your spreadsheet or been following, but I would think this is something for a billing/quote software instead of excel... Especially with multiple reps etc

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13368
  • Total likes: 8165
  • DansDeals.com Hat Tips 13
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #876 on: April 27, 2017, 10:21:50 AM »
It can be .xlsx.

You don't get the typical yellow bar to enable macros rather you get a warning so I wonder if you can trust the location an not get the warning.
Is this the entire issue?
Feelings don't care about your facts

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3379
  • Total likes: 555
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #877 on: April 27, 2017, 10:29:38 AM »
Upon further review it did not work on page view because when adding it did not add to the printable area as Skyguy918 wrote that it is not dynamic.
THat leads me to the question if there is a way to have a letterhead show. I now have it in the header but if it is better some other way that is also fine.
It shows when it prints, right? You're just asking how to get it to show on the screen as well?

Offline dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1183
  • Total likes: 59
  • DansDeals.com Hat Tips 23
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #878 on: April 27, 2017, 10:34:11 AM »
With VBA I think you can
I'm not well versed in VBA, any idea how? I found a video on youtube, but that has what seem like unnecessary steps, like having to double click a cell to prompt a combo box to appear. Is there a simpler way to do it?

@aygart, haven't seen your spreadsheet or been following, but I would think this is something for a billing/quote software instead of excel... Especially with multiple reps etc
We don't actually use it for billing, its more for keeping each employee's time, and it works pretty well for us, this would just make it easier for those of us who are lazy and or can't spell well (data validation would solve the spelling issues because it wont let you leave a misspelled name in the cell).
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
-Albert Einstein

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2657
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #879 on: April 27, 2017, 10:36:24 AM »


Is this the entire issue?

Well you also have to set up the server and a button on the user file that links to the server.
The add-in is on the server and when you assign a macro to the button you put in a link to the add-in (location/filename.xla!macroname)