Excel 2007 :: Syntax For Referencing Worksheet In A Formula
Apr 19, 2012
I am using Excel 2007 in Windows XP. I am trying to change the worksheet referenced in a formula from the label of the worksheet to the index of the sheet. I want to use this macro in multiple workbooks, and the relevant sheets have different names but are all in the same position.
I used "record macro" to get the structure of my code. It gave me the line:
ActiveCell.FormulaR1C1 = "=SUM('8Nov11'!R[13]C[-4]:R[108]C[-4])"
I want to change that '8Nov11' to the 3rd worksheet from the left, so something like:
ActiveCell.FormulaR1C1 = "=SUM(ThisWorkbook.Sheets(3)!R[13]C[-4]:R[108]C[-4])"
I can't get this to work, and I can't find the syntax for this anywhere. I've tried with and without quotes, exclamation points, changing to A1 notation and using "Range," "Sheets," "Worksheets," "Item(3)", and many other combinations, but haven't gotten it right.
Most of the errors I get are "Runtime Error 1004: Application-defined or object-defined error."
View 3 Replies
ADVERTISEMENT
Nov 8, 2011
I am working on a sheet that will copy a specified number of sheets, rename them, set a cell within the equal to a cell from another sheet and (the problem) set cells on my input sheet equal to cells in the newly copied and renamed sheets.
I want the cells in sheet "Data Input" to look something like "='newsheet'E26"
I can make it return the actual number in the cell and return the formula in the cell, but not make the cell in "Data Input" reference the cell in the new book.
Here is what I have:
Code:
Sub CopySheetsTest()
Dim i As Integer
Dim wks As Worksheet
[Code]....
View 4 Replies
View Related
Feb 7, 2012
Everytime I save my workbook, I get a dialog box that says:
"A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a vlid path, workbook, range name, and cell reference."
First off, it say's "this" worksheet no matter what worksheet is active.
Usually the culprit is a screwed up name that has a #REF in it somewhere. Not this time. All of the formulas appear to be working fine.
Is there an audit feature that will find the errors for me? (Excel 2007)
View 3 Replies
View Related
Nov 22, 2012
Is there a formula to count empty cells on a excel sheet?
View 4 Replies
View Related
Jul 20, 2012
I'm trying to optimize code by avoiding activating other worksheets, but I'm running into a problem with a match function.
I'm using a workbook called "Template" and then opening another workbook called "DCP" and trying to use the match function to figure out what row data is on. I can get this first line of code to work:
HTML Code:
MatchedRowNumber = WorksheetFunction.Match(Combo, Sheets("Sheet1").Range("A:A"), 0)
However for that to work, I have to activate the other workbook. I want to avoid that and stay within the "Template" workbook. I think I need something like this:
HTML Code:
MatchedRowNumber = WorksheetFunction.Match(Combo, DCP.Sheets(DCPSheet).Range("A:A"), 0)
That one however doesn't work... looks like I have the wrong syntax.
Below is an excerpt for the code in case something is wrong with how I set the variables.
HTML Code:
Sub StockOrderByDCP()
Dim Template As Workbook
Dim DCP As Workbook
Dim MSS As Worksheet
Dim DCPSheet As Worksheet
[Code] ..........
View 4 Replies
View Related
Dec 8, 2011
I have the formula:
=VLOOKUP(C10,'[Ticket Out Comparison Report.12.4.11.xls]TO'!$C:$O,10,0)
I want to do something like putting 12.4.11 in cell B1 and change the formula to something like:
=VLOOKUP(C10,'[Ticket Out Comparison Report.B1.xls]TO'!$C:$O,10,0)
View 9 Replies
View Related
Oct 31, 2011
I use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.
I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.
I tried by deleting unnecessary COM add-ins but that didn't work.
View 1 Replies
View Related
Aug 19, 2009
Reference a worksheet that has not yet been created, avoiding the #REF! result?
The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.
View 9 Replies
View Related
Jan 20, 2010
The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.
For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2
That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.
Is there a way to reference the prior worksheet without using it's specific name?
=C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.
View 7 Replies
View Related
Oct 19, 2012
I have created a userform that once the user clicks the okay button, the information is transferred to a worksheet template named 'PO Template', copied, and renamed. That portion works great. I then have code for a summary worksheet named 'PO Log' that finds the next empty row on the log and enters a formula referencing back to the newly created 'PO Form' (note - 'PO Form' is a variable as the actual worksheet name changes with the user input). The formula on the 'PO Log' references the 'PO Form' literally, which gives me a REF! error because there is no sheet actually named that. I've tried defining POForm in the code as a string so I can enter it into the formula code but I get an error because it's already defined as a variable. I've entered the sub routine for clicking the okay button below:
Code:
Sub OkayButton_Click()
'Make PO Template Active
Sheets("PO Template").Activate
[Code]....
View 2 Replies
View Related
Jun 15, 2012
I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
View 1 Replies
View Related
May 18, 2009
I have a workbook with ~80 sheets. One sheet is a summary sheet containing data from each of the other sheets. The formula to display the data is pretty easy, but I need a function to copy the formula but increment the worksheet reference in each cell.
For example:
the formula in one cell is ='17'!$AI$6
The next cell should be ='18'!$AI$6
and so on...
I have about 12 columns like this, so I really don't want to edit each of them individually.
View 5 Replies
View Related
Feb 14, 2012
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies
View Related
Jan 9, 2013
I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.
Rolling 12 Months
IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on
1234Employee Name - - - - (11.07)
1235Employee Name - - - 0.20 -
1236Employee Name - - - - -
1237Employee Name - - - - (1.00)
[Code] .........
View 4 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
Nov 14, 2012
How to autofill "1" on the other worksheet, the situation as follow,
Sheet 1 is data input sheet.
Name
Start date
End Date
Ann
2-1-2013
5-1-2013
Billy
7-1-2013
9-1-2013
[Code] ..........
Sheet 2 is the Calendar and the autofill result should be like this.
Date
Ann
Billy
1/1/2013
[Code] .........
View 2 Replies
View Related
Jul 29, 2013
how to add many columns to the rightmost part of the worksheet carrying on from Col AA?
I keep getting error 'Cannot shift objects off sheet.' and followed instructions to change a setting to All or use control-6 but this made no difference.
View 7 Replies
View Related
Aug 5, 2013
How can I unprotect MS excel 2007 sheet. I have forgotten the password.
View 1 Replies
View Related
Dec 30, 2012
I know how to protect a worksheet with a password so that no one can amend the contents or view the formulas in the cells. And then we can un-protect the worksheet by clicking on the "review" tab in excel 2007 and so on. But recently I came across an excel sheet which was password protected and my query is that the "Review" tab was greyed out. What kind of protection was this that clicking on "review" tab option is also unavailable. And how does these kind of sheets get unlocked then.
View 2 Replies
View Related
Oct 13, 2013
I'm fairly new to Excel (2007).
I have used a simple COUNTIF formula (=COUNTIF(C7:C207,"Name")) on sheet 1 to give me the total I want.
What I now want to do, is display this total on a different sheet in the same workbook (Sheet 3) without having to take all the data from Sheet 1 over to Sheet 3.
View 7 Replies
View Related
Sep 13, 2009
It is possible to lock the Author of the Worksheet in File --> Properties --> Summary Tab..?
I am using Excel 2003 & 2007..
View 8 Replies
View Related
Feb 15, 2012
Excel 2007. Is there a way to extract information from cells and rows that constantly move? These rows need to be able to cut & paste, copy & Paste, insert and delete. I have the formula I need to display the data, but with the cutting and pasting etc, my formulas get all messed up.
I have the excel sheet on Google docs. It displays some cells wrong because it does not recognize certain formulas.
[URL]
On the second worksheet, I want the information from columns A, H, M, N, and O to be extracted at a certain time each evening and sent to a worksheet, without sending duplicates. These rows will constantly move which is why my current setup, which is just to display the information, will not work... We decided to try to extract it into a different worksheet or workbook if possible.
View 1 Replies
View Related
Apr 27, 2012
How do I insert an animated gif into a worksheet in excel 2007?
View 8 Replies
View Related
Dec 31, 2012
I am using Excel 2007 on Windows Vista Business 32 bit. I have several workbooks. Each one contains 33 worksheets. 31 of the worksheets are for each day of the month, the other two are for yearly totals and revision history. Recently, a menu bar has appeared at the bottom of my worksheet. At the left is an icon that states that no macros are being recorded, click here to start recording. The center of the bar is blank, but placing the mouse over the bar brings up a customization menu.
At the right side of the bar are three icons dealing with page layout. As I scroll down the worksheet entering data for the day, the cursor goes below the bar and the screen does not move up as it used to do. Also, by using the arrows keys to scroll down, I do not see the bar containing the worksheet names. Luckily, I have a macro that allows me to save the current days worksheet and move to the next one, otherwise I would not be able to do that. When I open a previous month, the bar is there also, so it must be a flag that I can't find or don't know about. Is there a way to get things back to normal?
View 9 Replies
View Related
Jul 23, 2014
How to Lock Individual Cells in a Worksheet excel 2007 .... i.e.
A2:A8
I want this selected area locked with password.
View 1 Replies
View Related
Aug 26, 2010
Here is my situation:
I created one worksheet called: Assumptions. In this worksheet I have 6 different columns representing 6 different cases.
I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B columns for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.
Any quick way to do the changes?
My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.
I was thinking there was a way to highlight ALL the reference cells used in my Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.
View 12 Replies
View Related
Mar 7, 2014
I am creating a login system for two users on a excel worksheet. I have the two users on a drop-down list, Maria and Andrew, which then requires a password entry. This is linked onto a data validation to make sure that the password matches the data table.
After, I then click on a macro, which, when successful, takes me from sheet 1 to sheet 2.
My problem is that I need to have it so that Maria is taken from Sheet 1 to Sheet 2, and Andrew from Sheet 1 to Sheet 3.
View 1 Replies
View Related
Nov 24, 2011
Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?
View 9 Replies
View Related
Dec 22, 2011
Using Excel 2007, I'm trying to figure out (and not succeeding!) on how to declare worksheet and range - in order to:
input a formula to cell xfd1, then copy down to xfd2:xfd100 - and here's what I've been playing about with
Code:
Sub filldownxfd()
Dim src As Range, out As Range, wks As Worksheet
Dim sRangeName As String
Workbooks.Item(1).Sheets.Item ("Sheet1")
Dim example As Range
Set example = Range("xfd2:xfd100")
[code]....
View 2 Replies
View Related
Jul 9, 2012
I wish to monitor a change in a certain columns at some worksheets. I have - after having consulted John Walkenbach' fantastic book Excel 2007 Power programming with VBA - understood how I do to monitor a change in specific worksheet, by using the Intersect-function in the change-event on that particular worksheet. That works fine.
My situation is that I have an application that I start out with a certain number of workheets in a workbook, and in the course of the work that the application does, it now and then adds worksheets, and these worksheets I would like to monitor for change in a special column. I know, by looking at the names of the worksheets which ones of those in the collection that I want to check.
I therefore used, not the Worksheet_Change event but the Workbook_SheetChange. It doesn't work quite well. I guess my question for now would be:
The call to this event looks like this:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
1) How - in what way - should I use the first argument, the "Sh as Object"? Can I/Should I give it a name?
The error message I get when I use an instruction like this:
If Not Intersect(Target, Range(M:M)) Is Nothing Then
'Do Stuff
end if
The method 'Intersect' in the object '_Global' failed
(M is the column that I want to check)
View 4 Replies
View Related
