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

Offline yungermanchik

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2018
  • Posts: 671
  • Total likes: 366
  • DansDeals.com Hat Tips 0
    • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2244
  • Total likes: 385
  • DansDeals.com Hat Tips 4
    • 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: 11449
  • Total likes: 1480
  • 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: 11057
  • Total likes: 4865
  • DansDeals.com Hat Tips 8
    • 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
Just because things turned out a certain way doesn't mean you were right.

Offline yungermanchik

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2018
  • Posts: 671
  • Total likes: 366
  • DansDeals.com Hat Tips 0
    • 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 Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2018
  • Posts: 671
  • Total likes: 366
  • DansDeals.com Hat Tips 0
    • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2244
  • Total likes: 385
  • DansDeals.com Hat Tips 4
    • 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 Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Nov 2018
  • Posts: 671
  • Total likes: 366
  • DansDeals.com Hat Tips 0
    • 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: 11449
  • Total likes: 1480
  • 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: 1958
  • Total likes: 42
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4833
  • Total likes: 365
  • 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 »
Once the game is over, the king and the pawn go back in the same box.

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 1958
  • Total likes: 42
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4833
  • Total likes: 365
  • 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 »
Once the game is over, the king and the pawn go back in the same box.

Offline Mikeoracle

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2008
  • Posts: 1958
  • Total likes: 42
  • 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