Referencing Sheet Using Cell Notation In Range
Aug 12, 2012
repTot = Application.WorksheetFunction.Sum(Worksheets("Revenue").Range(Cells(xx, 65), Cells(xx, 65 + moNum)))
I am trying to debug the above line of code (moNum is just a variable the tells me how many months to extend the range to the right inorder to get year to date sums).
I have gotten to the point that it will run error free if "Revenue" is the Active worksheet when I run the line, but if I am on any other sheet it throws an "Run=time '1004' Application-defined or object-defined error" error. And if I change the range from cell notation to Column-Row, the issue goes away as well.
View 3 Replies
ADVERTISEMENT
Apr 19, 2013
I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.
View 3 Replies
View Related
Sep 23, 2008
I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.
For example, Range("A1:B10") would be [A1:B10].
I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?
If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.
View 9 Replies
View Related
Oct 31, 2008
I'm trying to select a range of cells using the R1C1 notation. But I'm making an error in the syntax. I know it's really simple, I just don't know what's wrong.
View 2 Replies
View Related
Jul 28, 2009
Ok, I want to use a dynamic range in my Vlookup formula.
I want to use this formula: ....
View 11 Replies
View Related
Feb 24, 2013
I want value in Cell "B2" of "Sheet2" and "Sheet 3" to have the value as active cell in "column C" of "Sheet1".
View 8 Replies
View Related
Oct 23, 2008
I'd like for users to click in a cell in col A in a sheet named "Period7", have the cell value placed in cell C11 in a sheet named "Per7Report," and change the focus or active sheet to sheet Per7Report.
Can this be done simply by clicking in a cell, or must a command button be inserted into each cell (note - odd rows only) in col A in Period7.
on the code to do this?
Note that there is a formula in col A in sheet Period7
View 9 Replies
View Related
Oct 18, 2013
I am trying to reference a cell in a different sheet and am having a hard time getting the formula to return correctly. The IF statement is currently written like this,
IF(theotherworksheetcell="0",iftruethiscelltoequal"0",iffalseIwanttotypeasimpleformula)
Example:IF('BaseRent'J16="0","0",(D16*E16/12))
The current formula is returning $0 only, it should return a number in some cases.
View 2 Replies
View Related
Jul 10, 2009
I have a row with month titles. I manually fill this with data as the year progresses. I need to automatically use the last month updated in the row in a formula. i.e. theformula is in one cell and calculates on the last figure in the row e,g, data is in january (say C3) - when I input Feb data in C4, I want the formula to automatically reference the last complete cell (ignore january data and calculate on Feb data), and so on through the year.
View 12 Replies
View Related
Aug 2, 2014
I have multiple sheets with the names Payrolldata_Companyname (the company name is different for each sheet)
On a sheet called EmailList i have a list of the company names. Part of my macro is giving the cell containing the company im working on a Named Range of CompanyName. For example i might have company in the list called ExcelForum, which is in Cell A12. Cell A12 has been named CompanyName.
I want my macro to select the Sheet called PayrollData_ExcelForum, by getting the ExcelForum part of the sheet name by referencing the Range CompanyName.
My current link of code for this is as follows
Worksheets("Payrolldata_" & (Sheets("EmailList").Range("CompanyName").Value)).Select
This does not work.
View 2 Replies
View Related
Sep 14, 2006
I am working on a sheet called 2005, with "Jan" entered into cell C4. I want to reference cells on the sheet called Jan2005 using a function that points to C4 and adds "2005".
View 2 Replies
View Related
May 14, 2007
I'm trying to figure out the best way to reference a cell's contents as a range in VBA code. So essentially it would be simple like this (except of course this doesn't work)... Range("cell(contents,A1)").Select
I always look in my CD of old forum posts, Mr. Excel articles, Mr. Excel books I have etc. before I break down and ask.
View 9 Replies
View Related
Aug 12, 2008
I need to be able to auto fill, on sheet two,cell references for every 26th entry. Below is an example of what I would like it to be able to do with the drag, auto fill.
View 8 Replies
View Related
Jun 28, 2014
I have multiple sets of data that i need to reference based on the value in another cell. In this instance I am importing data to Excel from an outside source, this data is not consistent each time I import it and therefore based on the values that are imported I need to reference different cell ranges. Here is an example:
Import Data
A B
Alex 1
Ben 2
April 2
Harry 3
Data Sets
Alex Data Range
1 2 3
Refine Help Token
Cover Safe Coin
Simple Aware Change
Ben Data Range
1 2 3
Sorry Away Candy
Happy Home Soda
Mad Neutral Water
My problem is that based on the name in column 1 of the import data I need to reference that data range so I obtain the correct column of Alex's data range with a random chance of any item in the column to be selected. Currently my formula looks like this: =INDEX(CELL("contents",A1),RANDBETWEEN(1,3),B1) and it is returning #VALUE, however when I put the Range Name "ALEX" in in place of the "CELL" formula it works properly the problem is I am not able to put the correct name in for each row.
View 2 Replies
View Related
Oct 21, 2008
I want to set a conditional format to cell A1 with a value in cell P1 When the value in P1 is between 0 and 10 the conditional value in P1 should be set.
Note
cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.
View 7 Replies
View Related
May 13, 2013
Here's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
View 1 Replies
View Related
Dec 2, 2008
looking for a formula that will get me the sheet name.example: I have 3 sheets each named X, Y, Z. in cells A1 I want the name of sheet 3, which would be X
View 2 Replies
View Related
Apr 12, 2007
I have 8 sheets, named cpt1 to cpt8 in vba.
Now i need to loop through these 8 sheets, storing the same range of each sheet into a matrix. I have the following
dim test () as variant
For i = 1 to 8
(1) test = sheets(i).Range("b2:u21")
(2) test = cpt1.Range("b2:u21")
next i
Now (1) doesn't work, but (2) does. However i do not know how to reference the cpt1 sheet name using the for loop (ie "cpt" & i) Can anyone help me with getting this to work?
View 9 Replies
View Related
Jun 26, 2014
I have a spreadsheet with two sheets in it.
Sheet 1 = sheet with formulas/functions for data analysis
Sheet 2 = sheet with table
I am trying to reference a column in the table on sheet 2 from sheet 1. Specifically, I am trying to set up Data Validation that will create a list of unique values from a column in the table. I tried:
[Code] ......
There is a space in the header name of the column. but I was unable to get the validation to work even with columns that have just a single word name.
I also tried:
[Code] .....
and that did not work either.
View 3 Replies
View Related
Oct 4, 2009
I need assistance referencing cells in other sheets. I have a summary tab which I would have each refenced sheet listed (named for the activity being billed). In a separate cell in the summary tab, I want to bring back the value of the total amount billed for that activity. The total value is in the same cell of each sheet, as all invoice tabs needing to be referenced are identical in layout.
There is one hitch to this, the sheets referenced have not yet been created (that would be too too easy), and the activity list in the summary sheet has place holder names that will change when the activity being billed is defined (hence prompting the biller to create the sheet to be referenced).
View 5 Replies
View Related
Jan 31, 2014
I am trying to reference a list of numbers in a sheet from another sheet. the problem is that whenever i try to copy and paste the formula it doesn't go to the next number in the ist it goes to the corresponding row. i.e. ='number'!H2 is in row 1 when copied down to row 32 i need it to become ='number'!H3 but instead it becomes ='number'!H33. I also need it to go across; however that was solved by making it ='number'!$H2 but i still cant figure out how to progress the row 1 at a time.
View 3 Replies
View Related
Oct 14, 2008
I have a quick question that'll hopefully have a quick answer. Part of my workbook requires an input box to appear. When you type the name of one of the worksheets into the box and hit enter I want a VLOOKUP formula I've written to compare to that worksheet. Heres what I mean, with some descriptions of what i would like:
View 8 Replies
View Related
Oct 7, 2005
Is there a way to use the "value" of a name in a drop-down list to reference a sheet tab name in a formula?
A1 has a drop down list. When a name (Bob's Sales) is selected from the drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".
View 7 Replies
View Related
Apr 17, 2009
I have two sheets in the same work book and I want to pull values through from one to the other. So its a simple =Sheet2!B45 or whatever yeah?
But no! What Excel 2007 does when I hit enter is to swap the Sheet!2 bit for "C:documents and Settingsmy documentsspreadsheetsmyworkbook!Sheet2!B45" or some such.
Why the hell does it do that and how can I stop it.
View 9 Replies
View Related
Oct 28, 2008
I have a workbook that has a number of sheets with various project stages and dates on them. These details are arranged in small tables displayed vertically down the page. Each worksheet's name is the project code - e.g. 30033_99
I have now been asked to create an overview page that displays all these stages and dates on one page. What I'm trying to do is get Excel to look at the column with the project code and then pull information from the worksheet with that name.
View 4 Replies
View Related
Nov 5, 2013
Ok, I have a spreadsheet that I use to track the majority of my work throughout the day. I have about 10 columns that I use for my own information, and about 4 of them that my colleagues need updates on. I have one sheet "worktracking" and another sheet "printable." Printable has just the 4 columns of info that my colleagues might need, formatted in a way that prints out nicely. Each column in "printable" references a column in "work tracking" like this
='worktracking'!c2
Now the problem that I'm running into is that I routinely delete rows from the "worktracking" sheet, which results in a reference error on the "printable" sheet. I am wondering if there is a way to rework it, such that when I delete a row from "worktracking," then "printable" just does the same, and only retains rows that have data in them...
View 3 Replies
View Related
Oct 10, 2013
I have this MAXIF array function that I am trying to use but I need it to reference the Last Row and not the absolute refererence. I am a novice at VBA. The first max if array is the one with my effort to try and get the last row. It doesnt work. The second one works but references the cell.
Code:
Range("U2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:RC1 & LR=RC[-20],'Monthly Production'!R2C19:RC19 & LR))"
Range("V2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:R8729C1=RC[-21],'Monthly Production'!R2C21:R8729C21))"
View 1 Replies
View Related
Jun 29, 2009
After a formula to copy the contents of a cell in the previous sheet in a workbook, so that if I was to copy the last sheet in a workbook the formula would automatically reference the cell from the copied worksheet and so on if I copied tht one.
Hope this makes sense I have a lot of formulas referencing the previous sheet and everytime I copy this sheet to create a new sheet I have to change the sheet number in the formulas.
eg, in sheet 8 this formula get info from sheet 7 cel J30 ='7'!J30 when i copy the sheet to create a new one (Sheet 9) I would like the new formula to automatically be ='8'!J30.
View 9 Replies
View Related
Dec 4, 2009
I am trying to find out whether it is possible to reference to a sheet index number, instead of sheet name, in a VLOOKUP formula.
For instance, normally I would write:
VLOOKUP(A1,'ExampleSheet'!A1:B10,2,False)
(I hope got the translation to English language Excel correct)
Let's say that the sheet index of Examplesheet is 2 (at least it is 2 the way VBA sees it).
Is there a way I could reference sheet number 2 in the VLOOKUP formula, instead of its name? My sheet names vary, but the formula should look in the same area, regardless of name.
View 9 Replies
View Related
Jun 5, 2009
I dont know why this wont work
it falls over on the ".Columns(6).FormulaR1C1=" line
View 14 Replies
View Related
