|
I have been working at my new finance job for little over a week but the work load has been getting very tough. Especially this one project that is giving me a hard time understanding so I am hoping that someone here can help me understand it better. My boss made a P&L (profit and loss) excel spreadsheet for FY13 and she wants me to simplify it for FY14 with various different drop down menus added. Now before I get into that, I still need to understand the big picture. The Excel sheet has two "master" sheets titled "Pivot_Master" and "Pivot_FuncDetail". I assume that the information is gathered from those sheets but why is there two? In addition, there are about 10 different separate slides with headcounts and expenses (operating expensive and cost of goods sold). They all have different expense reports for the different sectors of the business. I understand the basics of Excel. I understand how Power Pivot and Macros work, along with the formulas. However, I am having a hard time seeing where the data is pulled from. For example, in one of the expense sheets under headcount, this is the formula used for the Research and Development cell: =IFERROR(GETPIVOTDATA(+$H$9,INDIRECT($H$11),"Fiscal Month",$E$6,"Line Item",$D14,"Profit Center SEC Func Area2", $C14), 0) Now can someone explain this to me. Where is the Excel sheet pulling the data from? Is it Pivot_Master or Pivot_FuncDetail? Both Pivot sheets go down to about 700 rows with numbers and data and I am having a hard time seeing where they are pulling the information from. I looked at the GETPIVOTDATA info online and I cant find the H9 data field or even the H11 pivot table. I wish I could just click on $H$11 and it'll take me to the slide its pulling the data from.... Thank you for your help!
|
#
¿
Apr 14, 2013 19:55
|
|
|
|
| # ¿ Jun 3, 2024 07:45 |
|
|
Old James posted:It all depends on what the values or H9, H11, E6, D14, and C14 are. The Pivot table being referenced is based off INDIRECT($H$11), this is the name of the pivot table itself not necessarily the same as the tab name. H9 is the field name whose value is being returned by the function. The other 3 are different filters applied to narrow down which value in field H9 to return. That did help, thank you! One of the main issues I had was that many of the columns and rows in the file were hidden. Once I unhid everything, it all started to make sense. Now I have some additional questions that I was hoping I could get help with. The formula at the ‘Incentive Compensation’ column and row ‘Actual’ for the Expense Sheet is: =IFERROR(GETPIVOTDATA(1:1+$H$2,INDIRECT($J$11),"Profit Center SEC Func Area2",$A22,"Class", $B22,"Fiscal Month",$E$6,"Exec Function Summary",$L$1,"Sub Class",$D22,"Group",$E22,"Line Item",$C22), 0) 1) The data field for the GETPIVOTDATA is 1:1+$H$2. What does that mean and what is the point of 1:1? H2 is a cell with the word “Actual” in it with no formula. In that case, is there even a point to do $H$2 instead of just H2? 2) Explain =IFERROR, so if the value is 0, it’s an error? 3) Why the need for an INDIRECT($J$11) function? J11 has the following formula ‘MercPivot_Master!$B$20’ – Why the need for $J$11 and not just J11? Seems like it is just pulling the data from J11 anyway? 4) Why is there an exclamation point between MercPivot_Master and $B$20? 5) For - "Fiscal Month",$E$6, - E6 is a data validated drop down menu of the Fiscal Month. So you can click on E6 and choose any of the FY13 months. If that is the case, why is it in the formula typed out as $E$6 and not just E6? 6) Do the Field 1 & Item 1 part of GETPIVOTDATA necessary have to be in a special order or can it all just be random and Excel will pull the data from the supplied Field & Item entries? Thank you. Busy Bee fucked around with this message at 20:43 on Apr 15, 2013 |
|
#
¿
Apr 15, 2013 20:18
|
|
|
 Simple question. As you can see in Row A, I have the profit center with all the internal order numbers in Row B and comments on those orders in Row C. How do I make it so that I can collapse and expand both Row B and C when I click on the profit center in Row A? Old James posted:Stuff Thank you for your help. That was extremely helpful! I just ordered a few books off of Amazon on Excel so I am hoping that that will help me. Busy Bee fucked around with this message at 23:29 on Apr 16, 2013 |
|
#
¿
Apr 16, 2013 23:06
|
|
|
edit!
Busy Bee fucked around with this message at 19:16 on Apr 17, 2013 |
|
#
¿
Apr 17, 2013 01:16
|
|
|
 I currently have a Pivot Table with the above data. I can filter it by the Function of Business and whether the purchase order is open or closed. There are around 10 different functions of business, 10 different company codes, a lot of people, and the purchase order numbers. I am trying to find the value of how many purchase orders each person has. I tried to do a "Count of Purchase Order Numbers" as the value in the Pivot Table but it doesn't work for the following reason. Within each order number, there can be anywhere from 1 - 10 different line items. Meaning one purchase order number can have three line items with such descriptions such as "Bill for conference room" / "Bill for service" / "bill for cleanup" etc. So when I use the "Count of Purchase Order" value, it counts each line item so someone can have only one purchase order number, but if that number has three line items, then the value shows as 3 for "Count of Purchase Orders". What would be the easiest way for me to see the number of purchase orders each person has? I'm thinking I could manually create a formula field and insert it into the Pivot but what type of formual would I use? Busy Bee fucked around with this message at 19:53 on May 15, 2013 |
|
#
¿
May 15, 2013 00:00
|
|
|
I've been messing around with PowerView on Excel 2013 and I was wondering if there was a way that I can have all my PowerView sheets in presentable format. All the tutorials online talk about SQL Server and Sharepoint, I dont have access to either. celestial teapot posted:It would be easier to say for sure if I could see the data source instead of the pivot table, but a pivot table should be able to do this for you: you would add a total row for PO Number and change it from a sum to a count. Edit: Okay, it sounds like you said you tried that, which is weird. Pivot tables are designed to handle data normalized in the way that you described. Can you post the workbook? Thank you for your help!
|
|
#
¿
May 20, 2013 18:12
|
|
|
There are two types of people who work at my company - contracted employees (c- & t-) and full time employees. I currently have a list like so with the data on the left, and what I want to accomplish on the right:  I have the "Team" and "Employee Name" in Pivot Tables. How would I go about this using Excel 2013? I'm thinking I could do something with a formula and have a column on my list of employees with "Employee Type" (Contract & Full Time) so then I can just easily play around with the "Count of Employee Type" in Pivot Tables. celestial teapot posted:Maybe this: http://office.microsoft.com/en-us/excel-help/create-a-power-view-sheet-connected-to-an-external-data-model-in-excel-HA103230578.aspx Thanks for that. Apparently my company has an offsite Sharepoint site that I had no idea about. Busy Bee fucked around with this message at 02:33 on May 21, 2013 |
|
#
¿
May 21, 2013 02:30
|
|
|
I work in a department with 150 employees divided into four offices around the world. We're a global company and we speak with customers all around the world. Sometimes we have cases we need to take where we need a specific language / region skill. I want to make an Excel sheet where I pull data from the following categories: 1) Office Location 2) Employee Name 3) Day of Hire 4) Shift Hours 5) Days Off 6) Language Skills - English (Native) / Spanish (Advanced) / French (Intermediate) etc. 7) Region Skills - United States / South America / Eastern Asia etc. With the above information, any of the employees can open the Excel document and say, "I need someone with Spanish skills" "I need someone with knowledge of South America" and once they choose whatever they need, it'll pull up a list of the employees with those specific details. I will use the #3 data point to show a cell that shows months of experience since day of hire that automatically updates to the current date. I would also like to use the #4 data point to create a cell that shows whether that employee is on shift and if not, how many hours until they will be in office. How do you suggest I go about this once I gather all the data points from the employees? I am thinking have one tab with all the information, and another tab where it pulls the data from the first tab. Pivot tables? Busy Bee fucked around with this message at 21:49 on Feb 19, 2016 |
|
#
¿
Feb 19, 2016 21:45
|
|
|
fosborb posted:Pivot Tables summarize data. Your requirements sound like you just need to filter details. That makes sense and seems like the easiest option. However, how would I filter some of the sections such as 'Language Skills' and 'Region Knowledge' where there could be multiple choices - there are many employees who speak at least 3 or 4 languages.
|
|
#
¿
Feb 19, 2016 23:41
|
|
|
I have a final round of interviews next week with a 30 minute assignment / tech interview using Microsoft Excel. They will give me a large data set and I will have to use Excel to find patterns in it and manipulate the data using Excel / Pivot Tables to identify patterns in the data. I've used Excel and Pivot Tables in the past but is there anything I should keep in mind when faced with a task such as this?
|
|
#
¿
Dec 1, 2017 09:04
|
|
|
Fingerless Gloves posted:It depends on if you've already demonstrated your excel abilities I think. If you haven't then you should throw in a few examples of things that can seem impressive like conditional formatting and named ranges. Really it sounds more like a test of your data reading. Well this is for an analyst position so nothing too complicated I'm assuming. I haven't had a chance to demonstrate my excel abilities to them but I have a fairly good understanding from my previous role. However, conditional formatting, named ranges, trimming etc are things I'm not aware of so I will definitely look into that. Thank you!
|
|
#
¿
Dec 1, 2017 09:48
|
|
|
Fingerless Gloves posted:Oh cool, I can actually help there then. That's great advice, thank you. What do you mean by never making a static table? So lets say they give me an Excel sheet with multiple columns and rows - the columns could be name, price, purchase ID etc. The way I would go about it is to create a table from that where I can play around with the column header. Super basic understanding of Excel on my end. Then I could insert pivot tables and mess around with the data from there and compare the various variables. Does this mean it's still a static table?
|
|
#
¿
Dec 1, 2017 11:25
|
|
|
Thanks everyone for the help! So far, I've refreshed my memory or learned a little more about VLOOKUP, SUMIF(S), COUNTIF(S), PivotTables - consider date being the first thing to pivot, grouping by function, incorporating slicers, using combo chart of line and column, play around with value field setting and sorting the column by value. Anything else I should keep in mind?
|
|
#
¿
Dec 4, 2017 04:08
|
|
|
Wow, thank you so much for your help. That was all very helpful. I have a question though, I was able to follow your steps all the way until 4th screenshot. How did you get the closing month for the columns? If I try to move the closing date, it moves it from the row into the columns. How did you do both?
|
|
#
¿
Dec 4, 2017 14:04
|
|
|
I have an Excel document with about 1500 rows of different IP addresses. How can I take the IP addresses and output the location of the IP in the next column over? I have been manually copying and pasting it into a bulk IP lookup site (Which can only do a max of 100) so its quite a pain in the rear end. But I know there's an easier way out there. What should I do?
|
|
#
¿
Jul 23, 2018 19:14
|
|
|
Just received a new laptop at work with the latest version of Microsoft Office 365. My previous laptop had Microsoft Office 2013 installed and I had no problems opening CSV files with all the data formatted in the correct way – columns were all separated correctly. However, I’m having issues now on my new laptop right when I open the CSV file for some reason – all the columns are stuck together. A correct fix that I found is unchecking “Use system separators” in the Advanced section of Excel options. When I uncheck that, all the columns and data is separated correctly but it also messes up the format of some of the variables such as the currency amount. The CSV columns are formatted like this “data 1, data 2, data 3, data 4” etc. How can I fix this? It doesn't fix it if I save the CSV as an Excel Workbook after opening it and I do not have an option to save the CSV files as an Excel Workbook when I download the workbook.
|
|
#
¿
Jan 29, 2019 12:16
|
|
|
nielsm posted:Windows is probably configured to use comma as decimal separator, and period as thousands separator. (European style.) Change that in the international settings page where ever they have put it now. That worked, thank you!
|
|
#
¿
Jan 29, 2019 16:38
|
|
|
I have the latest version of Excel and whenever I do a Find and Replace -> Replace All -> Dialog pops up saying they made XX,XXX replacements and I click okay -> My Excel hangs and freezes for at least a minute and I can't do anything. What's going on here and how can I fix this?
|
|
#
¿
Apr 9, 2019 11:10
|
|
|
https://imgur.com/a/NpsXPsA As you can see by the above picture, I want to show the number of orders split by country (USA, Canada, and Japan) from January 2019 to December 2019 along with the rate of acceptance between January and December. The problem I'm having is that the % line is connected between the countries when I do not want that. Is there a way to fix this?
|
|
#
¿
Dec 16, 2019 10:53
|
|
|
I have some euro amounts in the following format: 6,923€ 10,021€ etc. But I want to shorten it to 6,9k€ and 10,0k€. How do I go about this? I looked at the following site and used the following format #,##0,“k” but it's only showing me rounded up so 7k€ and 10k€ https://wmfexcel.com/2015/03/14/show-number-in-thousand-k-or-in-million-m-by-using-custom-format/
|
|
#
¿
Jan 28, 2020 21:25
|
|
|
I'm currently grading some of my trading cards and keeping track of it in a spreadsheet. I have 7 columns - the 1st column is the name of the card, the 2nd column is the estimated grade of the card ranging from 1 to 10 with 10 being completely mint, and the remaining 5 columns are categorized from Ungraded to 7, 8, 9, and 10 with an average sold price for those respective grades. Essentially, any grade equal to 6 or below will fall under the "Ungraded" column. I want to create a new column where it takes the estimated grade from the 2nd column, finds the average price in column 3, 4, 5, 6, and 7 - and then outputs that number in the new column. What would be the best way of going about this? For example, from the image I attached, the new column will have the following outputs: Row 1 - $355 Row 2 - $380 Row 3 - $280 Row 4 - $235 (I accidentally did not include the "10" grade in the screenshot I shared and now I can't edit it but the formula would still be the same)
|
|
#
¿
Jan 5, 2023 13:49
|
|
|
HootTheOwl posted:seems simple enough to just use a switch (also, oh gently caress yes excel has built in switch case?) That worked! Thank you! Now I have another question. Let's say I want another column now to show the estimated price for the one grade above the estimated grade I chose. However, if the estimated grade I chose is any one between 1 to 5, the output will always be the ungraded price while if I chose an estimated grade of 6, 7, 8, or 9 - it will show the average price for the grades 7, 8, 9, and 10. I have no estimated 10 grades in my Google Sheet nor do I intend to have it. I want this new column to show this data in case I want to take the risk of paying to get the card graded in the chance that I will receive a higher grade. How would I go about this? Busy Bee fucked around with this message at 15:03 on Jan 5, 2023 |
|
#
¿
Jan 5, 2023 15:01
|
|
|
HootTheOwl posted:The switch function looks for a match and then returns the value when it does, with the last value being no match. Amazing, thank you so much!
|
|
#
¿
Jan 5, 2023 16:06
|
|
|
I hope a Google Sheet question is okay here  I have a column in a Google Sheet where I copy and paste a date in YYYY-MM-DD format from the data we receive from the supplier. However, since we have many suppliers and thousands of rows of data, sometimes the date format they send to us is incorrect which messes up the automated data extraction. What would be the best way to ensure that when the data is copy and pasted to have some sort of notification that the provided date format is incorrect? I was looking into conditional formatting but not sure if that would suffice.
|
|
#
¿
Feb 6, 2023 09:42
|
|
|
Hi all, I have such a small and basic Excel question that I'm hoping you can help me with. I'm organizing my finances and I have one main table with all accounts and a second one with only a few select ones that points to the various cells in the main table. When I make changes in the main table, it automatically changes in the second table. However, the issue is that when I sort the main table, of course the second table gets messed up because it uses a basic "=A2" formula to get the main table cell value. What is the solution here? I remember learning about absolute references but not sure if this is relevant.
|
|
#
¿
Sep 9, 2023 08:52
|
|
|
nielsm posted:Two ideas come to mind, depending on how your data actually look and what kind of view you are making: I took the template from this Google Sheet here under the "Holdings" tab - https://docs.google.com/spreadsheets/d/1U7HoZMaPDIaq-4EeXqCljoQhkGm9T0Zt8WZPaYj6KKE/edit?usp=sharing What you see in the Holdings sheet is the main table, and then I copied it to the second table to only include the relevant accounts I want to see. For the second table, it links to the cells in Column A, B and F (Account, Symbol and Shares).
|
|
#
¿
Sep 9, 2023 09:35
|
|
|
|
| # ¿ Jun 3, 2024 07:45 |
|
|
My question is, if I were to use VLOOKUP, how can I use it to have multiple data points? Column A has "Individual Account" and "Retirement Account" while Column B both has the same ticker name "S&P500". For example: If Column A = "Individual Account" and Column B = "S&P500", show data in Column C. Or would it be easier to have one unique ID for each row to link to?
|
|
#
¿
Sep 9, 2023 14:12
|
|