If Statement Referring To Another Worksheet
I am trying to insert an If statement using the
Range("c" & rBegRow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
Range("C" & rBegRow).Select
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 and H9 R[-8]C 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.
View Complete Thread with Replies
Related Forum Messages:
Referring To Worksheet Name In A Formula
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?
Referring To A Worksheet In A Closed Workbook.
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
Created Named Ranges Referring To Wrong Worksheet
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.
' 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")
If Statement To Pull Values From Another Spreadsheet / Worksheet
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.
Nested If Or Select Case Statement To Handle A Worksheet Event
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
I’ve 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 doesn’t work.
Nested IFs Statement That Looks At A Range Of Cells Between 2 Tabs Within The Same Worksheet
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.
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)?
Referring To Named Ranges
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.
Referring To Open Workbooks
Dim fname As String
fname = "c:Documents and Settings
Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9
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).
Referring To Multiple Sheets
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.
Referring To Closed Workbook
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.
If Function Referring To Two Cells
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).
Referring To Range() Using Shortcut Notation
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.
Referring To Other Workbooks Via VBA Code
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.
Formula Referring To Empty Cells
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.
Referring To Formulae In The Choose Function
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.
Vlookup - Referring To A Column Other Than The First And Returning Text
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).
Referring To A Cell For The Row Position On A Formula
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)))?
Lowest/Highest Lookup Referring To Another Field
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=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
Code For Form Referring To Cell Deletion
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.
Referring To An Area Relative To A Named Range
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.
Referring To A Sheet In An External Workbook By Position, Not Name
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)"
Concatenate Formula Referring To Cell In Another Workbook
"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
Utilize A Select Case Statement In Target Intersect Statement
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
Defined Names Referring To Nonexistent Range Won't Pick Up Once Existing
I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").
I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.
IF Statement, MAX Statement, And Return A Value
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
If Statement Contained Into A Sum Statement
I have a problem with a formula inserted with control+shift+enter
The formula is
In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.
Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.
The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.
I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
Switch Statement Is Too Long For VBA. To Make The Switch Statement Work Over Two Lines
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
If-statement Inside An If-statement
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
IF Statement Within A COUNTIF Statement: Cell In Sheet "Summary" Count The Number Of Cells In Column DX Of Sheet "Analyses" That Are Greater Than 0
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
IF AND Statement
I need a formula that will sum if certain criteria are true:
If column AY = FLOOR and Column BH = NicK then sum column AW. I have tried sumif, sumproduct and if(and. Nothing is working for me.
If And AND Statement
I am trying to obtain a result of Yes or No if the criteria is met on two values
if the value of B5 equals the value of A2:A3011 on the sub report AND the value of D5 equals the value of C2:C3011 on the sub report return YES if both criterias are not met then return NO
=IF(AND(B5='Sub Report'!$A$2:$A$3011,D5='Sub Report'!$C$2:$C$3011),"yes","no")
this is my latest attempt out of many tries the result never comes out right
IF Within An IF Statement
I am trying to do and IF within an IF like i could do in java. Although i unsure now to whether this can be done within VBA and whether the syntax i am using is correct.
The situation is that i have to two cells with a list of information within them both.
For example cell (2, B) has options 1, 2 and 3 & cell (3, B) has options a, b and c.
If option "1" is selected then either option "a", "b" or "c" can be selected that will therefore show different information in another cell within the worksheet.
Two If Statement
Here is what I have right now, =if(i3:i133="MLB"&if(b3:b133="W",c3:c133))
Basically the statement needs to contain two "ifs". I have a column that has MLB, CFB, NFL, etc and a column that designates wins and losses. So, what I am trying to make it do is say if i3:i133="sport" AND if b3:b133="W" then add those up.
If Only Statement
Is it possible to have an if function that doesn't have an else part to it? So if your criteria is true, assign a value, if it becomes not true, don't do anything, just leave the value as is. Example: =if(a1=1, sum(a1:a5))
If Statement In VB..?
I am looking to get my vb code to do a simple thing, i need it to look at a date on my excel doc and to flag a messagebox that it cannot run due to date passed only if the date is exceeded and to stop at that point.
Using If Or Statement
I have a sheet where A2 is machine name A3 needs to put a -1 if A2 is either Machine 1 or Machine 2. I looked and cant find what I need. I know this should be a no brainer but having problems. here is what I have
=IF(A2="Machine 1",-1,"") or IF(A2="Machine 2",-1,"") this doesnt work
I have also tried something I found
=IF(OR(A2="Machine 1",A2="Machine 2"),-1,"")
If Statement And #VALUE!
How do i say if x is not equal to #value! in a formula? In the context of...@search() returns #VALUE! when the search string is not found. So: =OR(SEARCH("price",$A4) **NOTEQUALTO#VALUE!** ,SEARCH("pricing",$A4) **NOTEQUALTO#VALUE!**)
see the attached file
sheet 2 explains what i am trying to do.
i have data copied from a pivot table in colummn c and d there could be values that are just subtracted . in column called profit
in column called margin it is the percentage profit
but what i need to say is that if column c has no data in it the the margin will be a minus
if there is data in c but not in d the the margin will be a plus
Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.