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

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5997
  • Total likes: 1823
  • DansDeals.com Hat Tips 262
    • View Profile
Re: Excel Problem
« Reply #820 on: April 19, 2017, 02:19:37 PM »
Try:
Code: [Select]
=CONCAT(text(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0),"H:MM AM/PM")," - ", text(RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0),"H:MM AM/PM"))
The Text function before the left and right (and the H:MM AM/PM) formatting after the decimal number will force the decimal to the time that it represents, I think

Creamofsoup you are a cream of genius! that worked perfectly! thanks so much!
If it's not free shipping it's not worth 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 #821 on: April 23, 2017, 02:25:29 AM »
Using GAS
I'm copying values to range, using get last row+1.
The values which I am copying sometimes have one row of Data, sometimes 2 rows of Data and sometimes 3.
I put getRange (row, 1, 3)

My issue is that for the next submission it goes to the fourth next line even if the 2nd and 3rd line had no data. (because it's copying a blank cell into the target sheet)

Any ideas?
I don't sin, I give myself opportunities to repent.

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 431
  • Total likes: 38
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #822 on: April 23, 2017, 11:14:15 AM »

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 #823 on: April 23, 2017, 11:45:32 AM »
Whats GAS?
Google apps script
Google sheets version of VBA/macros (correct terminology?)
Runs based on JavaScript
I don't sin, I give myself opportunities to repent.

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 #824 on: April 23, 2017, 03:41:33 PM »
Using GAS
I'm copying values to range, using get last row+1.
The values which I am copying sometimes have one row of Data, sometimes 2 rows of Data and sometimes 3.
I put getRange (row, 1, 3)

My issue is that for the next submission it goes to the fourth next line even if the 2nd and 3rd line had no data. (because it's copying a blank cell into the target sheet)

Any ideas?
Eta: fixed using
If (small > number) {getRange...
I don't sin, I give myself opportunities to repent.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13077
  • Total likes: 7733
  • 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: 12125
  • Total likes: 2174
  • 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.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13077
  • Total likes: 7733
  • 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.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13077
  • Total likes: 7733
  • 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.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13077
  • Total likes: 7733
  • 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: 1819
  • Total likes: 236
  • 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: 26060
  • Total likes: 3224
  • DansDeals.com Hat Tips 75
    • View Profile
  • Location: NYC

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13077
  • Total likes: 7733
  • 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: 12125
  • Total likes: 2174
  • 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: 12125
  • Total likes: 2174
  • 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.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13077
  • Total likes: 7733
  • 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