I want to have a summary tab calculate figures of other tabs. For example in "Summary" tab, I want to Sum (a:a) for tab "January" =January!sum(a:a)
Does not work. I have also tried other options as well. Is it possible to use formulas when referencing other tabs.
The "summary" tab will be calculating 20 statistics for each of the 30 or more tabs. In an effort to automate this - can I replace writing "January!" with a cell reference? For example;
instead of writing = sum(january!a:a). Could I write = sum("A1"a:a), with the A1 Cell = january!. Obviously I tried this and it does not work but I am not sure if there are other options.
I am currently working through a macro and got stuck about halfway. I have a number of files in a folder on my drive that I am pulling the first tab from into a Master workbook, and then I want to have a summary tab for all of those tabs(they are all identical). Some of the cells will be text(say range A5:C105), some will be SUM(E6:G105) and some will be AVERAGE(D6:D104) formulas needed. These formulas will not change, but will need to pull the data from all tabs that are pulled into the file.
So far I have this code that pulls all of the first tabs together:
Code:
Sub Staff_Plan_Update()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Call TimeStamp
[Code] ......
I was going to record a macro that creates a summary table every time, but not sure if it is easier to create a blank template for the summary tab that will update every time all of the tabs are pulled into this file. The problem I ran across with that is that I will be taking the SUM of all tabs, but the number of tabs/name of tabs will be different.
How can I create a macro that will:
Copy a formula in multiple cells to the next cells in each tab that I designate in my workbook.
For example,
I need the EXACT formula in cell N13 to be copied to O13. And then paste special the "value only" back into N13.
I also need the EXACT formula in O19 and O20 to be copied to P19 and P20, respectively. And then paste special the "value only" back into O19 and O20.
Can I have this exact process done in several different tabs within my workbook?
I have never written a macro and when I record one I usually have trouble with the relative references.
I am trying to write a macro to transform formulas into values every month.
I want to transform formulas:
- across a range of tabs: each tab is exactly alike and is named page-1 to page 25
- on a different column every month (same column across all tabs)
- on the same rows: L168 to L227 and L266 to L277 (same rows across all tabs)
I have a worksheet that contains 26 tabs all of which have the same format but contain different data based on that pay period. i would like to create a summary tab which will allow me to enter the pay period at the top (1,2,3 ect) and have excel reference that tabs information into the summary. Is this possible?
View 6 Replies View RelatedI'm writing a custom function, and in the function i want a reference to the cell that the function is entered in, how do i do that?
View 7 Replies View RelatedI have 2 cells which I will select on a work book; (say A1 and A2). A1 is the input cell and the formula and the result generated is in A2.
Suppose I input 3 in A1 and A2 shows 60, what I also want to do is input 60 in A2, which should show me 3 in A1, without destroying the formulas.
In short both cells will have formula and both cells will be input cells for each other.
I would like to drag down the formula from the last cell in Column A in reference to the last row in column B.
E.g My last data in Column A104 (code to select the last cell and this can vary from time to time the report is updated)
Column B has data till B142. I want to drag A104(contains formulas) down to A142.
Here is what I have but Selection Autofill doesn't work.
Range("A2").End(xlToRight).Select
Range("A2").End(xlDown).Select
Selection.AutoFill Destination:=Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault
In the attached example, I need to create a formula for cell I2 (Balance Remaining) that is driven off the most recent calculation (in this case, F68) but still allows for additional rows to be added to the ongoing list of orders. I believe I'm supposed to use an index formula but not sure how to write/apply it? I need to be able to add rows past 68 and still have Cell I2 (Balance Remaining) continue to calculate correctly.
View 10 Replies View RelatedOn choosing Auditing Funtion, Trace Dependants, a small icon representing a spreadsheet ? appears at the end of a dashed line. What does this refer to?
View 3 Replies View RelatedFormula: B2+C2
In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.
As it stands I can only get the formula to calculate within the same row.
I am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.
It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?
This may be an easy answer, but I haven't been able to figure it out. I have a cumulative page of formulas that reads "SHEET1!B8+SHEET!B9". I need the next row formula to reference "SHEET1!B47+SHEET1!B48", and then "SHEET1!B86+SHEET1!B87". Each row the formula moves 39 rows on sheet 1. Is there a way to get excel to automatically do this, so I don't have to go line by line?
View 9 Replies View RelatedI'm actually having difficulty with the sumproduct function, I'm trying to count items based on multiple criteria (and yes I have looked under that). I'm using a very helpful formula that I grabbed from this site. =SUMPRODUCT(1*(base!$B$1:$B$19465=locations!B$1),1*(base!$A$1:$A$19465=locations!$A6))
The problem that I have is that this doesn't work if I replace $B$1:$B$19465 with $B:$B. now as the number of rows on the base sheet may change, this means I need to change the formula each time I change the data on the base sheet which is somewhat ridiculous. I've been playing around with indirect trying to reference the last cell in a range, but I don't seem to be getting anywhere with that.
I want to be able to sort a table alphabetically by one column after adding a new row to the bottom. But there is another table that uses the values of the former table. How can this be done without screwing up the formulas in the second table.
View 5 Replies View RelatedHow do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.
View 6 Replies View RelatedThe spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.
We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?
I'm preparing an accounting model for my office use. How to solve the copying of formula to all other cells.
The detail is here:
Sheet 1:
1) I've entered a number 1000 in L2
Sheet 2:
1) I've entered a values in columns D,E,F,G
2) Calculations :
at G2 : the formula is =D2*Sheet1!L2
The problem is when I Copy the formula in G2 through G3, G4, G5........... it changes to =D3*Sheet1!L3, =D4*ValidData!L4, =D5*ValidData!L5 and so on... but it should be =D3*Sheet1!L2, =D4*ValidData!L2, =D5*ValidData!L2, so that the L2 value shall be constant for calculations in all cells.
I have a 2 columns (A and B) next to each other that is using a Sum formula and other multiplying formulas. They reference 3 cells in a different column (C) to come up up with the answer for A and B. Therefore A1 and B1 reference C1 C2 and C3 (and possibly if want to use in future column D with D1 D2 D3).
Then in A2 and B2 I want to reference from C4, C5, C6
Then in A3 and A4 I want to reference from C7, C78, C9 etc.... and so on...
Instead of retyping formulas for each row in A and B, I want to copy down, but I getting the wrong answer when I do that.
I am therefore assuming I typing the formula wrong. How do I type it correctly so the cell reference changes automatically when I pull down columns A and B?
Formula for Column A: =SUM($G28:$G30)
Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17
Therefore next row should be referenced from G31 to G33 and H31 to H33
But when I copy it only adjusts it for 1 reference down.
I have 2 sheets in a workbook where i would like cell G8 on sheet 1 to equal the value on A11 sheet 2 & G9 to equal A12 and so on down the sheet. The problem is that G8 are 3 merged cell so every time I try to use autofill the reference number jumps by 3. The formula in cell G8 is
View 3 Replies View RelatedI have a list of codes from which I'm trying to extract all unique entries.
For example, col A has 001, 001, 002, 003, 003, 003, 004, ...
I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.
Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.
The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.
I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.
View 1 Replies View RelatedI have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedHave a workbook that was sent to me, linked to another workbook. Have just clicked into a cell to see the reference (there is no value in the cell, it is just coloured) to find this: ='[wwtrial M5 J14-20.xls]TL''s'!D5 I've not seen the TL''s'!D5 part before - can anyone tell me what it means??
View 3 Replies View RelatedI 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".
I'm using Excel '03SP2 on Win 2000sp4. I believe I have a case where old tabs originally written in Excel ' 97 behave differently than new tabs in the same workbook, which is saved in 2003 format.
Specifically, the formula ="PLAN" & budyr-1 where budyr is a named range on a different tab containing 2008 . This formula yields PLAN 2007 on a new tab and #VALUE! on an old tab.
How do I get Excel to update the old tabs so that they behave as Excel 2003 tabs?
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View Related