Reference To Name Range As Array From Another Sheet / Workbook
Jan 28, 2013
I have the below formula
=IFERROR(INDEX(Settlements!Account_No.,AGGREGATE(15,6,(ROW(Settlements!Account_No.))/(Settlements!Settlement_Date=TODAY()+1),ROWS(N$2:N2))),"")
I would like to copy it across and use different named ranges for the red part, hence have it reference to a cell with the name of the range.
i.e. (which doesn't work)
=IFERROR(INDEX(Indirect("Settlements!"&N1),AGGREGATE(15,6,(ROW(Settlements!Account_No.))/(Settlements!Settlement_Date=TODAY()+1),ROWS(N$2:N2))),"")
View 9 Replies
ADVERTISEMENT
Feb 27, 2014
My company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?
here is one of the equations I am working with:
{=TRANSPOSE('1st week'!A10:L48)}
so it would look something like this maybe
{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.
View 3 Replies
View Related
Jan 9, 2009
In a worksheet is a table:
a b c d
e f g h
i j h l
m n o p
q r s t
I'm looking for a procedure to (with the help of
an array) reproduce this table exactly under the original one
with one row between them but in the folowing form:
q m i e a
r n j f b
s o h g c
t p l h d
the procedure should be applicable on any haphazard
regardless of its place on a worksheet
View 9 Replies
View Related
Jun 24, 2014
I have attached what my problem is. Have a look, and let me know what you think...have spent hours trying INDEX, MATCH, IF, ISTEXT...
JamesExcel problem.pdfExcel problem.pdf
View 2 Replies
View Related
Jul 15, 2014
Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.
[Code].....
This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?
By using an array:
[Code].......
or
by using a range of cells:
View 8 Replies
View Related
Dec 31, 2007
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
View 9 Replies
View Related
Aug 19, 2009
Is it possible for the VBA code to capture the open workbook name and sheet name from a cell?
I've been trying to tweak this ...
View 6 Replies
View Related
Oct 7, 2013
Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?
This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.
View 3 Replies
View Related
Oct 5, 2006
I have two workbooks wkA and wkB. I have a named range in wkB called BName.. can someone tell me whether it is possible to set a range variable (say, raA) from wkA to this named range in wkB. I have tried something to the effect of set raA = wkB.range("BName") but this doesn't work.. I've also tried set raA = [wkB.name].range("BName") to no avail. I'm wondering whether named ranges can actually be read from other workbooks.
View 4 Replies
View Related
Nov 23, 2007
I'd like to create a list via Data validation-list. Anyone knows how to reference a range on another sheet of the workbook?
The range is =$Q$5:$Q$13
Is it possible to reference another sheet? If yes, how is is done?
View 9 Replies
View Related
Nov 17, 2011
How Could I change the below line to reference the sheet "NO" not just a range.
Set Source = Range("NO!A1:BK10000").SpecialCells(xlCellTypeVisible)
View 3 Replies
View Related
Mar 5, 2008
Im currently located in Sheet1. I want to reference a cell in Sheet2.Range("B2") using a R1C1 reference.
View 9 Replies
View Related
Jun 10, 2008
in my workbook i have 3 sheets named "time" "final" "push". in the sheet thats named "final" ihave in cells B2 and C2 2 values that correspond to a type interval (lets say 75-378) in the sheet "time" each row represents 1 sec, starting at one.
what i would like to do is locate the interval defined by the 2 values on "final" sheet
and find the min max and value in column S of the "time" sheet.
i.e if B2 and c2 said 78 and 378 it would go on "time" sheet select the range of S78-S378 and find the mean, the max and the average and report them in sheet "final" in cells E2 F2 G2 respectively What would be optimal is that there was a way to perhaps loop so that it can do the same thing with the range defined by the values in B3 and C3.
View 14 Replies
View Related
Jul 25, 2012
I want to know how to copy a range into an array then an array back into an range.
Code:
Dim a(3,3) As Double
a(3,3) = Range("C3:E5")
Range("C10:E12") = a(3,3)
View 1 Replies
View Related
Jun 6, 2014
I want see if it's possible to take the name of the source workbook and put it in a cell in my destination workbook and then use that as the reference in formulas. The source document title includes a date that changes yearly, or in some instances changes entirely, and I want to keep the formulas in the destination workbook intact. I don't want to have to go to every instance where I have used the source workbook and change the title. Instead I would like to copy the source workbook title, paste it into the destination in the same cell and have that propogate the needed changes into the destination workbook formulas.
View 2 Replies
View Related
Jun 19, 2014
I have data that is on a separate .txt file (the source file) that resembles this;
A
B
C
D
E
F
1
Case
District
Pct
Division
Level1
Level2
2
305035
0
20
72
[Code] ..........
Using VBA from within the destination workbook, I would like to use an input box that allows the user to request/specify what numerical number (0 through 55) in Column B of the txt file (the source file) will be used to determine what data to copy to the destination file. The cells to copy to the destination file would be from Column A through Column G of the source file onto Sheet1, E2 of the destination file.
Column B of the Source file will only contain numbers. However there will be numerous identical numbers in Column B. All of which will need to be copied onto the destination workbook.
From the sample above, if the user were to enter '15' in the input box, the desired result on the destination sheet beginning at E2 would look like this;
E
F
G
H
I
J
1
Case
District
Pct
Division
Level1
Level2
2
502046
15
1
3
[Code] ............
View 9 Replies
View Related
Jul 14, 2014
Is it possible to reference a cells value to define a range reference?
[Code] ......
I am trying to define the row value in the range reference with a value in a secondary cell?
View 3 Replies
View Related
Jul 20, 2014
I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
View 2 Replies
View Related
Feb 11, 2010
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.
View 9 Replies
View Related
May 27, 2009
Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...
View 2 Replies
View Related
May 4, 2006
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.
View 3 Replies
View Related
Aug 6, 2012
I have a workbook "Weekly Field Report-Master Copy" and another workbook "Weekly Field Report $$$-Master" The "Weekly Field Report $$$-Master" workbook has all the formulas that pull data from the "Weekly Field Reports-Master Copy" workbook and populates a financial report of that week's field unit production.
When I save the "Weekly Field Report-Master Copy" workbook to a new name "Weekly Field Report Job #1" and then save the "Weekly Field Report $$$-Master Copy" workbook to a new name "Weekly Field Report $$$-Job #1" is there a way to automatically change the formulas in the "Weekly Field Report $$$-Job #1" workbook to now pull data from the "Weekly Field Report Job #1" workbook instead of the "Weekly Field Report-Master Copy" workbook???
Currently I am manually changing the formulas in the new workbook "Weekly Field Report $$$-Job #1" to reference the data in the new workbook "Weekly Field Report Job #1". Otherwise the formulas pull the data from the "Weekly Field Report-Master Copy".
View 4 Replies
View Related
May 27, 2009
Sheet 1 contains:
Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)
Sheet 2 contains:
Item Data
ABC 2
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.
View 3 Replies
View Related
Aug 7, 2013
I have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
View 9 Replies
View Related
Feb 11, 2014
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:
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],{"January","February","March"}))
BUT! When I try to use a formula which points to the VLOOKUP cell (N10)--thus allowing me to make the display dynamic--the result is always 0.
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10))
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?
View 6 Replies
View Related
Mar 4, 2008
I am trying to figure out a method for calling the ith number in an array that was defined in another function. The mean function is working and the result is (1 x variables) array. Then, I want to use that array in the sdev function. I am having trouble pulling the ith number from the mean function. Also, is there a way to make variables constant so that they do not need to be declared for every function.
Sub stats()
periods = Range("periods")
variables = Range("variables")
Redim X(periods, variables) As Double
Redim uX(variables) As Double
Redim sdX(variables) As Double
'Load Data
For i = 1 To variables
For t = 1 To periods
X(t, i) = Cells(4 + t, 2 + i)
Next
Next
'Calculate Mean (run 'mean' function')
uX() = mean(X)................................
View 7 Replies
View Related
Jun 25, 2014
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
View 9 Replies
View Related
Jul 1, 2014
The two sheets are...
1. Sheet1.xlsx (regular excel sheet as the extension is .xlsx)
2. Consolidated.xlsm (macro enabled sheet as its extension is .xlsm)
1. Copy both the files on your system under a particular folder.
2. Now make 9 replica's of Sheet1.xlsx and name them as Sheet2, Sheet3, Sheet4, .......... Sheet10
3. Open the sheet Consolidated.xlsm and see the button i have added called "Pull Data".
Now write down the code by adding a Module in that excel sheet from the code window.
4. Write a code that will open each of these sheets (Sheet1, Sheet2, Sheet3, Sheet4, .......... so on) one at a time and will copy the data from these sheets to the Consolidated.xlsm sheet.
5. Make sure that the data is appended(and not overwritten) from the multiple sheets (Sheet1, Sheet2, Sheet3). that is once you paste the data from sheet1 to Consolidated.xlsm then the Sheet2 data will be pasted at the end and after that sheet3 data will be pasted and so on..
6. In this way at the end we will have all the data from Sheet1, Sheet2, Sheet3...in the consolidated sheet.
7. The Division column in the Consolidated sheet will have the value of first row in these multiple sheets. So after making the replicas of Sheet1, please change the value in first row (Range A1) to any other value to avoid the confusion.
8. I have highlighted the data for two sheets in yellow and grey color in the consolidated sheet.
View 8 Replies
View Related
Nov 1, 2009
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
View 2 Replies
View Related
Jul 14, 2009
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
View 14 Replies
View Related
