Sumproduct With Dynamic Range Across Multiple Workbooks
Apr 11, 2012
I have a problem with a sumproduct formula. The formula works however both workbooks have to be opened at the same time or it will not update and you get a #VALUE! error. The formula is:
=SUMPRODUCT(SUMIF([Mthlydta.xlsx]FEB!$DZ$11:$DZ$65536,Cash,[Mthlydta.xlsx]FEB!$DP$11:$DP$65536))
I have also tried using
=SUMPRODUCT(INDEX([Mthlydta.xlsx]FEB!$DZ$11:$DZ$3000,MATCH(Cash,[Mthlydta.xlsx]FEB!$DP$11:$DP$3000,0),1))
But it only finds the first value in the list Cash. Which the list of accounts for cash will grow because we are expanding and have several mergers going on.
View 1 Replies
ADVERTISEMENT
Nov 2, 2007
I have a sheet with data in A1:A50 also in B1:B50 and amounts in C1:C50
I calculate using the formula sumproduct((a1:a50="yes")*(B1:B50="RED")*(C1:C50)). How can I change this to allow for rows being added on a regular basis.
View 9 Replies
View Related
Jul 16, 2012
I have a "RANKIF" formula using SUMPRODUCT:
=SUMPRODUCT(--($B4=$B$4:$B$100),--($F4
View 9 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
Nov 23, 2008
There are two teams in my department, and each is assigned to maintain their respective work book and I'm looking to link them in order to save some time.
Team A - Responsible for receiving Invoices (Bills) and entering them in an excel spreadsheet when received and update when bill is paid. Only one tab in this workbook.
Row A - Name of company billing us
Row B - Invoice #
Row C - Invoice Amount
Row D - Once Bill is paid the check amount is entered here
Row E - Balance Due (Row C - Row D = Row E)
Team B - Is Responsible for maintaining a list of all checks issued. All of the checks issued to pay the bills received by Team A are entered here plus other checks to pay a variety of different stuff. On this workbook a new tab is created every month. One tab per month. Since we need to follow accounting rules and record the check NOT on the month it was paid, but on the month the service was provided. for example I might be paying a bill in the month of November for services that were provided in September, so I would need to enter this check in the September Tab.
Row A - Name of company check is paid to
Row B - Invoice #
Row C - Amount Requested to be paid
Row D - Reason for payment
Row E - Date of check issued
Row F - Amount paid
Row G - Check #
Here is what I want to do.
I want to link both of these workbooks so that when Team B fills out the information of the check issued this will automatically update the Workbook of Team A so that
the balance is zeroed out.
He is my challenge. Workbook of Team B has multiple tabs so I can't just do a simple Vlookup and also every month a new tab is created (very dynamic workbook).
TO add to this in Team B's worksheets have to be in alphabetical order, which means that rows are inserted everyday. for example if I paid yesterday to A and C, I enter company A in Row1 and Company C in row 2 but today I received invoice from Company B so in order for them to be alphabetically I would need to insert a row between Row1 and Row2. So if I had links to this workbook they wold not update when the new row is added.
View 14 Replies
View Related
Feb 22, 2009
I have what I thought was going to be a simple function to create and, after a week of getting more error messages than I've had in a year, I'm ready to throw in the towel. Situation: There are two worksheets in the same workbook [Excel 2003]. One is for "Posting" details about technicians and their work orders. The other is a "Report" that summarizes the number of workorders open, closed, etc.
In the "Posting" sheet, I have created dynamic name ranges for two columns: Technician (4-digit text field) and InDate (date field that, if blank, means that the technician has not closed this work order. My ranges are set up like this:
Technician=OFFSET(Posting!$B$2,0,0,COUNT(Posting!$B:$B),1)
InDate=OFFSET(Posting!$D$2,0,0,COUNT(Posting!$D:$D),1)
So all I need to do is to total all the blank InDates for each Technician by employee number. I've tried COUNT to SUMPRODUCT. A co-worker got it to work with using...=SUMPRODUCT((ISBLANK(Posting!$D$2:$D$65307))*(Posting!$B$2:$B$65307=4288))
His solution was to list almost every available cell in each column. I just can't help but think that the dynamic name range should be able to work but I can't get it right. I think I'll choke if I get one more "N/A" or "VALUE! error.
View 3 Replies
View Related
Sep 15, 2014
I have an issue with SUMPRODUCT. My data is the budget for different items as follows
Column C has criterias such as Payroll, Expense or Fringes.
Column D has criterias such as "32", "43"
Column E to P are the numeric value (budget) by month (Period 01 to Period 12)
I am trying to create a formula that, for a given month, will sum the budget according to specific criteria.
For example, I want the value of the budget for "Payroll" + "32" for Period 1.
I managed to do that with the following formula:
E18 = 1
E16 = Fringes
E17 = 32
"=SUMPRODUCT((INDEX($E$4:$P$12,0,MATCH($E$18,$E$4:$P$4,0)))*($C$4:$C$12=$E$16)*($D$4:$D$12=$E$17))"
I used INDEX/MATCH so that the range into which the sumproduct will look for data to sum is variable and depends on what I want (I can easily change E18 to any period for which I would like to see my budget).
Now, I would like to take this to the next level and ask for a range of a column. For example, I would like to know the value of budget for "Payroll" + "32" for Period 01 + Period 02 + Period 03. How can I do that? I tried adapting the INDEX/MATCH but got no results.
Test on SUMPRODUCT.xlsx
View 6 Replies
View Related
Aug 19, 2012
The part in green will count the number of entries for the name Johnson & Freedman LLC perfectly fine. However when i add the last part in red i receive a #Value! error.
Col. W is formatted as General and has a data validation for the user only to choose Pass or Fail.
Not sure why it isn't working.
Code:
=SUMPRODUCT(--( 'SCRA'!B26:B29>=Sheet3!C2),--('SCRA'!B26:B29
View 8 Replies
View Related
Feb 15, 2009
In a folder I have a group of approx 30 workbooks which each contain up to 200 worksheets. All the worksheets contain a set of formulas in the range FR1:FT30. I now need to update these formulas.
I have a precedent file called prec.xls & i need to copy the range FR1:FT30 from this file to all the worksheets in workbooks i have described above.
View 9 Replies
View Related
Aug 31, 2007
Currently i am looking for a macro to combine information in different workbooks but on the same sheetname. In every single workbook i have a worksheet with a single row of information which has to be added to one big file. In stead of doing all 200 manually i would like vba to select this particular range, copy it and paste them under eachother in one single worksheet in a different workbook. Is this possible?
for examble book 1, sheet 1 has all the data in rang a1 till z1. this has to be copied to the masterfile under a1. this goes on from book 1 till book 200.
View 9 Replies
View Related
Jun 16, 2014
I am trying to create a macro to gather a data range from multiple files placed a folder and combine them into a single worksheet which can be easily totaled. I've used some similar code I did for another project to gather the data. It starts by listing the file name and then the data set (About 40 cells) below the file title. However, the data sets from each spreadsheet are filling themselves into a single column one after the other, whereas I would like to have them populate one worksheet's data in each column.
[Code]......
SummaryWorkbook.xls
View 1 Replies
View Related
Aug 6, 2014
I have multiple excel files from which I would like to copy specific ranges to a master file. The row should add up to the previous rows. From this side I have no problems things work fine. The problem is to copy the header which remains the same data to the top of the master file. Currently the macro I use overwrite the first line of the master file. So I loose the date from one of the excel files.
View 3 Replies
View Related
Apr 28, 2008
I have the following code that works properly, Copy & Paste Same Range On Protected Sheet From Multiple Workbooks
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook, book1R As Range
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit...............
View 2 Replies
View Related
May 13, 2014
I have a spreadsheet where I've disallowed selection of multiple cells using the code in the 2nd box below, but is there a way to override this protection to allow selection of a named range? My reason for doing so is to open this workbook as read only from code in another workbook and copy the entire table. My code for doing that works ok (I frequently use it in other workbooks) but, in this case, my need to disallow selection of multiple cells in this particular worksheet is tripping me up.
This is the code I'm using to try and pull the data into another workbook:
Code:
Workbooks.Open Filename:=ThisWorkbook.Path & "RSL-Jobs-Register.xls", ReadOnly:=True
Application.GoTo Reference:="JobsTable"
Selection.Copy
and this is the code I have in RSL-Jobs-Register.xls which is preventing me from selecting the (dynamic) named range called "JobsTable".
Code:
''' I'm thinking I could have something here like (in plain English) "If selected range "JobsTable" Then"
''' (apply the code below) but I'm not sure of the code
If Selection.Cells.Count > 1 Then
MsgBox "Sorry, operations on multiple selections aren't allowed here", vbCritical
ActiveCell.Select
Exit Sub
End If
The formula for the dynamic named range called "JobsTable":
=OFFSET(Jobs!$A$2,1,0,COUNTA(Jobs!$A:$A)-2,11)
View 1 Replies
View Related
Aug 1, 2014
I have several workbooks (5) with the same variables (columns- A:Q) but with a changing amount of rows (2:n, not including the headers). Each row corresponds to a date range (usually a week) for a particular person (up to 40 people) plus a few other values.
I would like to have a way of "merging" or "compiling" the 5 "seed" workbooks into 1 "master" worksheet. Where rows 2:n of each of the 5 "seed" workbooks are added to the master without any duplication of the same name-date range combination. Also, the master worksheet should not include the rows which only contain a name and date range but for which all the other variables are zero or missing.
Each "seed" workbook would have a button that sends the data over to the "master" worksheet.
Is this a really difficult project? Feasible for someone with near to zero VBA experience?
I attached 3 files to show you what I mean. The 2 "seed" files are merged into the "master" file. Please note that in the files only 2 names are used, but the "seed" files could contain any combination of 40 some names. Also note that the length of rows which contains data in the "seed" files is variable, although it should not be longer than 16 rows + the header row.
View 14 Replies
View Related
Nov 8, 2008
That does allow me to filter the output in Master, but ideally what I would want to do is not have to set aside 300 lines in the Master file for each of the Staff files. Conceptually, I'd like to have the spreadsheets Staff A, Staff B, etc. look for and export only lines which have data in them and then have Master bring those lines in automatically. This may not be possible, in which case your suggested solution is the best approach.
View 9 Replies
View Related
Mar 31, 2008
I have been trying to use a FOR EACH C ...... NEXT loop, where each cell in the range is the beginning of a worksheet name that I need to import.
What I have got so far is:
Sub FindMyFiles()
Dim fs As FileSearch
Dim ImportWB As Workbook
Dim MasterWB As Workbook
Set fs = Application.FileSearch
Set MasterWB = ThisWorkbook
View 4 Replies
View Related
Feb 11, 2014
Selecting the range from Multiple Column data.
Currently, it is:
[Code]....
I have data from columns A:E
View 4 Replies
View Related
May 2, 2007
I'm having a fit with this formula; maybe the gurus will show me what I'm doing wrong. I have a list of area numbers along with dollar amounts that go with these area numbers. I have created a named range named "Areas" and another one named "MO_PD".
At the bottom of the worksheet I am doing a recap that takes the area number and sums all the entries in the MO PD column. This approach has been done before with Thomach's help. I'm trying to mimic it, but it's not working for me.
NOTE - I know that I can get around this using a pivot table, but I would like to use this approach for now. I would at least like to know what I'm doing wrong. Also, I'm not totally sure if I did the dynamic named ranges correctly so that may need to be fixed.
View 9 Replies
View Related
Apr 2, 2014
I jst need to console data from different workbooks to one worksheet. There are 30 workbooks & each workbook contains one worksheet name as Data. The work i am currently doing mentioned below step wise
1) Open workbook
2) Apply filter on whole data
3) Select dates from "J" Column (E.g 02/10/2014 to 02/20/2014)
4) Then Copy whole filtered data
5)Paste into New workbook
6) Open another worbook
7) repeate Step # 2,3
8)Then paste whole data in below the first data New Workbook (In which we have paste the first data below that i will paste this data)
These steps i have to do manually it takes almost 1hr daily After pasting all 30 workbooks data in one workbook i have to make pivot of it & sent it to my Manager. What i need
I jst want a excel macro that will ask me date range from which i have to pull data from all 30 workbooks.
Once i gave date range will jst click on button say extract or pull. It willl console whole 30 workbooks data from the given date range in a one worksheet.
View 13 Replies
View Related
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Dec 19, 2008
I have a spreadsheet that requires data from different workbooks.But the names of the workbooks are to be received as user input and used dynamically to copy data from that workbook to original workbook.
To make it easier,let me use this example.
I have a workbook named original.xls.It has a command button or any other control.When i click that control it will ask for a workbook name of the project which is in the second column of the "original.xls" workbook.As soon as i enter the workbook name it will copy data from that workbook to the rest of the columns in my "original.xls" workbook.Then it has to ask to ask for second workbook name and do the same procedure till it encounters a blank row.
View 9 Replies
View Related
Dec 15, 2009
I'm working on developing a stock option trading system in Excel using a live data feed and VBA. It tends to crash after running flawlessly for a half hour to 2 hours. It seems to crash when loading a support workbook, so I was hoping you could take a peek at my code and see if there's a problem with the way I'm dynamically opening/closing workbooks.
In order to cope with limitations in the amount of live data that I'm accessing (through DDE with Reuters Station), I've split up the data I need into about 600 workbooks, which open when they're needed or close when they're not (through VBA). At any given time, about 35-40 of these workbooks are open.
The main workbook that uses all this live data and support workbooks is set to have the application recalculate every 5 seconds (automatic recalc crashes the program almost immediately). After that refresh, it calls other subroutines, including opening/closing these support workbooks:
View 14 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
Feb 19, 2012
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
View 5 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
Feb 27, 2014
The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".
I would rather not have to copy this by column for each of the 4 workbooks
[Code].....
View 6 Replies
View Related
May 14, 2014
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
View 2 Replies
View Related
Oct 7, 2009
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
View 9 Replies
View Related
Mar 16, 2008
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View 9 Replies
View Related
