How come doing this doesnt work?
=GETPIVOTDATA("Payment",PivotTables!$B$25,"Year",Formulas!J4)
I want to have the item at the end link to a cell reference so it can change.
how to make that work?
I manage a team that quote for business. They are targetted on number of quotes per month but then also wins per month, however a win may come a number of months after the original quote was generated.
They work off a spreadsheet where they log:
Quote date (And month)
Quote Value
... customer info etc
Win month (against orginal quote information)
Win value (against orginal quote information)
I started writing my pivot using the Quote month as the main reference point, IE Quote Month in the Row, and then put all the data in the central drop data section... However, there are two immediate problems with this:
They are targetted on quotes generated per month, some business won has come from last year, which they are lifting from the old spreadsheet and puttin in my new one... meaning that quotes they generated in Oct 2012 are now showing up as quotes for October this year.
If the wins span a number of months they are showing in January (if quoted in January) and not in March (when actually won)
Is there a better way of writing my pivot table??? There must be... ideally what I would like is half the table referencing the Quote date and half referencing the order date... but I don't know how to do this? I could probably do this with pages but I would like to show all on one sheet...
I have a worksheet ("VillageReport") that contains several pivot tables. All tables are from the same data source and all pivot tables have "Location" as the first column field. I would like for the tables on this sheet to automatically be filtered when I select a village name from a drop down menu in a cell on this worksheet (C1). So, for example, when I select "Tarzana" in C1, the location field of all pivot tables on this sheet will show only Tarzana (all others are deselected).
a Macro for this? (I have already set up the drop down menu in C1). I have found some examples online but can't seem to adjust the code to fit my workbook. I am not very familiar with VBA.
Here is what I have
1. Have a COUNT value in a cell (counted the number of rows with values in them).
2. A formula that needs this value (sums another set of values in another column that has the same number of rows).
Example: the COUNT value is 12, and is located in cell G5.
For simplicity sake I have a formula in cell I5 SUM(F1:F12).
Instead of directly referencing F12, I want the formula to be SUM(F1:F(G5)).
This does almost exactlly what I want. The one problem that I have is that the active cell remains B1 as it enters the formula in the rest of column B. I need it to refer to the row that it is entering the formulas; meaning each formula entered in B2,3,4, etc. is still referencing cells A1 & D1 for sBook & a sFilePath.
Dim sBook As String
Dim sFilePath As String
Dim sLen As Long
Range("B1").Select
sBook = ActiveCell.Offset(0, -1)
sFilePath = ActiveCell.Offset(0, 2)
sLen = Range("A1").End(xlDown).Row
Range("B1").Formula = "='" & sFilePath & "[" & sBook & ".xls]Sheet1'!R1C1"
Range("B1", Cells(sLen, 2)).Formula = Range("B1").Formula
I've got an issue where Formulas Referencing Cells on another sheet are getting changed automatically to refer the the moved cell data. The formulas on a sheet (2) refer to specific cells on sheet (1) within my workbook. I update Sheet(1) data with a macro. When sheet (1) updates, the present data gets shifted to the right.
In other words, I need to prevent the formulas from changing to reference the moved data values and keep referencing the initial cell with the new values. xample of change that occurs with update:
Correct:
I am working on a sheet called 2005, with "Jan" entered into cell C4. I want to reference cells on the sheet called Jan2005 using a function that points to C4 and adds "2005".
View 2 Replies View RelatedI have a series of worksheets, named, 'sheet1' 'sheet2' etc. through to 'sheet10'. A final worksheet, 'sheet11' has been produced that uses the formula: address=Sheet1!A1, address=Sheet2!A1, address =Sheet3!A1 etc. The same goes for other identical cells on each of the sheets. I am at the moment entering each cell on 'sheet 11' separately because if I copy the formula down it progressively changes the cell value and not the sheet number. How can I produce a formula whereby the cells remain identical but the sheets change progressively?
View 4 Replies View RelatedI want to set a conditional format to cell A1 with a value in cell P1 When the value in P1 is between 0 and 10 the conditional value in P1 should be set.
Note
cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.
I have a document that I created that has merged cells. In order to autofit the rows of this document, I referenced the merged cells in singular cells that are not visible to the users of the document. (They are not hidden)
It had been working relatively nicely, but now the autofit function is not working correctly. If I copy and paste values into the cell it will work, but this would not be my ideal solution. I have tried playing with the number format, which works for the most part if I set it to "&" - text. It doesn't work for some longer cells with line breaks in them.
I want to use the values in a range of cells in a sheet for a formula in another sheet. The problem is that these range of cells already has formulas and when I select these cells for use in another formula, the values doesn't get picked up and my formula doesn't work. for example -
cell L4 in sheet 1 has a formula - ='H:VK_2011_onwards1_ACC_BJS_SAA 6
and it has a value - 34.76
cell L5 in sheet 1 has a formula - ='H:VK_2011_onwards1_ACC_BJS_SAA 6
and it has a value - 47.09
Now I want to use this two cell to calculate an average in sheet 2. So if I use the formula =AVERAGE(Sheet1!L4:L5). I get no result.
I have a table where the first column contains the date and the second column contains number of calls answered on that day. I have all the dates from Jan 1, 2007 - Dec 31, 2007 listed in the first column. I would like to be able to show the last date where data was entered at the top on my page where I have my Year-to-date running total. I would like the date to be automatically updated to the last datewhere data is entered.
View 2 Replies View RelatedIn cell H1, I want to multiply the value in cell B[x] by $G$1 where x is given in cell A1. For example, if A1 is 4, I want to to multiply B4 by $G$1 and have the result in H1.
View 2 Replies View RelatedI am trying to create a macro that will change all pivot fields with a certain name to the value I have the master pivot changed to. For example, I have 5 pivot tables, which each contain the field "Fruit". I want to change the 1st pivot table to "Apples", "Oranges", and "Pears" as active values, and then run the macro, making the other fruit fields also have these values. I can do it for single items, but when I need to do multiple items, I get an error message. I'm not sure how to write in VBA in order to do this.
View 5 Replies View RelatedI have a question about pivots tables and formuals in the raw data. What I am trying to do is an absolute formula within the raw data, so that I do not have to do it on each individual tab and just have it in the raw data to add into the pivot.
Example: in the raw data I have columns with orders and forecast. I do an absolute formula in that raw data to get a number (lets say the number ended up being 9). Now when I create a pivot and add product, order, forecast and absolute formula it shows something much different (a higher number 35). How I can just get the number that is showing in the raw data without it creating a new number??
Overview: The formula in the raw data is showing differently in the pivot.
I have a pivot table, and I would like to be able to have a formula or vba to extract the data to separate sheets. The pivot table looks similar to;
Row Labels
Count of FileSemester
John Smith
8
[Code].....
The pivot table consists of employee name (John Smith), followed by class code and qty. I have a separate spreadsheet for each employee and what I need to be able to do is to extract the class & qty into these spreadsheets.
I can’t seem to work out the formula for calculating a ratio using the data in the pivot table.
I’ve added a column next to the pivot table to work out the ratio between to columns.
=SUM(B11/C11)
But what if there is no data in cell (B11), I want to return a “ “ (blank space)… but it returns a #DIV/0!
I like to keep it in a cell and rather not get into pivot tables.
I don't know if it can be done but I need two formulas that are described below. I am thinking maybe an array formula? ....
In this file Vehicle Fuel Tracking.xlsm I have a pivot table set up to filter my data. Next to the pivot table I have a column that Totals the Mileage based on the MAX and MIN of each group. I am looking for a dynamic formula to keep the totals alligned if data is added or deleted from the pivot table.
I would also be open to changing the data table to accomodate this request if needed.
My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).
The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:
Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))
The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".
[URL] ...........
I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.
I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.
Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.
I have the formula:
=VLOOKUP(C10,'[Ticket Out Comparison Report.12.4.11.xls]TO'!$C:$O,10,0)
I want to do something like putting 12.4.11 in cell B1 and change the formula to something like:
=VLOOKUP(C10,'[Ticket Out Comparison Report.B1.xls]TO'!$C:$O,10,0)
I'm having an issue with a formula that references another workbook, and I'm not sure what the culprit is.
The formula is this: =IF(ISERROR(E4/VLOOKUP(D4,'C:Documents and SettingsusernameMy DocumentsRoom Capacity Master List.xlsx'!Table2[#All],2)),"",E4/VLOOKUP(D4,'C:Documents and SettingsusernameMy DocumentsRoom Capacity Master List.xlsx'!Table2[#All],2))
It works just fine on the initial pass. When I reopen the book and it prompts if I'd like to update the data, and I select yes, everything goes away. Cells E4 and D4 are both part of a PivotTable, not sure if that is important or not. This is happening on Excel 2010 running on XP.
Just got a problem with auto-populating cells with a formula that is made up of variables. My formula references the cells on another sheet, but this will be dynamic.
I'm trying to get the formula to reference e.g. cell A1 in the Calls Taken sheet, with the column held in the srtshortname variable and the row held in the formularow variable.
Code:
set rng = range(cells(introwdate, formulacol), cells(introwdate, formulacol))
rng.formula = "='Calls Taken'!" & strshortname & "" & formularow & ""
I am having trouble with the following formula in my workbook with many sheets: ="='"&MONTH(A6)&"-"&DAY(A6)&"'!"&"$B$13". I entered this formula into B6 of the current sheet. Cells A6:A33 contains a date (02-Jan-07). So what I'm trying to do is retrieve the value in B13 of the sheet corresponding to the month and day in A6. If I hard code this formula into B6 ='1-2'!$B$13 it works but I don't want to edit each of the formulas in column B to correspond to the correct sheet. When I type in the above concatenated formula it just returns text (='1-2'!$B$13). Is there a way to do this so the formula will actually execute?
View 3 Replies View RelatedI've come across a problem I can't seem to solve. I have a cell that contains =AVERAGE(D8:AH8) which was working fine. I made some changes to my sheet and now D8 through AH8 contain a number arrived at through a formula ( for example =D6/D7 ) instead of just a hard number, and my average formula no longer works.
View 3 Replies View RelatedI'm a bit over my head on this one. I want a formula that does the following: Look at the date I put in on the last tab and find the correct date on the other tabs. Using that date as the column I want it to return the correct row for the data.reference.
I am using the HLOOKUP function. I'm not even sure this is the right function. Ont the workbook attached I'm trying to get the data on the Totals tab to come from the Sept Wk 1 through Sept Wk 5 tabs. The formula I tried to use is on the Totals page C7.
Im using the below formula to paste information into a new spreadsheet based on the if then statement. However, as i paste the formula down the sheet, it doesnt reference all the cells. The formula stays on the cells it referenced initially. How can I make the formula reference all the cells as i drag it down?
=IF('[All Cases ATL.xlsx]New_All_Case'!$C$2="DA1",IF('[All Cases ATL.xlsx]New_All_Case'!$T$2="A",'[All Cases ATL.xlsx]New_All_Case'!$E$2,""),"")
I have data in every cell within a column. I want to pull data from every 7th Cell.
Ex.
Cell CP7 needs to reference AG10
Cell CP8 needs to reference AG11
I'm having a problem with the below array. The column heading in cells E7:H7 Never change and i would like to sum up the appropriate data below those headings according to the what is in cell D4. The formula in cell A9 works but only if the string is exactly the same as what is listed in cell D4. Is there a way to reference cell D4 like i have in cell B9. I'm looking to get the formula in B9 to work, but i'm missing something.
Sheet1
ABCDEFGH2Assumptions 3Collateral Sample 1{60;90;FCL} MACRO Populates 4Collateral Sample 2{90;FCL} {60;90;FCL} 5Collateral Sample 3{FCL} 6Collateral Sample 4{60;90;FCL} 7 Current6090FCL8 990 123410200 658711360 4682212
Spreadsheet FormulasCellFormulaA9{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E9:$H9))}B9{=SUM(IF($E$7:$H$7=$D$4,$E9:$H9))}A10{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E10:$H10))}B10{=SUM(IF($E$7:$H$7=$D$4,$E10:$H10))}A11{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E11:$H11))}B11{=SUM(IF($E$7:$H$7=$D$4,$E11:$H11))}Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4