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

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 895
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #855 on: April 26, 2017, 10:50:57 AM »
Meaning using google sheets instead of excel?
I'm using Google sheets anyways, but I want to be able to submit data input also from the Google sheets app
I don't sin, I give myself opportunities to repent.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11929
  • Total likes: 1961
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #856 on: April 26, 2017, 03:03:17 PM »
An additional issue with using a macro is that some of my reps use the sheet on mobile devices. I striped down the sheet and attached it. They fill the info on the CustInfo tab and it fills on the LOE tab which i also export to a PDF.
How do they get the file to you? Do they email it?
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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #857 on: April 26, 2017, 03:04:18 PM »
How do they get the file to you? Do they email it?
yup
Feelings don't care about your facts

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11929
  • Total likes: 1961
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #858 on: April 26, 2017, 03:06:15 PM »
yup
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.
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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #859 on: April 26, 2017, 03:58:53 PM »
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.
I am not the only one who would run it but if there is no other option then I would just leave the reps with it as is.
Feelings don't care about your facts

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3230
  • Total likes: 440
  • 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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • 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: 11929
  • Total likes: 1961
  • 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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • 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

Online dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1181
  • Total likes: 58
  • 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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • 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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • 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

Online dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1181
  • Total likes: 58
  • 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: 12071
  • Total likes: 6470
  • DansDeals.com Hat Tips 8
    • 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

Online dpk4588

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2012
  • Posts: 1181
  • Total likes: 58
  • 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