Return SUM Of Variable Cell Across Multiple Worksheets
Oct 26, 2012
I am trying to return the SUM of a variable cell across multiple worksheets... This 3D formula works
=SUM('March 2012:December 2013'!E27)
I wanted the formula on the next row to ref F27 then G27 etc... (which I can't just drag down) so thought I'd try to "assemble" the formula I wanted using the INDIRECT function...
I tried this formula which doesn't seem to work...
=SUM(INDIRECT("'"&E6&":"&E27&"'!E27"))
NOTE: the script in the INDIRECT brackets returns the text string... 'March 2012:December 2013'!E27
Can INDIRECT not handle multisheet references?
View 9 Replies
ADVERTISEMENT
Mar 6, 2014
I have a different worksheet in the same workbook for every "gig" that I book. Each gig contains the name of the musicians I staffed on the gig along with their salary+expenses For taxes, I need to summarize into a new worksheet how much each musician made. Ideally i'd see not only the total per name but itemized. So if 1 guy did 3 diff gigs, I'd see each row pertaining to his name.
Lastly, I'd like to include names that do not have multiple records because it's possible there is inconsistencies with the spelling of certain names.
View 3 Replies
View Related
Apr 2, 2014
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells:
$DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
View 12 Replies
View Related
Feb 24, 2009
I have a problem with VLOOKUP. The Value im trying to look up a value which is one of 5 sheets Called Con1 to Con 5. These Sheet names are in an Array called "ArrCons" the look up value range is also a variable and looping through column "C". The table Array is on Sheet "Con" Range "A:E", return column is 2 and the logical value is "false".
ActiveCell.Offset(i-1,2)=Application.VLOOKUP _(ArrCons(y)!C & i -1, Sheets("Con").Range("A:D"),2,False)
View 9 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Nov 25, 2013
I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.
Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:
Worksheet 1 (ColumnA/ColumnB):
Revenue/45,000
Expense/20,000
Asset/43,000
Liability/21,000
Revenue/6,000
Expense/9,000
Expense/11,000
Liability/13,000
Worksheet 2 required output (no row gaps or spaces):
Input cell "Expense" - in A1
List required (A3:B5):
Expense/20,000
Expense/9,000
Expense/11,000
View 9 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
View 2 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
Jul 27, 2007
I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.
For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11
Is there a faster way to do this?
View 6 Replies
View Related
Jul 27, 2007
I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet. For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11
View 3 Replies
View Related
Jul 23, 2006
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
View 8 Replies
View Related
Jan 6, 2014
I have nearly 200 worksheet in a same workbook. Sheet 1 is the index sheet. Now I need to copy cell values from A1:C1 of all 200 sheets to A1:C1 , A2:C2 , A3:C3...A200:C200 of index sheet. How can I do it automate?
View 6 Replies
View Related
Nov 26, 2009
I have a formula that sum' the same cell but on 24 worksheets in the same work book. Each sheet is exactly the same format, just different data.
If I wanted to create a macro that would select say cell ("A1") on sheet1 threw sheet12 how would it be written?
What currently have is a long formula, selecting each cell on each page. Can this be shortened up? Besides holding the shift key.
View 9 Replies
View Related
Apr 3, 2008
I am wanting to make a table that shows values from multiple sheets. The individual sheets are essentially a template, and I would like to be able to grab a value from the same cell in different sheets and make a table. Can I do it without having to get each value individually? Perhaps put most clearly, I would like to have a table that shows the value at 'C1' in each sheet without having to manually select 'C1' from each sheet.
View 9 Replies
View Related
Jun 19, 2009
I am using excel as a base for formatting reports, and collating logical paragraphs, which I want to display in one massive merged cell on another worksheet.
The paragraphs are different for each report, and I do not wish to manually enter them in each time. I also want them to display as seperate paragraphs on the page, but in the same cell.
To throw a spanner in the works, the paragraphs are all of indeterminate length, otherwise, I could use different cells. If I combine them in the massive merged cell by using =cell1&cell2&cell3 then they will just appear merged as one whole huge paragraph. I want them to appear as separate paragraphs in the same big cell.
View 4 Replies
View Related
May 17, 2014
how to set a cells value into a variable, using .value, then set another cells value equal to that variable without using copy/paste
What I can't figure out is how to see the value of multiple cells to a variable and place them into another range of the same size using .value. It would be nice to free up the clipboard.
View 2 Replies
View Related
Nov 26, 2009
I'm trying to place the value of "X" in multiple cells based on a cell variable (i). For example, if (i) is evaluates to 35, the following code ...
View 8 Replies
View Related
Jun 27, 2014
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
View 2 Replies
View Related
Feb 10, 2013
I have many worksheets named with client names and in different order from what I see in VB.
How can I copy cell C6 from each sheet into a column somewhere (it doesnt matter)
But the order of worksheets is messed up (On VB Sheet1 is in 3d place sheet2 is in 10nth place ... and so on) I need the vales to be pasted in the order of which I see the worksheets when I open the file.
View 6 Replies
View Related
Jan 12, 2013
I have a spreadsheet with many worksheets, everyone except the first representing a year. Every worksheet has twelve tables (the months), the first row of each table has the days of the month and the second row has the data that I put on a daily basis. The first worksheet calculates statistics using the data from the other worksheets.
I am looking for a formula that returns the date by finding the last populated cell and looking right above it, so I can use this info in the first worksheet to show when the spreadsheet was last updated.
View 2 Replies
View Related
Oct 11, 2007
I'm simply trying to take for example cell A1 from multiple sheets and list them vertically on a master sheet. When you drag it down it does not alter the sheet number, that just stays static.
View 3 Replies
View Related
Apr 28, 2008
I am trying to automatically insert multiple (100) worksheets using VBA. The names I need each worksheet to be named are in a list in a separate worksheet in the same workbook. I found this site: http://www.mindspring.com/%7Etflynn/excelvba3.html and have been trying to adapt the following
Sub AddSheetWithNameCheckIfExists()
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets(1).Range("A2") ' Substitute your range here
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
End Sub
I am having difficulty iterating the code from cell A2 to A102.
View 4 Replies
View Related
Jun 24, 2008
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was
Sub Selects()
Dim strSht As String
Sheets("Criteria").Select
strSht = ActiveSheet.Range("L31")
Sheets( Array(strSht)).Select
End Sub
View 7 Replies
View Related
Aug 12, 2008
I have multiple worksheets (too many, actually!) and each worksheet is a record. I need to enter a Record Number into B5 of each sheet. The Record Number doesn't start at 1. I'd like to enter a number into the first sheet and the rest of the sheets to increase by 1. However, I only need to do this to some sheets.
View 9 Replies
View Related
Dec 1, 2013
I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.
Sub Header()
Worksheets("Header").Activate
ActiveSheet.Cells(1, 1).Select
ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' "
End Sub
how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?
View 2 Replies
View Related
Feb 22, 2008
We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.
Current working formula:
=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202,0)))
What I'm trying to accomplish, but it not working is something like this:
=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202&CustomSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202&CustomSchedule!$B$3:$B$202,0)))
View 9 Replies
View Related
Jun 25, 2008
I am trying to write a Macro that would go into same specific cells in multiple worksheets and paste the data into one summary worksheet if ofcourse a criteria is met.
The multiple worksheets have the same format. The worksheets are numbered 001, 002, 003, 004, etc (increase on a daily base) ..
View 3 Replies
View Related
Nov 19, 2013
At work I have a spread sheet that I used to track material shortages by part number. So in column A of the spread sheet there is a list of part numbers that have shortages, column E contains a list of all sales orders that are affected by the shortage separated by a comma. I am trying to setup a query sheet where I input a sales order and get back a list of parts that are short for that sales order(basically reversing the original list to be by sales order instead of part number). The number of values in column E varies, sometimes a cell will have 1 value, sometimes 20+ and anywhere in between.
Example Sheet:
A
B
C
D
E
123
012
234
789, 567
465
789
890
012
I'm already got a INDEX/MATCH that would show both shortages for sales order 012. But I can not figure out how to get the shortages for 789 or 567.
View 1 Replies
View Related
May 10, 2007
I have a workbook that has a lot of worksheets. I want to merge all the worksheet data, skipping blank rows from each worksheet and simply appended the data on one single worksheet. The goals is to just copy the data from worksheet b and append to worksheet a>copy data from worksheet c and append to worksheet a> etc. etc.
View 9 Replies
View Related