If Statement Referring To Another Worksheet

Mar 8, 2007

I am trying to insert an If statement using the

With Sheets("Sheet2").Select
Range("c" & rBegRow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
Range("C" & rBegRow).Select
Range(Selection, Selection.End(xlDown)).Select
End With

The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically. the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly. If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G9<>0,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.


Referring To Worksheet Name In A Formula

Feb 20, 2010

I have the following formula that I want to copy to a worksheet named Tankersley:

=IF('Tankersley Input'!B5=0,"",'Tankersley Input'!B5)

I would like the formula to read the worksheet name rather than entering the word Tankersley directly, so that if I copy it to another worksheet named Tyson, the formula would read:

=IF('Tyson Input'!B5=0,"",'Tyson Input'!B5)

How would this formula need to read?

View 2 Replies View Related

Referring To A Worksheet In A Closed Workbook.

May 5, 2009

I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month. Kind of lost on how to possibly make this work. I basically need something like: =location/[workbook.xls]worksheet!cell - where worksheet = B12 and B12 =month(today()) and is formatted to MMMM

View 4 Replies View Related

Referring To A Worksheet Cell Within The Vba Code Of A Custom Function

Jul 26, 2006

How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement

View 3 Replies View Related

Created Named Ranges Referring To Wrong Worksheet

Aug 22, 2007

I have a FOR loop which is supposed to loop through all the worksheets in my workbook, create a named range, then add some data to the right of the range. However, when I run my code, all the named ranges refer to the last worksheet in my workbook. The loop only seems to be partly working, and I just can't work out why.

Sub maxLifData()
' for each worksheet in the workbook, do dmax formulae and add results to new sheet. bhole id on left, depth across top.
Dim ws As Worksheet
Dim wsName() As String
Dim i As Integer
Dim mCount As Integer
Dim nr1 As Range
Dim nr2 As Range
Dim head1 As String
Dim head2 As String
Dim wsRangeName() As String
Dim maxF As String
Dim fRange As Range
Dim rng As Range
Dim mRng As Range
head1 = "STCN_DPTH"
Set fRange = Range("G3")
Set mRng = Range("A2")
Redim wsName(Worksheets.Count)
Redim wsRangeName(Worksheets.Count).............

View 4 Replies View Related

If Statement To Pull Values From Another Spreadsheet / Worksheet

Feb 7, 2008

I have two spreadsheets, i suppose one part of the solution may be to copy spreadsheet 2 into a new worksheet in spreadsheet 1, but this is my current situation:

Spreadsheet 1 has "Employee lastname" in column F, "Employee Firstname" in column G. Column A in this sheet needs updating with the data populated in column H for that employee in spreadsheet 2 - the lastname is contained in column B, firstname in column C - so i need to to a match.

View 12 Replies View Related

Nested IFs Statement That Looks At A Range Of Cells Between 2 Tabs Within The Same Worksheet

Jun 8, 2006

I'm trying to create a nested IFs statement that looks at a range of cells between 2 tabs within the same worksheet, determines if there is a value of 1 in ANY of those cells, and if there is, to multiply the value of yet another cell by 1 in a cell on the second tab. So, if 1 is a value in any cell of Tab 1 cells C82 to C86, then in Tab 2 cell B21, multiply 1 by the value in 'Tab 1 Cell H5'.

I've tried this =IF(('LM fundamentals'!C82>=1), ('LM fundamentals'!C83>=1), ('LM fundamentals'!C84>=1), ('LM fundamentals'!C85>=1), ('LM fundamentals'!C86>=1), 'Order Information'!$B$21!=(1* 'LMfundamentals'!$H$5!))

I even tried a vlookup, having given the cell range in Tab 1 a name, etc. But I have little skill with logic statements, apparently.

View 5 Replies View Related

Nested If Or Select Case Statement To Handle A Worksheet Event

Jan 31, 2010

I would like to have a nested if or select case statement to handle a worksheet event.
The conditions it will check are:
1.Make sure target is w/in range, otherwise EXIT
2.Make sure that target offset value is not empty, otherwise display message
3.All is good, open form

Ive tried various formulations and positionings of the statements ,but not all conditions are met with equal success.

What happens is I get the the first two conditions, but the third doesnt work.

View 6 Replies View Related

Referring Cells

Jun 17, 2009

I am trying to refer to a cell. Not sure if thats a good way to put it.

Example: I am in Cell B1 and I want to have a formula that says if C2 is 5 then A1 is 5, else B1 is 500.

I need A1 to be completely blank with the formula only in B1 and C2 has the number typed in it.

How can I put a number in A1 without having a formula or any text in that cell (A1)?

View 9 Replies View Related

Referring To Functions Within Function IF And THEN VBA

Nov 5, 2012

So far I have 2 Functions with a sub to test to do certain calculations.

[B]Private Sub test_getBaristaSalary()
Dim InputYear As Integer
InputYear = InputBox(Prompt:="How many years of Experience?")


View 1 Replies View Related

Referring To Empty Cell?

Jun 12, 2014

I need a better way to refer to an empty cell. I'm using “” but if the cell contain a formula but show empty it affect the conditioning format I'm using this =AND(cell <> "", existing_rule)

View 9 Replies View Related

Referring To Open Workbooks

Jan 27, 2009

Sub vocab6()

Dim fname As String
fname = "c:Documents and Settings

Workbooks.Open Filename:=fname

Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9

End Sub

I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).

View 9 Replies View Related

Referring To Other Workbooks Via VBA Code

Oct 5, 2009

I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.

I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.

I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.

View 12 Replies View Related

Referring To Characters/numbers

Nov 13, 2009

Lets say we have several numbers and combination of letters/numbers. What can we use to commonly refer to them correctly,


I tried to use

Like *####

But thats not correct

View 3 Replies View Related

Referring To Sheet Name Through Variable In VBA?

Feb 21, 2012

I have a workbook with a list of staff in it. The format is: the manager's name is in row 1, the 8 or so team members are listed below. And again in row 20, there is a manager's name with 8 or so team members listed below. This is duplicated over many columns and multiple sheets. The sheets are all named after the ops-managers who manage all the managers on their respective sheet. The book is called "stf.xls"

I'm using the following code to look up a staff member and return who their manager and ops-manager is to the relevant cells in a different workbook:

Sub AdvisorFind()
Dim Wsht As Worksheet
Dim Slookfor As String


My problem is how to return the manager's name for a staff member in the middle of the book. Activesheet. is the last worksheet so no good. Wsht.name.Range(manager) is what I want to do but I don't know the correct syntax.

View 2 Replies View Related

Referring To Chart In VBA Code

Jul 31, 2012

My first version is this:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19

Works without problems, but I really don't want to use "ActiveSheet" there

I changed it to:

Dim chtTR As ChartObject
Set chtTR = shtData.ChartObjects("Chart 1")
chtTR.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19"

and it gives me

"Object doesn't support this property or method."

View 3 Replies View Related

Referring To Closed Workbook

Mar 31, 2007

I have the following formula to extract a specific cell from a closed workbook. It works fine. I want to be able to make the file name refer to a another cell so I can create a spinner to change it. For example:

='C:Documents and SettingsTom Desktoplabor[01_032407.xls]Stats'!A4

Cell A1 would be 01_032407, and I would replace [01_032407] with [A1]
but it does not work.

View 9 Replies View Related

Referring To Named Ranges

Dec 11, 2007

I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself.

Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.

View 9 Replies View Related

Referring To Multiple Sheets

Feb 24, 2010

I have every month a product demandplan (for each of 150 models) in a workbook. This workbook contains a seperated sheet for every sales region and is saved on network.

What I want to do is a new workbook be able to pull in the information from the closed demand plan. That part is easy. The hard part is that I want to select the sales region from droplist and than get demand per model. VLOOKUP won't work since I can not select the region and INDIRECT doesn't work with closed workbook.

View 9 Replies View Related

If Function Referring To Two Cells

Jun 29, 2007

I have this formula:


which is meant to return the number 1 in a cell if C38 has the letter C in it and F38 is equal or greater than 0. I have met both criteria and it returns an error of #VALUE! What am i doing wrong? NB in F38 there is a Vlookup formula that returns a number (in this case it is 0).

View 3 Replies View Related

Difference In Two Methods Of Referring To Other Workbooks?

Feb 22, 2014

These workbooks are located in different folders.

The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.

{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))

What is the difference between using these two different methods?

View 1 Replies View Related

Using Countif Referring To Specific Cell

Nov 27, 2013

Can you set up countif to work to refer to a certain cell instead of a specif name or text.. for instance

HTML Code: 

countif(a36:b36, "c1"

View 3 Replies View Related

Listbox Referring To Hidden Range

Apr 11, 2014

I have this code for a texbox to search into a listbox

It's working properly but sometimes it doesn't look at all the names in the list.. probably because some of the rows are hidden?

View 4 Replies View Related

Referring To Variable Array In Formula?

Mar 14, 2012

Is it at all possible to refer to a array that may change in a formula?

For example I need to use a Vlookup formula, however the table array will change depending on the value of another cell.

I need the user to be able to select the column heading that the lookup should work off from a drop down list. So if the user selects column heading C, the array should start from column C though it will always end at column Z. If the user selects column heading Y the array would be Y:Z.

View 7 Replies View Related

VBA - Referring To Named Range On Different Sheet?

Jun 20, 2012

I just named a range on a sheet and would like to paste that range on a different worksheet within the same workbook. I can't figure out how to refer to the range. I've tried several different variations so far. Example below:

ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
'Add new sheet and paste data
ActiveSheet.Name = "Raw_data_Sector_Summary"
ActiveWorkbook.Sheets.Add.Name = "RawData"
Range("A1").Formula = "=RawData!(RawSectorData)"

View 5 Replies View Related

Referring To A Cell For The Row Position On A Formula

Jul 8, 2008

I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.

I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?

View 9 Replies View Related

Referring To Range() Using Shortcut Notation

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

Referring To Code From Multiple Events

Jul 14, 2006

If I have a button on each of numerous sheets, how do I get all of the buttons to refer to the same bit of code, without having to copy it to the click event of each?

View 4 Replies View Related

Formula Referring To Empty Cells

Dec 19, 2006

I think I understand what you were talking about now with the generation of the watchbill using the rand() and sort. You were trying to get me to get rid of the whole system I was using before and use only the rand and sort. I thought you were asking me to incorperate the rand sort thing into the randomization process I already had using offset etc. The whole thing works good now, however, because the column Ive designated for the roster names may or may not always be filled the formula sometimes refers to empty cells in that column thus producing 0s on the watchbill. I was thinking maybe (if its possible) having a formula to identify a 0 and if so skip to the next cell down. The formula would repeat until it found a name without a zero in it.

View 5 Replies View Related

Referring To Formulae In The Choose Function

Jun 27, 2007

I have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them in line as value1, value2 etc into the CHOOSE formula e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,))

but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.

View 4 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved