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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #260 on: February 01, 2015, 01:53:06 PM »
we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.

By far, the easiest way to do this is a pivot table.
  • Open your spreadsheet and hit ALT>D>P to start the pivot table wizard.
  • On the first page of the wizard, select 'Multiple consolidation ranges' and click next.
  • On the next screen, select 'I will create the page fields' and click next.
  • On the next screen, press the little icon with the red arrow, navigate to your first monthly tab, and select the whole table that has your values, including the headers, names, and fee values. If you want to set this up before the values are put in (ie at the beginning of the year), just highlight the entire columns - for example Sheet1!$A:$B if you have only 2 columns of data - and that way later you can just refresh the pivot table results after new data has been entered. Once you have the range selected, click add, and repeat the process for each monthly tab, then click next once you finish the last one.
  • On the next screen, select 'New worksheet' and click finish.
The wizard will then generate a pivot table, and a pivot report showing the results. Each row in the pivot table will show one customer name, alphabetized, with no duplicates. When the report is first generated, it'll show a count of how many values there are for each name. To switch that to a sum of actual values, look at the bottom right corner of your screen, in the Values section of the PivotTable Field List window. Click the little arrow next to the Count of Value field, select Value Field Settings, and switch from Count to Sum in the window that pops up. The report will now show the final result.

If you add or remove data from the source ranges (ie your monthly tabs) later, you can always click into any cell in the report and hit ALT>J>T>F>A to refresh the pivot table.

ETA: I did that in Excel 2007, but it should work similarly in all newer versions as well at the very least.
« Last Edit: February 01, 2015, 01:58:04 PM by skyguy918 »

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #261 on: February 01, 2015, 02:54:27 PM »
Thanks,
I tried to experiment with the pivot table with excel 2013, seems very complicated... Asking me about related table ranges etc. -Either way, I keep  getting an error that excel cant do it with the available resources and im just playing with jan and feb!

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #262 on: February 01, 2015, 10:59:12 PM »
Thanks,
I tried to experiment with the pivot table with excel 2013, seems very complicated... Asking me about related table ranges etc. -Either way, I keep  getting an error that excel cant do it with the available resources and im just playing with jan and feb!
Was that even with the steps I mentioned above, or does 2013 not follow that exact script?

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #263 on: February 01, 2015, 11:35:47 PM »
Was that even with the steps I mentioned above, or does 2013 not follow that exact script?
To me it looked like 2013 is totally different. Definitely couldn't follow the step by step..
Needed to set data as tables first in order to use multiple ranges etc

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #264 on: February 02, 2015, 10:00:56 AM »
To me it looked like 2013 is totally different. Definitely couldn't follow the step by step..
Needed to set data as tables first in order to use multiple ranges etc
Does ALT>D>P get you to the wizard? I googled it and it sounds like 2013 has the exact same wizard as 2007.

Here's a tutorial made for Excel 2013 that has screenshots for everything I mentioned. The relevant details are on pages 1 and 2:
http://prairiestate.edu/Assets/Global/itr-department/office/2013/Excel13-PivotTables_MultipleSheets.pdf

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6890
  • Total likes: 19
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • LYNX IT Consulting
  • Location: Chicago, Jerusalem
  • Programs: AA Exec Plat; UA Premier 1K; ; LY Plat; Hyatt Platinum; Hertz President's Circle; Avis President's Club
Re: Excel Problem
« Reply #265 on: February 24, 2015, 01:21:24 PM »
I am looking to convert a excel sheet into a html dropdown leading to another dropdown.

Column A = Countries
Column B = City, Airport Code

Any ideas? Thanks
You may not hold me responsible for any actions taken that were recommended from my account or username.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #266 on: February 24, 2015, 01:47:02 PM »
I am looking to convert a excel sheet into a html dropdown leading to another dropdown.

Column A = Countries
Column B = City, Airport Code

Any ideas? Thanks
Pivot Table wouldn't do it for you?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #267 on: February 24, 2015, 02:01:34 PM »
I am looking to convert a excel sheet into a html dropdown leading to another dropdown.

Column A = Countries
Column B = City, Airport Code

Any ideas? Thanks
Are you asking how to set this up in Excel, or how to have HTML code that looks in the Excel file for the counties/airports?

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6890
  • Total likes: 19
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • LYNX IT Consulting
  • Location: Chicago, Jerusalem
  • Programs: AA Exec Plat; UA Premier 1K; ; LY Plat; Hyatt Platinum; Hertz President's Circle; Avis President's Club
Re: Excel Problem
« Reply #268 on: February 24, 2015, 02:36:28 PM »
Are you asking how to set this up in Excel, or how to have HTML code that looks in the Excel file for the counties/airports?

Looking how to set it up in html code so i won't have to retype all the info.

But a html code that when they start typing it would look up the text in a excel file would be pretty cool
You may not hold me responsible for any actions taken that were recommended from my account or username.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #269 on: February 24, 2015, 02:37:14 PM »
Looking how to set it up in html code so i won't have to retype all the info.

But a html code that when they start typing it would look up the text in a excel file would be pretty cool
Is this form going to be on a website?
Workflowy. You won't know what you're missing until you try it.

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6890
  • Total likes: 19
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • LYNX IT Consulting
  • Location: Chicago, Jerusalem
  • Programs: AA Exec Plat; UA Premier 1K; ; LY Plat; Hyatt Platinum; Hertz President's Circle; Avis President's Club
Re: Excel Problem
« Reply #270 on: February 24, 2015, 02:43:13 PM »

Is this form going to be on a website?

Yes
You may not hold me responsible for any actions taken that were recommended from my account or username.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #271 on: February 24, 2015, 03:39:59 PM »
Yes
Does the website exist yet? How did you build it?
If you're going with wordpress, the gravity forms plugin can do this for you.
Workflowy. You won't know what you're missing until you try it.

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #272 on: February 25, 2015, 05:04:22 PM »
I have a corrupted excel file that I need repaired asap. I tried the built in repair tool..
Is there any free, safe software that I can use??

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #273 on: February 25, 2015, 06:00:51 PM »
easy, select "go to special" then "visible only"
or alt+;
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #274 on: February 25, 2015, 06:40:00 PM »
I have a corrupted excel file that I need repaired asap. I tried the built in repair tool..
Is there any free, safe software that I can use??
Can you open the file? Sometimes the easiest way is to open it, select all the cells, copy and paste them into a new file.
Workflowy. You won't know what you're missing until you try it.

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5121
  • Total likes: 2238
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #275 on: February 25, 2015, 09:13:49 PM »
Looking how to set it up in html code so i won't have to retype all the info.

But a html code that when they start typing it would look up the text in a excel file would be pretty cool
you can import csv format to mysql and pass the info through php & ajax
["-"]

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5121
  • Total likes: 2238
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #276 on: February 25, 2015, 09:35:54 PM »
example
download > open > type in the zip
["-"]

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #277 on: February 25, 2015, 09:50:24 PM »
Can you open the file? Sometimes the easiest way is to open it, select all the cells, copy and paste them into a new file.
Cant open and can't repair

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #278 on: February 25, 2015, 09:50:55 PM »
Cant open and can't repair
:(
I'll leave it for the experts, then. Good luck!
Workflowy. You won't know what you're missing until you try it.

Offline dealfinder85

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3580
  • Total likes: 9
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #279 on: March 02, 2015, 04:32:50 PM »
in a sumif, i want to add up all the values for certain states on the list, but im excluding a few. is there a function to nest inside the sumif that i can use for this to list the few im excluding?
thanks