Values Based On Row References
Mar 10, 2007
If I have the spread sheet
........Month Deal Name Deal Size
Row1 May
Row2 DrewDeal 50
Row3 DanDeal 100
Row4
Row5 June
Row6 FankDeal 60
Row7 MaryDeal 200
I want to know can I output into another sheet the values based on the month. So I would have a drop down window with the months, and if I selected May, all the data from Row1 to Row 4 would show up. If I selected June all the data from Row 5 to Row 7 would show up
If I have the Same Table, I want to find out the Sum of the Deals in the months, so I want to create a table that shows the months, and then the SUM of the Deal Size Column.
View 2 Replies
ADVERTISEMENT
Aug 13, 2012
I'm trying to put together a dashboard that is referencing a table. Now the dash board I would like to have it so I can change a value and the details will update based off of my selection. This is all drived off of named ranges and have that working. The part I don't have working is dynamically changing what part of the table is being referenced to.
i.e. Say i have modeling quality I have a formula that looks like this
=SMALL(IF(Table1[[#All],[Modeling Quality]]=$D$12,ROW(Table1[[#All],[Modeling Quality]])),ROW($M1))
Which works perfectly. However if I change modeling quality to say modeling experience which has different criteria i need the formula to look like this
=SMALL(IF(Table1[[#All],[Modeling Experience]]=$D$12,ROW(Table1[[#All],[Modeling Experience]])),ROW($M1))
Is there a way I can drive the table reference from a cell selection? I've tried referencing them by manually typing in the cell address to no avail.
View 3 Replies
View Related
Aug 9, 2009
If I have values in cell B5:B8
B5=0
B6=3
B7=7
B8=10
I want to have a standing formula in Cells J2:M2 that will always reflect whats in those cells. I don't want to use transpose nor do I wnat something as simple as J2=+B5,K2=+B6,etc.
I would like to know if there is a formula that would add the values in a certain row to a cell
For instance:
I want J2 to reflect what is in B5 AND I want K2 To reflect what is in B5 plus a row which would be the value in B6. I have a reason for doing this as I am going to import 120
ranges beginning at a1 to I30 on each sheet while having the sheet I am importing to have these formulas starting at J2.
I tried using +cellnumber+row, but it just gave me the row number and not the actual value in that cell.
View 9 Replies
View Related
Apr 27, 2014
Is it possible when copying and pasting a formula, to see the values from each cell rather than the cell reference?
View 12 Replies
View Related
Nov 19, 2009
I want a total of column F based on criteria in column D and column K from the worksheet named in cell C9. I have put in the indirect function in the first part but am messing up the syntax in the second and third part of the formula. "Indirect(C9&" should be where 'Oct2009' is shouldn't it. Where am I going wrong with the following formula?
SUMIFS(INDIRECT(C9&"!$F$2:$F$11000"),'Oct2009'!$D$2:$D$11000,$C$1,'Oct2009'!$K$2:$K$11000,"YOKO")
View 9 Replies
View Related
Apr 17, 2008
Is there a way of writing a formula to create a reference to an external spreadsheet? I have a folder containing 100s of workbooks all based upon a template. I can create a directory listing of this folder using a macro within Excel.
e.g.
Directory listing
C:My DocumentsReturnsFile1.xls
C:My DocumentsReturnsFile2.xls
C:My DocumentsReturnsFile3.xls
I would then like to use this list of file paths to create links to several different cells within worksheets contained in each of these. I know that if I did this manually for each worksheet the formula would be a variation on =[C:My DocumentsReturnsFile1.xls]Sheet!Cell. Is there a way that I can automatically create these external references? i.e using values in column A (directory listing) to create many external references, say in column B
I use my directory list and then a formula or macro populates automatically these external references? The file names in the directory change daily (100s of worksheets!) so I don't want to have to manually input these each time. If the folder containing the workbooks was held on an intranet would there be a similar solution?
View 2 Replies
View Related
Feb 21, 2014
I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.
View 1 Replies
View Related
Oct 22, 2009
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
View 9 Replies
View Related
Feb 10, 2014
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
View 1 Replies
View Related
Nov 25, 2007
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
View 9 Replies
View Related
Mar 9, 2009
I am trying to create a calculator in excel that has five parts. I want to manipulate each part based on the other parts. I cannot figure out how to remove the formula and replace it with the derived number without a circular reference. The circular references slow down the main macros of my program.
View 2 Replies
View Related
Sep 26, 2007
I have a pivot table which draws data automatically from a database
What I would like it for the customer field of the pivot table to only equal the customers which are present in another worksheet (Column A:A)
View 9 Replies
View Related
May 20, 2008
Here is what I am trying to achieve:
I have a workbook with two tabs, on the first one there is one column and the top of the column(A1) is a dropdown containing the options 'Boy' or 'Girl'.
One the second worksheet I have two named tables (Boy and Girl) - these tables each contain a single column array with 5 names in each (so we have a table of 5 boys names and a table of 5 girls names)
What I want to happen is when a user selects an option from the dropdown in A1 the cell below(A2) is populated randomly with a name from the corresponding list.
I have achieved this initially using the following formula:
=IF(A1="Boy", INDEX(Boy,RandInt(1,10)), INDEX(Girl,RandInt(1,10)))
This works fine, but I will need to expand this for several dropdown options, so my new workbook will have a dropdown of car makes, and each make will have a list of the models associatied with the car make (e.g. Ford: Fiesta, Escort, Mondeo...)
But I do not want to have a ridiculously long nested IF statement for every car Make, so what I want to do is something along the lines of the following:
=INDEX([CONTENTS OF A1],RandInt(1,10))
So I use the text selected from the dropdown in A1 as the TableName in my formula - however, I cannot retrieve that to use in the formula without it coming back as a text with the "" included and my formula errors!
View 9 Replies
View Related
Jul 19, 2014
Using Excel 2013.
I have two sheets in a workbook.
Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result
Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:
Mod 1
Mod 2
Mod 3
Mod 4
Text 1
0
0
0
0
Text 2
2
0.5
0.25
0.25
Text 3
7
1.75
0.875
0.875
What I want to happen is that the Result field value on table one is the result of:
Distance*(Modifier 1 + Modifier 2 + Modifier 3 + Modifier 4)
For example:
On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:
6*(7 + 0 + 0 + 0)
This might be exceedingly simple, but I just cannot wrap my head around how to do it.
View 1 Replies
View Related
Mar 2, 2009
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
View 9 Replies
View Related
Jun 30, 2014
Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:
Code:
Sub LoopthroughWorksheets()
Dim sheet_name As Range
Dim sheet_name2 As Range
Set sheet_name2 = Sheets("WS").Range("F:F")
[Code] ......
I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:
Code:
.Range("K1") = .Range("sheet_name2.Value!A14").Value
View 2 Replies
View Related
Mar 25, 2013
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
View 1 Replies
View Related
May 22, 2014
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
View 4 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
Feb 17, 2010
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.
Example:
On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.
I have attached a sample spreadsheet for clarification.
View 7 Replies
View Related
Aug 31, 2012
I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies
View Related
Aug 14, 2008
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"
Below is a small sample of data, real data is around 100 rows
Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
View 9 Replies
View Related
Sep 25, 2013
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
View 1 Replies
View Related
Feb 24, 2009
The simplified example below is intended to calculate the sums from the data values in the upper data set and place them in the appropriate cells in the lower summary table. Example......
I would like to find a single-cell formula that will handle the multiple instances of either the X or Y headings (ex: there are two 'Yes' Rows) and still reach the appropriate summations. I would like to accomplish this without resorting to Macros or manual cell references if possible. I've acheived some limited success with array formulae so far, but find that the multiple instances of 'Yes' cause only one row to be summed before the formula finishes.
View 2 Replies
View Related
Apr 21, 2014
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
View 8 Replies
View Related
Aug 7, 2013
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies
View Related
Jul 7, 2013
I'm attempting to assign 1 of 4 predetermined values to a cell based on the values of entries in another cell.
View the example attached : Value assignmts.xls
Assign the Value of 1, to (D8) when any value entered in (C9) is equal to or over 380, but less than 410.
Assign the Value of 2, to (D8) when any value entered in (C9) is equal to or over 410, but less than 440.
Assign the Value of 3, to (D8) when any value entered in (C9) is equal to or over 440, but less than 470.
Assign the Value of 0, to (D8) when any value entered in (C9) less than 380.
View 9 Replies
View Related
Apr 25, 2006
I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:
=SUMIF(D2:D110,D112,E2:P110)
My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?
View 11 Replies
View Related
Dec 19, 2013
I have a table of times which shows how long it took someone to process a form. If this value is longer than 10 minutes the cell next to the duration time contains the value 'Break Over Ten'. This is only true when the time is outside of 12:00 to 14:00. If the break is within this time period it shows 'Lunch'.
Is it possible to sum all of the time values that are over flagged as 'Break Over Ten'? This is how it looks
Event Date Start Time End Time Duration Breaks Duration Of Break
19/12/201308:43 08:43 00:00:35 00:00
19/12/201308:43 08:44 00:01:03 00:01
19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13
19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13
I was thinking of using SUMIF but can't work out how to total the values in the duration of break column when the cell to the left reads break over ten. Is it possible to do this?
View 1 Replies
View Related
Jan 1, 2014
I have a budget spreadsheet as follows:
Category, Sub-Category, Amount
Food, Lunch, 10
Food, Dinner, 20
Food, Lunch, 15
Food, Dinner, 30
I need to track how much was actually spent on various categories and sub-categories, as follows:
Category, Sub-Category, Budget Amount, Actual Amount
Food, Lunch, 50, 25
Food, Dinner, 90, 50
Essentially, the actual amount contains a set of formula, that sums up the spending sheet (the one on top) based the category and sub-category on the budget sheet (the one on the bottom). Tried to use SUMIF but it seems to work on a single column.
View 4 Replies
View Related
