Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

Author Topic: Excel Help and Problems  (Read 287428 times)

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4378
  • Total likes: 1320
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1420 on: August 27, 2020, 11:38:56 PM »
I need to be on top of multiple legal entities, each of which has its own quirks as far as filing deadlines, etc.
there's expensive Entity Management System software out there, but there's got to be an easy way in excel or access to do this.
Anyone have ideas?

I might be able to help you with this (I do VBA coding in excel) but I don't understand what you need done so I can't really say...

Offline thaber

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 3784
  • Total likes: 465
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1421 on: August 27, 2020, 11:45:04 PM »
I might be able to help you with this (I do VBA coding in excel) but I don't understand what you need done so I can't really say...
I need to track the following for a couple dozen legal entities (corporations and such):
Name/Formation Date/State ID/ Federal ID/ Bank Account/ yearly city filing/ yearly state payment/ biyearly state filing/ yearly irs extension/ yearly irs filing/
the first 5 fields are static, easy enough in excel, the rest need annual checkboxes/reminders
A nice perk would be if the org chart (which entities are involved in which other ones) was somehow represented.

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4378
  • Total likes: 1320
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1422 on: August 28, 2020, 10:22:28 AM »
I need to track the following for a couple dozen legal entities (corporations and such):
Name/Formation Date/State ID/ Federal ID/ Bank Account/ yearly city filing/ yearly state payment/ biyearly state filing/ yearly irs extension/ yearly irs filing/
the first 5 fields are static, easy enough in excel, the rest need annual checkboxes/reminders
Doable but you'd need to either have the sheet run in the background once a day to give you the reminders, or open it every now and then to trigger the reminders (could be via a popup dialogue box or it could send you an email but you'll need to open it for it to trigger).

A nice perk would be if the org chart (which entities are involved in which other ones) was somehow represented.
In all my years working in finance I've yet to come across a really good way to do this. I've worked with lists, charts, graphs, etc and if it's not a simple setup where each company has just one parent and there's no common-ownership situations, it's confusing.

Offline thaber

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 3784
  • Total likes: 465
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1423 on: August 28, 2020, 11:16:43 AM »
Doable but you'd need to either have the sheet run in the background once a day to give you the reminders, or open it every now and then to trigger the reminders (could be via a popup dialogue box or it could send you an email but you'll need to open it for it to trigger).
In all my years working in finance I've yet to come across a really good way to do this. I've worked with lists, charts, graphs, etc and if it's not a simple setup where each company has just one parent and there's no common-ownership situations, it's confusing.
Thank you for your help. I assume macros are the way to go for reminders?

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4378
  • Total likes: 1320
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1424 on: August 28, 2020, 12:32:24 PM »
Thank you for your help. I assume macros are the way to go for reminders?
Correct.

Offline yungermanchik

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2018
  • Posts: 2668
  • Total likes: 2048
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: Previous Signatures: If you chapped hana'ah from a post, like it; You think you know the answers and things are the way they seem.. it just ain't so -The Rebbe from Lublin-
Re: Excel Help and Problems
« Reply #1425 on: August 28, 2020, 01:34:39 PM »
@jose34 & @cgr  I know it's kinda late but I just noticed this question
In cell A1 enter 5000
In cell A2 enter =A1*35%
In cell A3 enter =A1*35%
In cell A4 enter =A1*20%
and so on.

If you need this on a regular basis for different ticket prices, but the Adult, Child, and Infant % stays the same
You can just click on the little square in the lower right corner of the cell and drag it down, that will automatically copy the formula model to all the cells in the column. You can also do this with all 4 by highlighting them and then doing the above.
Small people talk about other people.
Average people talk about things
BIG PEOPLE TALK ABOUT IDEAS.

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4378
  • Total likes: 1320
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1426 on: August 28, 2020, 01:37:17 PM »
@jose34 & @cgr  You can just click on the little square in the lower right corner of the cell and drag it down, that will automatically copy the formula model to all the cells in the column. You can also do this with all 4 by highlighting them and then doing the above.
The OP has different rates for different ages so auto-fill wouldn't have worked.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1427 on: August 28, 2020, 01:47:18 PM »
The OP has different rates for different ages so auto-fill wouldn't have worked.
you can probably use vlookup pull in the rate based on age
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: 17404
  • Total likes: 14342
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1428 on: August 28, 2020, 01:52:42 PM »
you can probably use vlookup pull in the rate based on age
*INDEX(MATCH
Feelings don't care about your facts

Offline yungermanchik

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2018
  • Posts: 2668
  • Total likes: 2048
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: Previous Signatures: If you chapped hana'ah from a post, like it; You think you know the answers and things are the way they seem.. it just ain't so -The Rebbe from Lublin-
Re: Excel Help and Problems
« Reply #1429 on: August 28, 2020, 01:54:42 PM »
The OP has different rates for different ages so auto-fill wouldn't have worked.
You misunderstood what I wrote. I meant after your first five steps, you can autofill downward for other tickets. I tried it before posting. it works.
Small people talk about other people.
Average people talk about things
BIG PEOPLE TALK ABOUT IDEAS.

Offline yungermanchik

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2018
  • Posts: 2668
  • Total likes: 2048
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: Previous Signatures: If you chapped hana'ah from a post, like it; You think you know the answers and things are the way they seem.. it just ain't so -The Rebbe from Lublin-
Re: Excel Help and Problems
« Reply #1430 on: August 28, 2020, 02:08:59 PM »
For example after you put in the formulas for the first ticket and you put in any other current tickets it will look like this:


highlight the cells b1-e1 and click the square as shown. drag down as far as you want


And voila! the formula will be there for the other tickets and available for future ones as well

Small people talk about other people.
Average people talk about things
BIG PEOPLE TALK ABOUT IDEAS.

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4378
  • Total likes: 1320
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1431 on: August 28, 2020, 02:20:04 PM »
For example after you put in the formulas for the first ticket and you put in any other current tickets it will look like this:


highlight the cells b1-e1 and click the square as shown. drag down as far as you want


And voila! the formula will be there for the other tickets and available for future ones as well

Oh of course.
You don't even have to highlight- just select the cells containing the formula and double click the fill-handle. It'll automatically populate the used region.

Offline yungermanchik

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2018
  • Posts: 2668
  • Total likes: 2048
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: Previous Signatures: If you chapped hana'ah from a post, like it; You think you know the answers and things are the way they seem.. it just ain't so -The Rebbe from Lublin-
Re: Excel Help and Problems
« Reply #1432 on: August 28, 2020, 03:09:16 PM »
If you need this on a regular basis for different ticket prices, but the Adult, Child, and Infant % stays the same I'd go with a Lookup or Vlookup table.
now I'm confused, what did you mean by this?
Small people talk about other people.
Average people talk about things
BIG PEOPLE TALK ABOUT IDEAS.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1433 on: August 28, 2020, 03:20:56 PM »
now I'm confused, what did you mean by this?
In another sheet, you have a sorted list of choices with values they correspond to. If you set the 4th argument in the vlookup to true, it will search for the correct match even if it's in between two values
Workflowy. You won't know what you're missing until you try it.

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 2037
  • Total likes: 68
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1434 on: September 03, 2020, 01:16:44 PM »
Hi, Im not sure if this was addressed upthread.
How do I have an excel sheet reference a cell on a Google Sheet, so that anytime the google sheet updates, the cell in MS Excel will update (or with a refresh).
Lets keep it simple and say we want to have cell A1 reference cell A1 on the google sheet.

So far I tried the following;
1) Published the Google sheet to web, and used that link in a "Get Data- From Web" query. It pulled the info in, but it did not refresh when I changed the data on the Google Sheet, and refreshed the Query.
2) I tried updating the share permissions to all who have the link, then downloaded the google file in Excel format, and used that download link for the "Get Data- From Web" query. This just got a long error about "Unable to Connect" , "Web.Contents failed to get contents from 'https://doc...."

Anyone have any experience with this and an easy fix?

Thanks

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1435 on: September 03, 2020, 01:59:36 PM »
Hi, Im not sure if this was addressed upthread.
How do I have an excel sheet reference a cell on a Google Sheet, so that anytime the google sheet updates, the cell in MS Excel will update (or with a refresh).
Lets keep it simple and say we want to have cell A1 reference cell A1 on the google sheet.

So far I tried the following;
1) Published the Google sheet to web, and used that link in a "Get Data- From Web" query. It pulled the info in, but it did not refresh when I changed the data on the Google Sheet, and refreshed the Query.
2) I tried updating the share permissions to all who have the link, then downloaded the google file in Excel format, and used that download link for the "Get Data- From Web" query. This just got a long error about "Unable to Connect" , "Web.Contents failed to get contents from 'https://doc...."

Anyone have any experience with this and an easy fix?

Thanks

Is the Google Sheet set to Automatically republish when changes are made?

Also, Published pages may only check / pull updates to you every 5 minutes.
« Last Edit: September 03, 2020, 02:03:00 PM by lubaby »

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 2037
  • Total likes: 68
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1436 on: September 03, 2020, 02:02:34 PM »
Is the Google Sheet set to Automatically republish when changes are made?
Yes

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1437 on: September 03, 2020, 02:18:18 PM »
Yes
Anything come in after doing a manual refresh after more then 5 minutes from when the sheet got updated?

I just did a test publish / link to excel and updates are coming through fine on manual refresh after waiting 5 minutes.

ETA: Excel's update automatically at set interval (every 5 minutes) is working fine as well to bring in the latest data from the sheet.
« Last Edit: September 03, 2020, 02:27:33 PM by lubaby »

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 2037
  • Total likes: 68
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1438 on: September 03, 2020, 02:44:22 PM »
Anything come in after doing a manual refresh after more then 5 minutes from when the sheet got updated?

I just did a test publish / link to excel and updates are coming through fine on manual refresh after waiting 5 minutes.

ETA: Excel's update automatically at set interval (every 5 minutes) is working fine as well to bring in the latest data from the sheet.
That did the trick, thanks!
I didnt realize there was a 5 minute lag time

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6658
  • Total likes: 2588
  • DansDeals.com Hat Tips 60
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1439 on: January 08, 2021, 12:42:09 AM »
I have a google sheet with a column with links to an mp3, a column for artist and a column for title, is there any way to have it automatically download all the mp3s with the filename based on the artist and title column?
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/