i am working on a project where i need to get data from a pivot table that resides on a separate worksheet named "Pivot_by_Group".
I have a worksheet "Budget vs. Actual" and I need to pull data from the pivot table to populate the Actual values.
How can i do that and one more thing, hopw can i suppres errors (#NAME, #REF, etc), i've used the ISERROR function with no luck.
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
I'm struggling to get the "getpivotdata" function working correctly.Sample book attached.
I'm trying to return the data that is held within the pivot table to a separate part of the sheet.In column D I have week numbers and in column E the qty(which is currently empty) How do I return the qty in column E from the data in the pivot table (column B) relative to the respective week numbers.
I have a spreadsheet with 10 or more items that I would like to separate into their own worksheet. I'm thinking about a macro but I'm not sure about creating new worksheets in a macro.
Is there any more efficient way to get a formula that tries to get pivot data to return data to show zero, rather than ERROR, than this?
=if(iserror("the get pivot data formula"),0,"the get pivot data formula")
I am trying to squeeze some more effenciency into a ridiculous spreadsheet, and I hate that this formula has to do 2 lookups and an if statement. If this really is 2x the work, I was wondering if there is a more efficient way?
Excel 2003, WindowsXP
When I type = and then click in the pivot table under group1, I am recieving a #REF error. It strange because when I do the same thing in another group (ie. group 2 or group 3, etc.) in the same pivot table I don't recieve the error?
Trying to use a the getpivotdata fomula with date as an argument, refer to with a cell. e.g. =GETPIVOTDATA( 'May Table'!A1, "SICK " & S20). where S20 is a date format
View 2 Replies View RelatedMy created pivot table chart is a "sales funnel" that has 8 field list in the title section used to slice the date (i.e. per quarter, per sales person, per product, etc.). When a combination of selections results in no data, the pivot table dynamically removes this column or row. The GETPIVOTDATA function returns a #REF! because of the invalid reference. How can I lock the pivot table to always have all columns and rows or can I return a zero instead of the #REF!? When the #REF! occurs in a @sum range, the #REF! is returned instead of the sum of the numbers.
View 3 Replies View RelatedI am trying to get the lookup function to return the corresponding value in a separate worksheet where it occurs more than once in the lookup range.
At the moment it is only finding the first match. i.e. in
=VLOOKUP(A2,'Appointment Tracker'!A2:B9999,2,0)
I have a spreadsheet which is a list of dates and amounts.
I want to have a filter on a second worksheet wherein I can type the date and it will filter all amounts for that date.
I've tried the Advanced Filter but I can't get it to auto filter when I change the date. Also it needs setting up every time I do an advanced filter.
I want to create a charts on a pivot table, but I don't like the way Pivot charts function and the limitations they have.
So I want to create a normal charts that is dynamic and based on a pivot table.
In order to do so, I want to create dynamic named ranged for the data in the pivot table to use for the data series for the chart.
I can reference the week numbers (headers in the pivot table) for one data series and get the data by this formula:
=OFFSET(Weekly!$C$15,0,0,1,COUNTA(Weekly!$14:$14)-1)
$C$15 is the first cell that holds the data for the series.
Row 14 holds the week numbers (headers in the pviot)
However, I want to make that formula even more dynamic by having it reflect the changes done to the pivot table who's cells it references to.
So how do I do this using the GETPIVOTDATA function? (or some other function that makes it dynamic and reflect changes done to the pivot table and accounts for the row numbers with the data can change)
If I generate the GETPIVOTDATA formula and try to substistute the fixed cell reference for $c$15 with it, the OFFSET formula for the dynamic range gives an error.
And how can I grab the column headers (week number) dynamically?
I've attached a spreadsheet which is a basic version of what I'm trying to achieve. I've got a pivottable based on 3 columns of data: Order No, Delivery Date, Value
I want the sum of a given order in a certain month. E.g. the value of Order No. 1001 for Dec '05. I tried doing a pivottable and then using GETPIVOTDATA (which I've not really used before) and failed. I imagine there's a better way I've not attempted yet. Can anyone shed any light? The spreadsheet is attached
I have this problem with pivot tables:when I write a formula relating to a cell in the pivot table I don't get the usual basic cell refs (e.g. B4/b5) but I get something like GETPIVOTDATA("Vehicle No",$A$3,"Model Desc",...... etc etc
and I cannot copy and paste this formula!
I would like to know if there is a shortcut to use basic Cell refs because it is very boring always type the cell details in manually when using data from a Pvt table
Some weeks ago I had to move from excel 2000 to excel 2003 and It seems incredible to me that moving to a more recent release of excel become a step behind....
also using vba code?
Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be:
=GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation")
I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus:
ERG
1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation")
.. and this works fine.
However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!
I have a workbook containing many different sheets. What I want to accomplish is when I click on a check box on one sheet, I want the check box in a different worksheet to be unchecked. I have form control checkboxes in my workbook, so I used xlOff as the value.
Here's what I have in my VBA code.
Worksheets("February").CheckBoxes("CheckBox13").Value = xlOff
It is giving me the error "Unable to get the CheckBoxes property of the Worksheet class"
Using VBA, I am trying (without success) to copy the active worksheet of my workbook and save it in the current folder using a filename shown in cell A1. I only need to save values and formats. Any existing code (auto fit) contained withing the sheet would no longer be required. I get a VB project message relating to macros. I would anticipate saving as xlsx would deal with this but again, am at a loss.
View 4 Replies View RelatedI have 2 worksheets in my workbook.
One is named "Link Menu" . . . The other is "Setup"
I have an object on "Link Menu" that can be clicked to run a macro.
On the Setup sheet, I have a workable link that opens my email. Whatever link that the user would type into cell D6 on the Setup Sheet would be opened.
I want to beable to click my object on "Link Menu" and have the macro open the link from cell D6 on the Setup sheet.
I would like to put together a macro that would compare a list of part numbers against a database, and recommend alternative vendors for the part. The actual VBA I think I can handle, my problem is with the formula, array or whatever is needed to return the results I am looking for.
I have attached a sample workbook to this message that contains two worksheets. The "Sample Database" is an example of the format of the database (in Excel format) that I will be looking against. This data may be a hundred columns wide, and thousands of rows long.
The "Vendor Reference" worksheet shows an example of the data that would be returned if the "perfect" formula were entered into the cells. (I'll explain what I mean by "perfect" in a moment.) It also has an area designated for testing formulas to see what is returned. It's blank, because none of my tests returned anything but errors.
You will notice the column headings on the "Sample Database" worksheet. Columns A and B will always contain the part number and product description. The remaining columns will contain vendor specific data, with perhaps 25 different vendors, and 3 columns per vendor. These columns would be "PV", (for Primary Vendor), the vendor number, (010299, for example), and the Vend Part#.
If there is an asterisk, "*", under the "PV" column for that vendor, that means they are the "Primary" vendor. The cost from that vendor is listed under each vendor number. There may be instances where 2 different vendors are listed as primary. If that's the case, the formula can simply return the FIRST one found.
Here's what the "perfect" formula would return: .....
I have a userform which when you enter data and hit submit, inserts data into a seperate sheet in the same file. Code below
Private Sub CommandButton1_Click()
Sheets("Purchase Order").Select
UserForm1.tbJobNo.Value = _
Sheets("Purchase Order"). Range("I4")
UserForm1.tbJobTitle.Value = _
Sheets("Purchase Order").Range("K4")
UserForm1.tbOrderNo.Value = _
Sheets("Purchase Order").Range("I49")
UserForm1.cbdirector.Value = _
Sheets("Purchase Order").Range("K43")
Instead of placing the data into another worksheet i would like to put the data into a closed xls file named Purchase Order Database.xls.
How can adjust the code for it to do this?
In Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
View 1 Replies View Relatedso, for a column, if i reference a getpivotdata to an item's total, it will sum the total being displayed- which can be less than the "real" total if some items have been hidden in the pivot table. what i want is to have getpivotdata (or another function, i don't care what it's called) to always sum an item's total for the entire pivot table range- irrespective of whether certain of it's details have been hidden. ideally, what i'm trying to do is:
sum the total of an item for the pivot table range
sum the total of the column from the original data
compare if they are not equal, the pivot table has not been refreshed
i need to do this without macros. (it's my "solution" to see if macros are enabled or disabled- because my pivot table automatically refreshes data based on macro)
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I can’t figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)")
If Template = False Then
cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly)
If cont1 = vbOK Then
Exit Sub
End If
End If
I am trying to build a report based on data in a separate worksheet. The report must only show the name of the value and qty associated with it only if the qty is greater than 0.
As an example, the values can be colors.
Column A = Names of Colors: Red, Blue, Green, Yellow, Black
Column B = Qty of each Color: 5,4,0,1,0
The report is held on a separate spreadsheet. I would like to only show the values that have qty's greater than 0, not any values that have a value of 0.
I've attached a sample workbook just in case.
i have a worksheet that has information filled in columns A-N and rows 3-7262 (not including title and labels)
i need a macros that will separate this data into multiple worksheets based on the word in Column A; these words could be HDA, CCR/NHT, GRU/ATU (essentially dividing this data up by area name, there will be 14 worksheets)
i would also like to be able to update this master worksheet and have it be reflected in the multiple worksheets that are divided by their areas.
Im working with a huge amount of data in BOOK1.xls and once or twice a week I received an update of the list.
1. I need to compare and find out from the new updated worksheet if there is a new revision, and if there are new documents added to the list. I have tried Vlookup..but it didnt work for me. Ex. Doc1 has revision 1, 2 in my BOOK1 that I am working on. Note that there is a repetition of the DocName, Doc Number except for the revision.
I need the information that is entered in a userform to be sent to a separate worksheet. Each time a user fills out the form, I would like the information to be documented on the next completely open row (ex. first form goes in row 2 (column headings are in row 1), second form goes in row 3, etc.). I don't know a ton when it comes to VBA, but I believe I have the code figured out to search for the next open row. Column A will always have data in it.:
Code:
dim currentrow as range
set currentrow=Range("A1").end(xldown).offset(1,0).entirerow)
What I can't figure out is how to insert the information to the correct columns. Each column has a header to it, and I would like the data from the userform to be put in the relevant column each time it is filled out.
For example, if column A's header was "First Name" and columm B's header was "Last Name", each time the form is filled out, I would like the first and last name (which was entered in the form) to be inserted into those columns (with each form entry on a separate row).
I think there might be a way to use the intersect method to find the row/column combination I'm looking for, but I'm not exactly sure how to do it. Using the first name example from above, it would basically look for the intersection of "currentrow" and column A, and insert the value that is entered into the userform.
I have read a number of posts and other sites and can create the hyperlink in the desired cell but get an error message to the effect, "Cannot open the workbook."
The current macro follows. I believe its description is made in the comment statements below the date-author comment.
Sub DecomposeCTQ()
'Decompose CTQ Macro
'14Oct13 Charles T. Carroll'
'This macro copies the template to a new worksheet and takes data from the'
' active cell CTQ and makes it the parent CTQ on the new worksheet. Then'
' it renames the new worksheet with the active cell CTQ ID.'
If ActiveCell.Column 2 Then
MsgBox "You must be in the CTQ ID Column to run this program"
GoTo Leave
[code]......
I need to populate combo boxes from a list of data on a separate workbook. I set everything up then found out the way I did it the seperate workbook must be opened before the drop downs are populated with the information. Is there a way to not have the linked workbook open?
Also these workbooks are all contained in their own file that must be duplicated on to other machines and into other larger files. I need that linked information to always follow the workbook contained in the same file. Another words if I have the original file "A" and duplicate it and now have a file "B" I want the combo box in file B to be linked to the workbook in file B.
Is there a way to display multiple cells in a separate worksheet that are a part of multiple worksheets? Please see below.
Worksheet 1 has list of email addresses in column A
Worksheet 2 has list of email addresses in column A
How to have Worksheet 3 display email addresses in column A that were on Worksheet 1 and Worksheet 2? Considering all duplicates are removed from each worksheet.
Worksheet 1 (column A)
red
blue
green
yellow
[Code]....
Need to have Worksheet 3 display as: (column A)
red
blue
green
I want to sum up data for two periods (4&5) from a pivot table using GETPIVOTDATA formula. I've only been successful when I manually type the period values 4 and 5 as is shown below:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{4,5},"Project",$A7,"Category","T&M"))),0)
However, I cannot get the formula to work when I substitute cell references for the period values 4 and 5. Therefore, the following formula does not work:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) where E5 has 4 in the cell and E6 has 5.