Indirect Sum Across Multiple Worksheets
May 24, 2007
I have the exact same problem as was posted in the past, in this post:
http://www.ozgrid.com/forum/showthread.php?p=231189
I am trying to sum across a dynamic range of worksheets: sum(sheet1:sheet3!A1). I would like to have "sheet1" and "sheetX" in a separate updateable cell and refer to these for the sum:
Basically, do something like:
Cell A2 = Sheet1
Cell A3 = Sheet3
sum(concatenate("Sheet1:":"&indirect(A3)&"!A1"))
When I use the following equation I get a "#REF" error(posted as a solution in the earlier thread):=SUMPRODUCT(--N(INDIRECT(" Sheet"&ROW(1:3)&"!A1"))) what is the "--N" for. Is there something that I should add?
View 8 Replies
ADVERTISEMENT
Sep 4, 2009
I am trying to sum through multiple worksheets but maintain flexibility using INDIRECT but it is not working!
I have a worksheet for each month of the year Jan - Dec with a financial result. In order to get a Year To Date figure I would have a formula such as:
=sum(Jan:Jul!B3) for a July YTD.
However, I want to maintain flexibility such that I can enter the worksheet name in cell A1, e.g. Sep and then have a formula such as:
=sum(INDIRECT("Jan:"&A1&"!B3"))
Thus allowing me to generate the correct YTD at any point. All I get is a #REF error.
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
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
May 14, 2012
I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
View 4 Replies
View Related
May 16, 2014
I am building an attendance spreadsheet and trying to copy and paste all of the names from Sheet 1 to Sheet 6 for a different purpose. Thing is I need the names to match so I'm not having to copy and paste every time my data changes.
My problem was that when I insert a row into sheet 1, sheet 6 accommodates and then I'm missing data and have to insert a new line manually.
At first I tried using absolutes ($) to fix the problem, but that's a different ballgame.
I've discovered INDIRECT and so now use this formula: =INDIRECT("'DIRECTORY 2014'!B5"). It works.
But I have almost 300 entries. Is there a way to expedite the process without having to change each entry? I don't want to have to retype the function on every cell.
View 9 Replies
View Related
Jan 19, 2009
My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:
=SUM('Jan-04-08:Jan-16-08'!A1)
But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:
=SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))
where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.
View 9 Replies
View Related
Oct 9, 2008
=SUM(INDIRECT("'Sheet2'!$C$1:$C$5"),INDIRECT("'Sheet3'!$C$1:$C$5"))
These do not:
=SUM(INDIRECT({"'Sheet2'!$C$1:$C$5";"'Sheet3'!$C$1:$C$5"}))
=SUM(INDIRECT("'"&Sheets&"'!$C$1:$C$5"))
Where Sheets refers to =Sheet1!$A$1:$A$2
[A1=]Sheet1, [A2=]Sheet2
For the last two ONLY Sheet2 is summed, not sheet3
View 5 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 9, 2013
I am trying to stream line the recording of time tickets . I would like to create a Summary sheet that shows the amount of hours each worker has done based on that particular day. The trick is I want the summary sheet to be automatically updated when the each individual time ticket sheet is completed. I would also like to be able to drag the formula across a row so I do not have to recreate the equation each time a new time ticket sheet is completed. Each time ticket sheet is its own sheet and is identified by date. Each time ticket ticket sheet is identical and is already created to so all you have to do is click on that the sheet reflecting the date and fill in the information.
This is the equation I have created but keep getting #VALUE!
=SUMIFS((INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$L$11:$L$34")),(INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$B$6")),('Data (2)'!J$8),(INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$C$11:$C$34")),('Data (2)'!$I$9))
The sum_range is Manhours from L11:L34 for each time ticket sheet. The criteria_range1 is date in cell B6 listed on each time ticket sheet, criteria1 is date listed on cell J8 of the summary sheet . The criteria_range2 is names from C11:C34 for each time ticket sheet, criteria2 is name of person on cell I9 of the summary sheet.
Each sheet is labeled as 8-19-13, 8-20-13, 8-21-13, and so on. Summary sheet is labeled as Summary
The table that I am using to record the data on the summary sheet has the date increasing by one day on row 8. The names are vertically listed on column I.
View 1 Replies
View Related
Dec 18, 2012
I have a list of named cells (i.e. Group1, Group2, Group3, etc)
I have a form control that allows the user to select which of the groups they would like to include.
I need to create a series of formulas that average the price of all the chosen groups. So if they just want to average Group1, Group3, Group5 they could do so by simply selecting the checkboxes associated with those names.
I am struggling to be able to create a formula which will look at the list of chosen values and Average only those names. If all groups were chosen this would be easy as I would simply use the:=Average(indirect(a1),indirect(a2)...and so on. However that assumes that I always know which values are being chosen. I have a fixed list of choices but if they don't choose one of the options then the indirect statement will be referencing a null value which than either returns to me a #REF or will factor in a '0' which then throws off the average.
View 3 Replies
View Related
Aug 11, 2013
Excel - 2010
I have the following formula:
=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")
TRANS_Cost = B:B on FEB tab
TRANS_Cat1 = C:C on FEB tab
TRANS_Cat2 = D:D on FEB tab
CAT_Main3 = "Everyday_Expenses"
TRANS_Status = E:E
In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.
View 2 Replies
View Related
Feb 7, 2014
I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.
View 4 Replies
View Related
Sep 28, 2011
determining the correct formula to lookup various details from multiple worksheet:
Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000
Sheet2: Data
AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
View 9 Replies
View Related
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
Aug 26, 2013
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies
View Related
Jul 12, 2014
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
View 4 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
Jul 28, 2013
I'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
View 5 Replies
View Related
Mar 27, 2014
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
View 8 Replies
View Related
Aug 18, 2007
I have an excel workbook with 8 worksheets. Each worksheet has vertical columns (approx 250 columns per sheet) of numeric data. Is there a function or macro that will combine all of this data into one vertical column without having to individually cut and paste each one into the new column?
View 3 Replies
View Related
Sep 5, 2008
I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
View 9 Replies
View Related
Jan 28, 2009
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
View 4 Replies
View Related
Dec 27, 2013
Code to merge worksheets from different workbooks stored in different location.
I have a sheet called "Master" in all the workbook I want to combine.
I have a unique password for all the workbook as well.
All the workbooks are stored in different folder location.
I would like to do a paste special values when the consolidation takes place.
View 1 Replies
View Related
Sep 7, 2006
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
View 4 Replies
View Related
Aug 10, 2007
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet.
The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets.
To reiterate, here's what I am trying to do.
Step 1. Create a summary sheet.
Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet.
Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet
I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data
I am trying to do this using macros (within an add-in)
I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.
View 4 Replies
View Related
Nov 21, 2006
I need a summary page in a workbook that will pull data from multiple workseets with mulriple criteria.
I have treid sumproduct, but that doesn't seem to be working when I select a range from multiple workseets.
View 9 Replies
View Related
Jan 8, 2010
I have a macro that I presently use with just one worksheet. I now need this same macro to do the exact same thing on additional worksheets (in the same workbook) that I plan to add. These new worksheets will have the exact same format, structure etc., only the data that my users enter will be different.
Question: Is there some simple way to modify my existing macro so that each time it runs it performs its functions over multiple worksheets?
(I'm guessing that it has something to do with the "worksheets.active" code but I can't figure out how to get the right syntax to get that to work.)
View 9 Replies
View Related
Nov 11, 2006
I have 4 sheets that use the same template.
Now user has option to add new Rows to it.
There are few predefined row layouts (using diffrent lists as column values).
This is code that i tryed to use:
Only adding new row and insertin its name (i didnt try formating cells yet since i cant get this simple stuff to work).
Dim ActiveSheetName As String
ActiveSheetName = ActiveSheet.Name
Sheets( Array("Week One", "Week Two", "Week Three", "Week Four")).Select
Range("A14").Select
Selection.End(xlDown).offset(1, 0).Select
Selection.EntireRow.Insert
Selection.Value = TextBox1.Value
But this code only enters new row in first sheet (Week One).
Other sheets are unchnged.
TextBox value is enterd in 4 sheets.
View 3 Replies
View Related
Oct 6, 2007
I have the following code for adding and naming worksheets from the default setting of three worksheets for a new workbook. However the first array element, "D1" is skipped and a blank sheet is inserted at the end.
Sub AddWorksheets()
Dim Tabs As Variant
Dim I As Byte
Tabs = Array("d1", "O1", "O2", "H1", "H2", "L1", "L2", "C1", "C2", "V1", "V2", "AC1", "AC2", "M1", "M2", "T1", "T2", "P1", "P2", "SR1", "D1", "D2")
Sheets.Add , Count:=19
For I = 1 To 22
Sheets(I).Name = Tabs(I)
Next I
End Sub
View 9 Replies
View Related
