I have a report for which I would like to set up a Quarterly summary section. There are 6 working teams listed in my table and I have created two cells with data validation to allow me to specify which team and for which quarter I would like to pull data. My intent is to have a formula in one cell which can extrapolate the quarterly team information based on the values in the data-validated cells.
My worksheet is set up so that:
The data table (tblD) contains metrics for all teams for the full yearTeam names appear in Column A of the table and the reporting month appears in Column BCell M10 has data validation so that the only choices are: Q1, Q2, Q3, or Q4Cells X1:X4 contain the quarterly values mentioned aboveCells Y1:Y4 contain array references, so that Cell X1 = Q1 and Cell Y1 = {"January", "February", "March"}Cell N10 contains a VLOOKUP which returns the appropriate array from Column Y based on value in Cell M10Cell L11 has data validation so that the only choices are the 6 team names
Now I was able to get quarterly information by manually typing the 3-month array into my formula like so:
While inspecting the formula in the formula bar, I noticed that the values listed in the array appeared with double quotes (i.e. "{""January"",""February"",""March""}"). I tried every permutation of typing the array in the lookup cells I could think of, but none would allow my formula to properly calculate the total I needed.
{"January", "February", "March"}
{January, February, March}
January, February, March
Although I don't often utilize arrays in my formulas, I do employ them from time to time. I'm hoping there's some factor I might have overlooked which keeps my formula from working. is there an issue preventing array references stored in cells from being used in formula?
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
I have the below formula working correctly. However the issue is when it pastes and fills down values its using H1 reference in the formula even when it goes to the next column. So basically when it goes to cell I2 it should update the sumifs formula to lookup I1 instead of H1 and so on and so forth for all columns till the loop stops.
VB: Sheet1.Activate Dim frmla As String frmla = "=SUMIFS($C:$C,$A:$A,H$1,$B:$B,$G2)" Range("H2").Activate Do Until ActiveCell.Offset(-1, 0) = "" ActiveCell = frmla ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1).Activate Range(ActiveCell, ActiveCell.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, 1).Activate
i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.
I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup
I'm setting up an Excel (2000) sheet to record the results of a competition at my local photographic society. I'm trying to make it as simple as possible to fill out by the people using it, so have shaded the cells that require the user to put data in. I'll post a screenshot of the sheet, which is named "DPI", to make it easier:
(EDIT) screenshot removed - see attached file in post #3 below instead
I use one row for each member entering the competition. Each member can submit up to three photographs for judging. The names in B5:B30 are tied to the member number in A5:A30, and pulled in with VLOOKUP from a separate sheet containing all the club's members. The names in this example are, obviously, fakes. ;-)
The three blocks are to enter the image titles and associated scores. I have a range called "scores" which is defined as =DPI!$D$5:$D$30,DPI!$G$5:$G$30,DPI!$J$5:$J$30
The rank columns are simply calculated using the RANK function. E.g. the formula in E5 is =IF(ISBLANK(A5),"",RANK(D5,scores,0))
The cells at the top are calculated using the LARGE function upon the defined range "scores" (although they could equally use the SMALL function on the rank columsn instead).
The bit I'm stuck at is what I want it to say along the top. eg: "Leg 1 Winner: 30 is {insert image title} by {insert member name}"
Using the winner as an example, I want to take the winning score shown in D1, find it in the named array "scores", and return the image title to the left of it. Obviously I can't use VLOOKUP as the data is to the left of the lookup value as well as being in multiple columns. I wanted to use OFFSET to return the value of the column to the left, but to do that I need to pick up the reference of the cell containing "30" i.e. the winning score shown in cell D1.
How can I find this cell reference? Or is there a better way? I thought about the old MATCH/INDEX function, but INDEX doesn't seem to work very well with data in non-contiguous arrays as I'd have to specify which block to look in.
Once I can get the image title returned I hopefully sholdn't have any bother extending it to return the member name from a fixed column, but I can't figure out how to get the reference of the cells in the "scores" array that contain the winning scores shown in D1, D2 and D3.
I find myself needing this often and hope there is an elegant formula that can make this easier.
Is there a formula that will test if a particular cell is referenced somewhere in another array or vector? Specifically, I find that I have to aggregate long lists into categories to fit budget formats of various lenders and investors. For example, my detail budget has separate rows for Water, Sewer, Garbage, Electrical, and Gas. These expenses have to be aggregated on one funder’s budgets as “Utilities.” Sometimes after going through this I find that my totals don’t add up, i.e. I left an item out of the aggregated budget. I would like to be able to add a column on the detail budget to test if each budget item has been referenced in the aggregated budget.
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
I'm trying to pull thesum of huge number of columns in this case the range of pulling the data is B2:E7 based on two criteria (B8 and B9) The result is on B11
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D 1 Task___________Trigger_____________Days_____Due Date 2 Design begins__Proj OK______________10____10-Jan 3 Specs written__Design begins (A2)____5____15-Jan (D2+C3) 4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
In my attached spreadsheet example below, in the Rollup Data sheet, I have a formula (Cell: C17) that will give me the actual cost totals that has been invoiced on WBS Element (DWRRI-BW096-231) for a total of $35,004.81.
What I need the formula do is run a redundant check procedure on my new WBS Element (DWRRI-BW096-231) actual cost total formula in the Rollup Data sheet (Cell: C17) against/vs. what the SAP system provides in the raw SAP Excel export contained in the PTD-Actual Cost sheet for the WBS Element (DWRRI-BW096-231) actual cost total of $35,004.81 (Cell: F9) that's highlighted in the yellow row.
In the Rollup Data sheet (Cell: C18), I need this formula to perform a similar function the as the previous formula above it (Cell: C17), but it this formulas function (Cell: C18) would have a separate operation that would only look for and return the single/sole WBS Element (DWRRI-BW096-231) actual cost total contained in the PTD-Actual Cost sheet (Cell: F9).
In the Rollup Data sheet I could then compare my two WBS Element (DWRRI-BW096-231) actual costs totals, the first from the automated addition of all the individual matching WBS Element actual costs into an actual cost total (Cell: C17), the second (new formula I'm requesting) from the matching and return function of the single/sole WBS Element actual cost total (Cell: C18).
So, if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both equal $35,004.81, the formula in C17 is a success and has correctly identified and added all the actual costs contained in the PTD-Actual Cost sheet, but if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both do not equal $35,004.81, the formula in C17 failed for some reason and has not correctly identified and added all the required actual costs contained in the PTD-Actual Cost sheet.
How to Add Total $ Value Formula into an Existing SUMIFS Formula.xlsx
I am trying to use the SUMIFS function to sum the cells in one column if the cells in a reference column ARE blank, and if the cells in a different reference column fall within a date range.
-Column F contains currency, and is the sum range -Column H contains dates as well as blanks, and I need the criteria to look for cells that ARE blank. -Column A contains dates, and I need the criteria to be <= 01 Feb 2013 and > 31 Dec 2012
This is the formula I have tried, but it keeps adding the cells in F if the cells in H ARE NOT blank. Clearly, it's an issue with "<>" and probably a very simple solution, but I just can't figure it out.
I have a number of cells, with a rule as follows, but within each cell the company name and month vary, over 836 cells, so I cannot do a find and replace so wanted to know if there was a way of adding the following part to the rest of the formula within the 836 cells in one go, rather than tediously going through each cell and copying/pasting.
The part I would like to add to the various cells - 'Master Quote Sheet'!$H$18:$H$6021, "Won"
Existing formula (the varying parts within the different cells are the "02.2014" and "Company Name" =SUMIFS('Master Quote Sheet'!$J$18:$J$6021, 'Master Quote Sheet'!$E$18:$E$6021, "Direct", 'Master Quote Sheet'!$A$18:$A$6021, ".02.2014", 'Master Quote Sheet'!$D$18:$D$6021, "Company Name")
The range NFCU!$A$5:$A$300 is a column of dates. The evaluation of "<e1" does not seem to evaluate. However, when I change "<e1" to a date value of 41760 it works. How can I reference a field rather than the raw value?
On the attached work book I have 2 sheets on the total minutes sheets I need to run either a VBA or sumif formula that looks at all the Init columns and if it finds that name then it sums the duration cells corresponding with that name.
lets say.. i want every cell counted which has in these range the first two numbers..
every cell has..
5052 5053 5054 4060 4050
so i count the entries which has 50.. so in total it gives a result of 3. Not summing them, just count the entries that has the first two numbers "50" in every row..
The Red Markings can be replaced, the rest should stay.. it is needed due different criteria.....
Need convert the following formula so that it would work in Excel 2003: =SUMIFS($BB$4:$BB$500,$E$4:$E$500,"Abbeywood First",$L$4:$L$500,"Autumn"). I could play around with it for hours and get nowhere, so I'll just turn to the experts.
The formulas work fine if the external file is open. However if the linked file is not loaded and I load the file with the external links, as soon as the file with links is calculated, some of externally linked cells turn to "#VALUE" errors. I have a need to have only certain people see the employee file that is referenced above but the others need to update the file with the external links and not have errors in cells.
The really weird thing is that some cells yield the #VALUE error and some don't, even when both have SUMIFS() functions in them. Even cells with the Exact same formula in them will sometimes yield #VALUE and other times yield a real value.
SUMIF and SUMIFS formula, where in I want to set criteria in the formula as greater than or less than or equal to value derived from another formula. I am inserting the following formula but excel is not allowing me to enter the formula.
Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.
However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.
I'm exporting data from an access database into an Excel document and want to use the Sumifs formula to add up weights between a defined period (we have to show tonnages on a weekly basis).
The data and calculations are on a separate worksheets. The data looks like this:
[URL]
I'm trying to sum the data in column E based on the date range in column A and my formula is
I have a large metrics spreadsheet that has thousands of formulas using SUMPRODUCT. We are using Excel 2007/2010 now and I wondered if there was a way to convert these type of formulas to use the SUMIFS or COUNTIFS. I can do simple ones with 1 or 2 criteria where its AND, but then there is a combined OR and AND like this one has I cannot get them to work right, am I stuck with using SUMPRODUCT? My hope was to speed up the overall calculation speed. Note: The data spreadsheet 'Sheet2' contains 64555 rows, I write these formulas using VBA so that the range is finite to the actual data range is why you see 64555.
