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 = _
"=IF(Sheet1!R[-8]C[4]<>0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)"
Range("C" & rBegRow).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
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.

ADVERTISEMENT

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.

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

[Code].....

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
obertDesktopvocab.xlsx"

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,

34566
R3456
88900
D2344

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:

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

[Code]....

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:

Code:
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:

Code:
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:

=IF(C38="C"*F38>=0,"1","")

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

Auto Update Of Totals By Referring To A Code

Jun 14, 2014

The below is a data sheet which is going to be designed to keep the records of "sending and receiving" details of wash-garments from a garment factory.

Descriptions;

01. Starting from B12, the dates of sending and receiving are entered in the sequence they occur. Both sending and receiving can occur on the same date.

02. Starting from C12, there are reference codes related to the activity, if Sending the letter is entered as "S", and if receiving it is entered as "R". I included this for the calculation or sorting purpose.

03. Columns D to J includes the break down of sizes of the quantities sent or received.

04. column K simply calculates the totals of the columns from D to J.

Requirement : I need to have the totals of each size wise quantities sent and received in the "summary table" as follows;

>> Size wise totals of "sent qtys" to be shown in D5 to J5(referring to the code "S")
>> Size wise totals of "received qtys" to be shown in D6 to J6(referring to the code "R")
>> The dates to be updated and displayed"automatically" in the rows of the column B, when the code letters("S" or "R") is entered in the column "C"

Special Remarks : The last date of sending or receiving cannot be predefined, the rows(dates)will be kept adding according to the way sending and receiving may occur.

View 14 Replies View Related

Referring To A Sheet In An External Workbook By Position, Not Name

Oct 26, 2008

I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like "=CoolFunction(ExternalWB.xls, 3, A1)"

View 3 Replies View Related

Creating A Named Range And Referring To Its Cells

Nov 30, 2008

here is the snippet of code I'm using

View 11 Replies View Related

Referring To An Area Relative To A Named Range

Sep 7, 2009

I'm working on or any part of it on here, because the work belongs to my employers and would constitute the loss of trade secrets. It would also take me way longer than I have the time to invest to put together a functional duplicate of the relevant part of the sheet just to be able to post it.

With that in mind, what I'm trying to do is refer to part of a range that spans five columns (though it could conceivably span more in the future, so I'd rather not count on that detail) and a constantly changing numbers of rows. The part I want to refer to includes all columns, and all rows except the first and the last in the range.

View 5 Replies View Related

Referring To An Array Column Or Row Of LinEst Function

Jan 13, 2010

I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format A1:A45. Is there a way to refer to a column in an existing array instead?

For example (thanks to Shg for this code): ....

View 7 Replies View Related

VBA INDEX And Match Formula Referring To Different Sheet

Feb 16, 2013

I have table as below of raw data. In which data will increase dynamically both for rows and columns from starting column for eg say Column "D".

I want to get ID data on sheet2 to compare it with some other data for the ID by using formula = INDEX(Dynamic range,MATCH(ID Ref- Sheet2,Sheet1! ref -dynamic,0),column ref) and get the referred ID data on "Sheet2").

On Sheet2
ID
Data1
Data2
Data3
Data4
Data5
Data6
Data7

[code].....

View 1 Replies View Related

Lowest/Highest Lookup Referring To Another Field

Dec 21, 2006

I am looking for formulas for cells B21 and B22 that will return the value from column A corresponding to the occurence of the highest/lowest value of Index compared to cell B:19, that is, the most recent Index data.

This seems to be an excellent candidate for LOOKUP as the data in Column A are unique and sorted.

Then, we have cells B:25 and B:28. On what "Week Ending" did the Min/Max value occurred?

Bonus Question, if Excel encounters more than 1 value that satisfies the formula, what happens?

************************************************************************>Microsoft Excel - Book1.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB24C24B27C27=
ABCD1Week*EndingIndex*AIndex*B*208/11/06561.2385.9*308/18/06561.1382.2*408/25/06556.5375.9*509/01/06566.3389.7*609/08/06584.2410.2*709/15/06595.8397.9*809/22/06566.5463.8*909/29/06633.9375.9*1010/06/06599.1383.3*1110/13/06585.8384.7*1210/20/06588.6382.4*1310/27/06570.8375.6*1411/03/06620.9402.2*1511/10/06620.9402.2*1611/17/06623.6401.4*1711/24/06599.0406.7*1812/01/06647.6426.6*1912/08/06612.2393.4*20****21Lowest*Since***22Highest*Since***23****24All*Time*High647.6463.8*25Date:***26****27All*Time*Low556.5375.6*28Date:***29****Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Vlookup - Referring To A Column Other Than The First And Returning Text

Sep 28, 2006

I have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).

View 9 Replies View Related

Code For Form Referring To Cell Deletion

Feb 6, 2007

This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.

I need to know what code i would have on the 'delete' button of the form.

If you want me to describe it any further i would be happy to, just ask.

View 9 Replies View Related

Concatenate Formula Referring To Cell In Another Workbook

Feb 9, 2007

"Smith B" is text in C1.

My current formula in C3 is a simple reference to a cell in another workbook:

=+'Z:Time Records2007[Smith B.xls]Daily Time'!$C1352

I would the formula to refer to C1 to get the "Smith B" part.

This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.

To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?

=+'Z:Time Records2007["C1".xls]Daily Time'!$C1352

View 5 Replies View Related

Excel 2003 :: Referring To Cell Value In COUNTIF Formula

May 7, 2013

I am using Excel 2003

Cell B13 contains the row number of the first row of data on the sheet and B14 contains the row number of the end row of data. This is because the number of rows vary over time.

I'm trying to do a COUNTIF to test for duplicates in column R (contains integers) and I want to specify the range to count by referring to the value in B13 and B14 in the formula and so I have the following which returns FALSE for some reason even if the value in R19 appears more than once in the range ...

=COUNTIF(INDEX(R:R,$B$13):INDEX(R:R,$B$14),R19)>1

View 3 Replies View Related

ADVERTISEMENT