Indirect 3-D Range Summation
Dec 5, 2006
I was trying to assist someone with 3D referencing and summing, but getting stuck on referencing text based sheetnames.
We are trying to sum range A1:A10 in the sheets between the range defined by A1:A2
Now, if the Sheet names were numerically sequenced, eg. Sheet1, Sheet2, etc...then we could use this formula with no problem
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(A1&":"&A2))&"'!A1:A10"),""))
where A1 and A2 housed the numbers 1 and 2, respectively,
but what if the sheets are text based? Is there a way....say the Sheets were named SheetX, SheetY, SheetZ....
I did some searching on the internet and found samples only with the numeric based sheetnames.
View 10 Replies
ADVERTISEMENT
Mar 6, 2009
In cell A1 I have the text Testme
Testme is a Named Range
with RefersTo set to:
=Sheet1!$D$3:$D$5
In VBA - How do I
Range("A1").copy Range("D4").PasteSpecial xlPasteValues
And D4:D6 will show
123
456
789
This thing is beating me up -- Indirect is likely involved, but,,,
View 9 Replies
View Related
Aug 18, 2009
I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
View 3 Replies
View Related
Dec 27, 2012
In the example I have attached there are two sheets, Details and DataTables. There are also a number of named ranges, both static and dynamic, that refer to data on the DataTables sheet. Most of the static ranges were added for debugging purposes. The tables have been shortened for this example.
On the Details sheet column A has a drop down that allows you to select the make of a device. Column B builds its drop down list based on what is selected in column A. The data validation in B2 uses a named dynamic range and the drop down does not work. The data validation in C2 used a named static range and the drop down works.
Column C will fill in the RU value based on what is selected in column B. Cell C3 uses named dynamic range and it does not work. Cell C4 uses named static range and it works.
I would like to used named dynamic ranges if possible so when data is added to the tables the named dynamic ranges will self adjust. Can what I want to do be done?
Dynamic Range.xlsx
View 8 Replies
View Related
Oct 9, 2003
I'm trying to use Indirect in a formula where named range refers to multiple non adjacent cells; e.g. ever other cell {A1, A3, A5... etc}; however, when I use indirect, I recieve a #ref error. Replacing the indirect with the actual named range will make the formula work just fine, or using a named range that a continuous range works fine as well. It seems that indirect doesn't like non continuous ranges. ?
View 6 Replies
View Related
Jun 29, 2012
$TC$2:$WX$3
is an indirect range that resides in cell B15. It is constantly changing and the expectation is that the X_Y plot would adjust accordingly. It represents the data range of the chart. The chart does not carry with it any title.
So how do I approach this without using vba? As always any input is highly valued.
View 2 Replies
View Related
Jan 18, 2013
I have the simple code below to perform and filter and copy:
Code:
Sub Macro1()
Sheets("Data Dump").Select
Range("A1:F100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"K1:L73"), CopyToRange:=Range("P1:U1"), Unique:=False
End Sub
However, the CriteriaRange can change depending on which OptionButton the user chooses on a different tab. My reference the range is in Cell N1. So how can I use Indirect to refer the range in Cell N1 instead of the current CriteriaRange of K1:L73?
View 5 Replies
View Related
Jan 10, 2008
I have a formula: ='Jan 7-11'!D10. I want to replace the tab name 'Jan 7-11' to change based on the content of F6 (which is a data validation for all tabs in my workbook).
View 3 Replies
View Related
Feb 13, 2008
I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.
VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"
where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.
View 9 Replies
View Related
Apr 30, 2009
How would I used the indirect function to reference a named range in the workbook in formula creation.
For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:
=sum(indirect(A1))
In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.
I'm using Excel 2007.
View 9 Replies
View Related
Jan 17, 2014
I am trying to use the INDIRECT function to look up values on a range of other worksheets. I have a column of data in col A which is essentially a lot of different worksheet names. On each worksheet I need to use VLOOKUP to find a value.
Easier to show formulas. This is an example of what I want to recreate:
=VLOOKUP(O2, test!B3:C13,2,FALSE)
I want to replace the text "test" with the text in column A. So the first INDIRECT formula looks like this:
=INDIRECT("VLOOKUP(O2, " &A1& "!B3:C13,2,FALSE)")
Which to me looks like it should work but I get a #REF! error. I've tried various permutations, e.g. having the INDIRECT part of the formula located in a different place (next to the "test" text) but run in to similar issues.
View 5 Replies
View Related
Feb 11, 2012
The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))
B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.
Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).
The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.
I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]
View 5 Replies
View Related
Jul 5, 2012
B1=Name of WorkSheet
B2=Name of 2nd Worksheet
I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.
=SUM(INDIRECT("'"&B1&":"&B2&"'!F5"))
with some added spaces for clarity:
=SUM(INDIRECT(" ' " & B1 & " : " & B2 & " ' ! F5"))
View 9 Replies
View Related
Dec 22, 2008
How can I summate cells in a column *except* for those rows which contain a specific value in another column?
=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.
View 3 Replies
View Related
May 19, 2009
I have written an SQL Stored proceedure which drags this information into excel.
All is well here, however because the information changes regularly adding and subtracting in row length it means I am having issues writing code for sums as a standard macro is absolute and not realative.
What I basically have is this ....
View 7 Replies
View Related
Jun 26, 2007
I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
View 10 Replies
View Related
Dec 17, 2008
I try to add some numbers, it will display ### symbol rather than that Value.If i give Summation for 10+10+10+10+10 the result displays ### than 50. How to resolve this?
View 15 Replies
View Related
May 25, 2009
how to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.
View 9 Replies
View Related
Aug 15, 2013
Attached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
View 9 Replies
View Related
Jan 10, 2014
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
View 4 Replies
View Related
Oct 8, 2008
I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.
Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation:
Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.
View 4 Replies
View Related
Jan 11, 2009
I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...
What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data
View 2 Replies
View Related
Jul 13, 2009
I'm trying to generate random numbers that will add up to a certain number and am having trouble brainstorming how to do so. For example, I'm trying to get a+b+c=6 with a, b, and c randomly generated to add up to 6.
View 4 Replies
View Related
Jan 23, 2012
Say, I have two columns each having unknown number of rows. what would be the vbscript code to get the summation of a column's values
For example,
Two existing columns are A1 and B1. In order to create C1.
Column C = 10/summation(a1)/(b1)
How to do the above operation?
View 6 Replies
View Related
Apr 13, 2009
Currently I am trying to do a gantt like chart time summation. I'll try to explain this as clear as possible... Right now I have a program that finds the name of a part in a giant list's column and then copies a few of the other columns (priority and hours to complete) from the part names row and pastes them into a new sheet. I then sort all the found data by their priority number. The job that is gets sorted to the top is the next job to be worked on for the machine. So here is what I need to be able to do...
Based on the following example I need to be able to sum for the object bolt on Machine #1 the tenative date that the "bolt" will be completed by. It will take a minimum of 35 hours for the bolt to get off Machine #1 based on its current priority. However, the part: "bolt" needs to go to Machine #2 before being completed. The part will have to wait a hour for "hammer" before being started on machine 2. (Its data for the time it will take complete "bolt" on machine 2 is also stored in the spreadsheet.) Since the bolts priority is higher than the "nail"... the "bolt" will be the next job to be completed. My question is this: Is there a way to tenatively sum the future hours that it will take to complete the bolt and allow it to be open to changes, in case a higher priority part comes into the system?
Machine #1
PRIORITY HOURSNAME123nut212bolt334washer422kazoo
MACHINE #2
PRIORITY
HOURSNAME122screw22hammer31nail
View 9 Replies
View Related
Jun 30, 2014
I was trying to use IF and AND conditions to calculate values for a Col. but when I attempt to sum.
It appears that the value is always "0". Below is my test data.
Col. H Col. J
Qty Result
Row 713 0 <-Excel Formula: =IF(AND(H7>20,H7<=50),"1","0")
Row 825 1 <-Excel Formula: =IF(AND(H8>20,H8<=50),"1","0")
Row 960 0 <-Excel Formula: =IF(AND(H9>20,H9<=50),"1","0")
Row 10Total 0 <-Excel Formula: =SUBTOTAL(9,I7:I9) / =SUM(I7:I9)
View 3 Replies
View Related
Feb 15, 2009
I have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.
I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.
Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??
View 2 Replies
View Related
Apr 4, 2009
I have a sheet with about 1500 hundred lines of data (The number always changes but is around there.)
The data consists of numbers separated by date with headers.
What I am looking to do is add the numbers offset by two rows of alike headers and have the result output to Sheet2.
These results will be totaled / Separated by day.. Date identification is listed in a few places on the sheet.
Then, I need to add the numbers offset by THREE rows of alike headers and have the result output to Sheet2. The output sheet will be properly listed by rows and labeled by appropriate header for the total.
I have attached a worksheet example to better conceptualize. Please let me know if further information is needed. I have an idea of somewhat to do, but don't know how to put it all together. This is the last piece to the program which I have been working on for 2 months now.
View 13 Replies
View Related
Jan 26, 2014
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #
Lap Time
Threshold
2 or more laps
0
118.2
85
118.2
[Code] ........
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above,
I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
View 1 Replies
View Related
Oct 25, 2007
I need a formula which can sum up some value based on some especial pattern which I indicate in the table below:
ABC
12565
21001542
33850
4
5
My question: I want to sum up value from A1, B1, C1, A2, B2, C2. I also need a formula to control how much I want to add. For example, I can instruct the formula to sum A1, B1, C1 and A2 (25+65+0+100) or sum A1, B1, C1, A2, B2, and C2.
View 9 Replies
View Related
