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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #840 on: April 25, 2017, 08:05:06 PM »
I am sure, but I am not familiar enough with VBA to wrote one that way. I have recorded and then edited it to make it more efficient such as removing selecting cells and scrolls.
That's a great way to learn VBA!
Workflowy. You won't know what you're missing until you try it.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1804
  • Total likes: 231
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #841 on: April 25, 2017, 09:02:55 PM »
Maybe it would be a good idea to have a macro that does what you want and the macro should call the print too (see here http://stackoverflow.com/a/20751096/5518385). And put this with a shortcut or a button. So it will just be a simple task to do instead of the normal way to print you'll run the macro.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #842 on: April 25, 2017, 09:08:45 PM »
Maybe it would be a good idea to have a macro that does what you want and the macro should call the print too (see here http://stackoverflow.com/a/20751096/5518385). And put this with a shortcut or a button. So it will just be a simple task to do instead of the normal way to print you'll run the macro.
I don't use the print function to make the PDF. Excel has the option to save to PDF which I added to the ribbon. My experience with such macros on sheets sent to me has been that they don't work well.
Feelings don't care about your facts

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1804
  • Total likes: 231
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #843 on: April 25, 2017, 09:13:45 PM »
I don't use the print function to make the PDF. Excel has the option to save to PDF which I added to the ribbon. My experience with such macros on sheets sent to me has been that they don't work well.
I'm not sure about your experiences, but the macro from here http://www.contextures.com/excelvbapdf.html works smooth and flawlessly for me to Save As PDF. Just add before it the other parts.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #844 on: April 25, 2017, 10:03:35 PM »
I don't use the print function to make the PDF. Excel has the option to save to PDF which I added to the ribbon. My experience with such macros on sheets sent to me has been that they don't work well.
Assuming save to PDF uses print_area, you can make that named range conditional (ie formula based).

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #845 on: April 25, 2017, 10:07:15 PM »
Assuming save to PDF uses print_area, you can make that named range conditional (ie formula based).
How?
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #846 on: April 25, 2017, 11:02:47 PM »
Maybe it would be a good idea to have a macro that does what you want and the macro should call the print too (see here http://stackoverflow.com/a/20751096/5518385). And put this with a shortcut or a button. So it will just be a simple task to do instead of the normal way to print you'll run the macro.
+1
Workflowy. You won't know what you're missing until you try it.

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 #847 on: April 25, 2017, 11:29:45 PM »
How do I make an auto completing dropdown list?

In the attached file, I'd like the "Team" column in tab "Lookup" be set that when I start typing "Ma" it starts to auto complete based on the list in tab "List"
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
-Albert Einstein

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #848 on: April 25, 2017, 11:57:01 PM »
How do I make an auto completing dropdown list?

In the attached file, I'd like the "Team" column in tab "Lookup" be set that when I start typing "Ma" it starts to auto complete based on the list in tab "List"
Select the Team column. Click Data > Data Validation. Under Allow, choose list. Click into the Source box and select your source, and click OK.

Then you can use the dropdown list.

ETA: Just saw your point about autocomplete. I don't know of a way offhand, am curious to see if anyone else does. Although once you use each choice in the column, it will offer it  to autocomplete later in the column.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #849 on: April 26, 2017, 01:22:11 AM »
How?
Go to each sheet that prints, highlight some cells, and hit Alt>P>R>C. That will create a named range for each sheet you do that on called print_range (not _area as I wrote earlier). Then you can go into the Name Manager (Alt>M>N), double click each print_range variable, and replace the direct reference with a formula. Assuming the first column is indicative of which rows need to be included, I would suggest =OFFSET($A$1,0,0,COUNTA($A:$A),x), where x is the number of columns you want included. If that needs to be variable as well, you can tweak it too.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #850 on: April 26, 2017, 09:43:49 AM »
How do I make an auto completing dropdown list?

In the attached file, I'd like the "Team" column in tab "Lookup" be set that when I start typing "Ma" it starts to auto complete based on the list in tab "List"
http://www.ozgrid.com/Excel/autocomplete-validation.htm
http://stackoverflow.com/questions/7989763/excel-2010-how-to-use-autocomplete-in-validation-list
Feelings don't care about your facts

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #851 on: April 26, 2017, 09:59:31 AM »
Go to each sheet that prints, highlight some cells, and hit Alt>P>R>C. That will create a named range for each sheet you do that on called print_range (not _area as I wrote earlier). Then you can go into the Name Manager (Alt>M>N), double click each print_range variable, and replace the direct reference with a formula. Assuming the first column is indicative of which rows need to be included, I would suggest =OFFSET($A$1,0,0,COUNTA($A:$A),x), where x is the number of columns you want included. If that needs to be variable as well, you can tweak it too.
It is still including all cells with blank references.
Feelings don't care about your facts

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #852 on: April 26, 2017, 10:18:07 AM »
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.
Feelings don't care about your facts

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 #853 on: April 26, 2017, 10:40:45 AM »
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.
That's the reason I had to make a trigger script on my sheet
I don't sin, I give myself opportunities to repent.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #854 on: April 26, 2017, 10:42:18 AM »
That's the reason I had to make a trigger script on my sheet
Meaning using google sheets instead of excel?
Feelings don't care about your facts

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.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • 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.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • 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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • 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.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • 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