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

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12216
  • Total likes: 6654
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #825 on: April 25, 2017, 05:55:09 PM »
I have a spreadsheet with multiple tabs. One is a "working tab" to enter relevant information and the other tabs cross reference from there. One of the other tabs is used to create an authorization form from the information entered by exporting it to a pdf. Currently I have all of the cells cross referencing using the formula =IF(CustInfo!B21=0,"",CustInfo!B21) so that cells with no entered information do not show on the form as zeros. The issue with cross referencing in this way is that the pdf always exports with 5 sheets even if only the first has any information on it since the cells all have content even if that content is blank. Is there any way to have the sheet automatically reference the correct amount of rows so that it will have all of the entered info but no blank references?
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11992
  • Total likes: 2017
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #826 on: April 25, 2017, 06:15:54 PM »
I have a spreadsheet with multiple tabs. One is a "working tab" to enter relevant information and the other tabs cross reference from there. One of the other tabs is used to create an authorization form from the information entered by exporting it to a pdf. Currently I have all of the cells cross referencing using the formula =IF(CustInfo!B21=0,"",CustInfo!B21) so that cells with no entered information do not show on the form as zeros. The issue with cross referencing in this way is that the pdf always exports with 5 sheets even if only the first has any information on it since the cells all have content even if that content is blank. Is there any way to have the sheet automatically reference the correct amount of rows so that it will have all of the entered info but no blank references?
What do you mean by "correct amount of rows"?

Is there any way for you to post a sample file?
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: 12216
  • Total likes: 6654
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #827 on: April 25, 2017, 06:19:24 PM »
What do you mean by "correct amount of rows"?

Is there any way for you to post a sample file?
it is basically a list of the prospect's locations. Some have 1 location and some have a few hundred. each row is one location. If I use the formula above it has all of the rows on the authorization form containing blank content even if only one row is really needed. I will try to fix up a sheet to post.
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 #828 on: April 25, 2017, 06:19:51 PM »
I have a spreadsheet with multiple tabs. One is a "working tab" to enter relevant information and the other tabs cross reference from there. One of the other tabs is used to create an authorization form from the information entered by exporting it to a pdf. Currently I have all of the cells cross referencing using the formula =IF(CustInfo!B21=0,"",CustInfo!B21) so that cells with no entered information do not show on the form as zeros. The issue with cross referencing in this way is that the pdf always exports with 5 sheets even if only the first has any information on it since the cells all have content even if that content is blank. Is there any way to have the sheet automatically reference the correct amount of rows so that it will have all of the entered info but no blank references?
Google or Excel?
Maybe try a script, copyValuesToRange, if small>var
I don't sin, I give myself opportunities to repent.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12216
  • Total likes: 6654
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #829 on: April 25, 2017, 06:21:52 PM »
Google or Excel?
Maybe try a script, copyValuesToRange, if small>var
excel. I assume that would be VBA. I would really rather not need to run that to get the form filled.
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 #830 on: April 25, 2017, 06:22:57 PM »
excel. I assume that would be VBA. I would really rather not need to run that to get the form filled.
You can have it trigger on edit
I don't sin, I give myself opportunities to repent.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12216
  • Total likes: 6654
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #831 on: April 25, 2017, 06:35:37 PM »
You can have it trigger on edit
how would I do that?
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 #832 on: April 25, 2017, 06:48:15 PM »
how would I do that?
I'm not familiar with VBA
I don't sin, I give myself opportunities to repent.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1743
  • Total likes: 219
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #833 on: April 25, 2017, 07:10:11 PM »
how would I do that?
Also not familiar with VBA, but google is...
Something like this for the triggering part:
https://www.thespreadsheetguru.com/blog/trigger-your-vba-macros-to-run-based-on-specific-cell-value-change
The VBA for the rest you know how to do?

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 24787
  • Total likes: 2502
  • DansDeals.com Hat Tips 75
    • View Profile
  • Location: NYC

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12216
  • Total likes: 6654
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #835 on: April 25, 2017, 07:14:24 PM »
I hate making macros. I especially since they bog down the worksheet and this is one i can easily have many of them open at the same time.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11992
  • Total likes: 2017
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #836 on: April 25, 2017, 07:45:06 PM »
I hate making macros. I especially since they bog down the worksheet and this is one i can easily have many of them open at the same time.
If you run the macro on demand it won't bog down your worksheet; you might find it faster than having tons of formulas recalculating all the time. You could probably even have the macro do the "export to pdf" thing for you.

That's what I'd do if it were me.

If you wanted to use Google Sheets you could have it import a range and then it wouldn't copy over the empty cells.

Another option to try (depending on you workflow) is before exporting, copy the whole sheet and paste values. This way the blank cells should be truly blank.
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11992
  • Total likes: 2017
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #837 on: April 25, 2017, 07:46:44 PM »
PS: on the topic of
I hate making macros.
There's a big difference between a well written, optimized macro written in VBA and a macro that was recorded and includes extra clicks, scrolls, and more.
Workflowy. You won't know what you're missing until you try it.

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 #838 on: April 25, 2017, 07:55:21 PM »
https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel

lol. Then how did you know it was possible.
I'm a bit familiar with Google sheets,
And also happens to be that I just wrote a trigger script.
I don't sin, I give myself opportunities to repent.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12216
  • Total likes: 6654
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #839 on: April 25, 2017, 08:01:16 PM »
PS: on the topic ofThere's a big difference between a well written, optimized macro written in VBA and a macro that was recorded and includes extra clicks, scrolls, and more.
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.
Feelings don't care about your facts