Function Which References Active Or Selected Cell
I have a spreadsheet which shows a matrix in pounds. But I would like to have a reference cell somewhere near the top which will automatically show the conversion to kilos of any selected cell. Is anyone aware of a function which will do this?
For example, one pound equals 0.45359237 kilos.
Cell A1 contains the reference function to the active of selected cell. If I click on B1 which contains 12, cell A1 would automatically show 5.45. If I click on cell C1 which contains 20, cell A1 would automatically show 9.09.
So I’m thinking it would be something like
=SelectedCell/0.45359237 or maybe =ActiveCell/0.45359237
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
Passing Cell References From A Cell's Text To LINEST Function And A Graph
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following: I250 contains: 10, shows: 10 I251 contains: ="I"&I250, shows I10 But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
View Replies!
View Related
Set Cell References In GETPIVOTDATA Function
Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be: =GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation") I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus: ERG 1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation") .. and this works fine. However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!
View Replies!
View Related
Structured References And With Cell References I Get A Column Of Zeros
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off. I have tried to do it with structured references and with cell references I get a column of zeros!
View Replies!
View Related
Elseif Statement In Vba: If The Selected Cell Falls Between 1/01/06 And 31/01/06 Then Jan Would Be Selected
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months. Sub test() Dim SelectDate As Range Set SelectDate = Range("SelectedDate") If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Jan" ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Feb"........................
View Replies!
View Related
Changing Hard Coded Column References To Variable References
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column. I have been working on this for a couple of days and even tried EE, but to no success. I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo mEnd Set rng = Sheets("Log").[F14:F10000] If Not Intersect(rng, Target) Is Nothing Then If Target = "" Then With Sheets("Log")
View Replies!
View Related
Macro Or Function That Changes Value Of Selected Ranges Of Cells
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges. I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells. Has anyone ever done something similar? Is it hard to program?
View Replies!
View Related
Function: Selected String Contained Certain Phrases
I wrote this function to see if a user selected string contained certain phrases. It keeps returning united states so i am guessing its not cycling through the entire list. Function region2(searchString As String) Dim result As String result = "None" Dim lng() As String lng = Array("arabic", "belg", "bul", "czech", "dan", "dut", "dutch", "euro", "finnish", "french", "ger", "greek", "greenland", "hebrew", "hung", "iceland", "international", "ital", "nor", "pol", "portu", "russ", "slov", "spanish", "swe", "swi", "turk", "UK", "united kingdom", "states") Dim Country() As String..........
View Replies!
View Related
SUMIF Function Based On Row Selected
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in. background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.
View Replies!
View Related
Conditional Concatenate Function: References ConcatenateIf Displays "#NAME?"
I have two tabs that reference the function ConcatenateIf(), defined by me, see below: Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer) For Each cell In iRange If cell.Value <> iLook Then ConcatenateIf = ConcatenateIf & Chr$(10) & cell.Offset(0, iNum).Value End If Next cell End Function Works great. But then, I copy one of the tabs to make a third tab, and suddenly every cell in all three spreadsheets that references ConcatenateIf displays "#NAME?".
View Replies!
View Related
Using Cell(s) References In Path
I'm trying to copy a file from one directory to another directory. However, the source directory, I'd like to have it so it's created from values within specific cells on a Sheet named "PULL". When trying the following, I get an error permission denied.
View Replies!
View Related
Datedif Using Cell References
I'm using Excel 2000 and trying to use the datedif function. I've formated 2 columns as date m/dd/yyyy and left the formula column general I'm entering dates A1: 1/1/2002 B1: 1/1/2005 I'm entering the formula in C1 =datedif(b1,a1,"M") I'm looking for the nmber of months between 2 dates I get #NUM! for a result.
View Replies!
View Related
Flexible Cell References
I want to do a search for the amount of people of a certain age in a column, but I want to be able to vary the amount of cells I look in. So first I might want to look for people aged 15 in A3:A35 and then in A3:A55 to see if there is a difference. Now the optimal way to do this, in my opinion, would be to have a reference that looks like A3:A(B1) and then have the number of the last cell I want to look in in B1, in this case either 35 or 55.
View Replies!
View Related
Incrementing Cell References
Here is what I have at present: At the beginning of a loop, my cursor is positioned on a row. I want to test two cells on that row. Depending upon the result of the two cells, I want to make the row a certain color. After that, I then want to move down to the next row, test the same two cells in THAT row, color accordingly and loop again until I reach the bottom of the spreadsheet. My problem is this: If I can use a loop that lists various conditions, along with the corresponding statements that color the row accordingly, how can I when finished increment the cell references so that the conditions change to refer to the cells the next row down? If a loop is not possible, any suggestions you have for solving this problem would be appreciated. I have about 200 rows so am trying to condense my code; this may be hampering my ability to figure out the solution.
View Replies!
View Related
Cell References As Values ..
If I have values in cell B5:B8 B5=0 B6=3 B7=7 B8=10 I want to have a standing formula in Cells J2:M2 that will always reflect whats in those cells. I don't want to use transpose nor do I wnat something as simple as J2=+B5,K2=+B6,etc. I would like to know if there is a formula that would add the values in a certain row to a cell For instance: I want J2 to reflect what is in B5 AND I want K2 To reflect what is in B5 plus a row which would be the value in B6. I have a reason for doing this as I am going to import 120 ranges beginning at a1 to I30 on each sheet while having the sheet I am importing to have these formulas starting at J2. I tried using +cellnumber+row, but it just gave me the row number and not the actual value in that cell.
View Replies!
View Related
Transpose With Cell References ..
I'm looking to do something similar to a Paste Special -> Transpose, but rather than pasting values or formulas, I want to paste cell references to the cells that I just transposed. E.G. Sheet 1: A1 = 1 A2 = 3 B1 = 2 B2 = 4 Sheet2: A1 = Sheet1!A1 A2 = Sheet1!B1 B1 = Sheet1!A2 B2 = Sheet1!B2 This would typically be an easy exercise, but I have a set 205 rows long and 12 columns wide. A little long to do it one by one.
View Replies!
View Related
Get Cell References From Formula
I am trying to code the following in VBA. Excel Cells have formulae like: '=+BZ165-BZ163-BZ162-BZ160-BZ159-BZ157-BZ153'. Now I want to write code in VBA which will be able to give me the individual cells referenced in this formula: BZ165, BZ163 etc. These names can be stored in individual variables or arrays. I will then use these for further processing. Note in cases where formula refers to data in another sheet like: '=Projects!P49' or data in another excel like '='C:Documents and SettingshoskopDesktop[Annual.xls]Quarterly'!BA$502': I would like to store the path , file name and sheetname in variables/arrays as well. Any pointers as to how I should approach this problem?
View Replies!
View Related
Finding The Cell References Of Autoshape ...
I have a sheet that contains several autoshape objects. What I need to do is find all the cell addresses & then format the same cell addresses on another sheet with a solid colour. So for instance if my first autoshape is in Sheet1!A8, I need to fill say red or blue the same cell on sheet 2 ...
View Replies!
View Related
Copy Formulas With Cell References
I have a formula that references data on another spreadsheet with multiple worksheets. I have a new worksheet for each month (Jan, Feb, Mar, ...). How can I copy formulas in series that keep the cell references the same, but change to the appropriate month. So for the forumula below, I want to fill a series down in a column to represent each month. Right now I have to go in and edit it to change the month to the next month (i.e. change Jan to Feb.) An example of the formula is below. In this case, I would want to fill the series down a column and have the months update in series but keep the cell reference the same. =SUM('[New 2008 sales report linked.xls]Jan'!$M$19,'[New 2008 sales report linked.xls]Jan'!$N$19,'[New 2008 sales report linked.xls]Jan'!$Q$19,'[New 2008 sales report linked.xls]Jan'!$L$47,'[New 2008 sales report linked.xls]Jan'!$K$47,'[New 2008 sales report linked.xls]Jan'!$S$47)
View Replies!
View Related
Cell References Inside A LINK
is it possible inside this link to put a variable reference? for example to have a cell in my excel which in this case would be Report FY09 and if i change it to Repot FY08 the link would be c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4 or in another case if i have the name of the sheet like ='Report FY09'!D4 to do it ='Report FY08'!D4 or even the excel file c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4 to do it c:Report FY08[PPV REVENUES FY 08 - Actual.xls]PPV Actual'!$C$4:$BB$4
View Replies!
View Related
Hidden Columns With Cell References
i have a Macro that gets rid of Hidden columns and Rows. but i kept getting REF# errors. so i changed the macro to Paste as values before the rest of the macro. but then my Subtotal's dont work. macro that only Paste the values of formulas that refer to a hidden cell? Sub Prepare_Workbook() Dim lp As Double Dim wsht As Worksheet Dim Buttons As Object Dim Calc As String Calc = Application.Calculation Application.Calculation = xlCalculationManual Application.ScreenUpdating = False
View Replies!
View Related
Find Cell References On Other Sheets
I've inherited a workbook with 100 or so tabs. There are absolute references to cells on the summary page scattered about all over the place. Hundreds of them, all over everywhere, with no easy way to find them. Now they want to be able to add/delete rows on the summary page and sort. I want to replace their absolutes with named ranges. Is there an easy way to find every cell that references the summary sheet? I have not used Excel since the 80's (when I knew everything about it), but obviously that was many versions back... I jumped into using VBA, and that's starting to make sense, but I'm still missing some of basics. Using Excel 2003.
View Replies!
View Related
Matching Cell References Using Lookup
I have a need to match a reference cell to a list of cells. How do I get it to match the exact contents of the reference cell, not just whichever is closest? I'm attaching the sheet, it's important that the contents of A13 match a string in the AG column exactly or return a null or "none" or whatever.
View Replies!
View Related
Undo Absolute Cell References
I'm using a lot of "activeCell.offset(row,col).address" type of entries to form formulas for me. All of the references come across as Absolute References - $G$24, for example. Is there a different way for activeCell to give me a "normal" address ('G24' vice '$g$24') or a function that will convert for me?
View Replies!
View Related
MATCH -- Using Cell References/variables?
I'm having problems with the MATCH function. If I use =MATCH(A2,'c:myfile.xlsx'!MyRange,0), it works. A2 is the value that I want to look up. 'c:myfile.xlsx'!MyRange is the full path of a range in an another XLSX. However, if I assign the file name to a cell and try: =MATCH(A2,A3,0). where A3 has the exact same path as the line that work, Excel returns #N/A. I also tried a match on a range in the same tab of the same spreadsheet, and it didn't work. Is it possible to do what I'm trying to do? The file location of the external XLSX is going to change, and I wish I could just update one cell rather than every single cell that contains a MATCH.
View Replies!
View Related
Replacing Cell References With IFBLANK
I have two worksheets, one contains lots of data and the other references this data to form a summary. The problem is that having set up all this I have found that not all the references on the summary sheet will always reference a cell with data in it and so bring back a value of 0. Since this summary sheet will be used to enter data into a database I would prefer a blank cell over a 0. I know I can use an IF ISBLANK to do this, but dont fancy going through each cell and re-writing the formula, so- Does anyone know an easy way of changing all the cell references to include the IFISBLANK statement?
View Replies!
View Related
Update Column Cell References
I have a spreadsheet with 2000 rows, and I need to add one column with the IF function that looks at the cell in the column to the left for each row, all the way down. How do I do that without typing in the specific different cell references for each row in my IF column?
View Replies!
View Related
Concatenating Text And Cell References
I need to concatenate some standard text with some cell references but I can't use quotation marks as I would in the normal excel function as it produces an "expected end of statement" error message. Is there a way around this? What I want to say is: Range(B23).FormulaR1C1 = "CONCATENATE(“With ”, Info!C18), “ entries, your expected rate is: ”, H21)"
View Replies!
View Related
Stopping Cell References Changing
I have a cell which references another cell (i.e. =A2). When I insert a cell above A2 the new formula becomes =A3. How do I stop it from changing? I want to insert as many cells as I want above A2 and the cell reference will always be =A2. I played around with $'s but to no avail.
View Replies!
View Related
Cell References In Copied Sheets
I have created some code that copies a master workbook into a new workbook that then creates new tabs for the sheets relevant to week numbers. In the master workbook there are 8 sheets, most of which reference each other in their formulas at some point. The problem arises in the newly created workbook as the sheettab names are renamed e.g "manager" in the master will become "manager wk1", "manager wk2" and so on. All the sheets are copying over great but they the lose their references to each of the sheets within their week groups because the formulas do not change to the correct week references. I am trying to solve the problem by using the Indirect funtion to change the formulas in the master to reference the correct week number, but i feel this is not the best way?
View Replies!
View Related
Putting Formula In A Cell Which References Other Sheets
I am writing a VBA macro in excel. I have several sheets in this one workbook........one sheet for each day in a given month. Sheets for each day are labled as 3_1, 3_2 for march 1st and march 2nd respectively. I also have 4 sheets for the 4 weeks in a given month. The weekly sheets are labeled week1, week2 etc.... What i am trying to do is this: In the week1 sheet i am trying to "put" a formula in say cell 9,12 which sums up the same cell in the first 7 days of the month. But i am having issues. Does anyone have a good way to do this via VBA? David
View Replies!
View Related
Recording Macro Using Cell References In Worksheet
I am working on this project that involves opening several Excel spreadsheets and copying the data into a new workbook. The problem is that the names of the various worksheets I want to copy change every day - it always reflects the date. I open 7 workbooks, go to the sheet named (for example)01.17.07, copy a static range and then paste it to a new workbook. Is it possible to record a macro and use a cell reference in the destination workbook (the one I want to copy to) to tell it which worksheet to copy? So, for example, I would type 01.17.07 in cell A1 of the new workbook and the macro would look for the sheet named 01.17.07 when running? Then tomorrow, I could type 01.18.07 and it would know to look for a different sheet?
View Replies!
View Related
Formula That References A Cell To Deterimine A Tab Name
I have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference. For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6" Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun). I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.
View Replies!
View Related
Hardcoded Cell References In Vba Code
I am modifying some vba code for an Excel workbook. The code references the row 35 a lot (altogether 16 times). Example references: With Worksheets("YTDBudget").Range("c35:c200") For i = 35 To 999 Range("e35:h" & i).Select Worksheets("Budget").Range("A35:D200").Copy _ Destination:=Worksheets("YTDBUDget").Range("a35") Range("e35").Select Rows("35:35").Select Range("A35").ClearContents Currently the "special row 35" is identical for a couple of sheets. Due to the nature of the changes I need to make there is a good chance that "special row" will be different for each sheet (i.e. stay at 35 for one sheet but be 36 in the other). I am thinking of creating a couple of public constants (Sheet1DataBegins and Sheet2DataBegins) that I can then use instead of the hardcoded 35. This presents problems in the I would have to string together the cell references like e35: CellE = "e" & Sheet1DataBegins Range(CellE).Select Which looks kinda ugly to me. This also has the problem with the column being hardcoded ... but that's another problem that I am not ready to tackle ... The original coding is not mine and I am just tasked with making a modification
View Replies!
View Related
Presenting The Math Calculation Instead Of Cell References
Assume A1=25 and A2 = 35. I typed, in cell A3: =A1+A2 which returns: 60. Is there a way to present, in a single cell, instead of the formula references - something like: =25+35 as TEXT ? (Any change in A1 and/or A2 will be reflected in the presentation of those two values I checked some add-ins functions, like "MoreFunc" etc, and INDIRECT - but no results.
View Replies!
View Related
Run A Macro Without Breaking Cell References
I have a macro that cleans up data on one sheet, text-to-columns, flip rows of data, etc. Another sheet references the cells AFTER the data has been cleaned up. However, I can't put the cell references in from Sheet1 to Sheet2 before I run the macro because it breaks the references. Just a simple example, Suppose cell A1 contains the formula: =Sheet2!B5. If I go to Sheet2 and run the macro to clean up the data, when I got back to Sheet1, cell A1 contains: =Sheet2!#REF! The idea is that this is a template, and the user can input aggregate data, run the macro to clean it up, and then go to the other sheet. I can easily create the macro to add copy/paste between the two sheets, but I'm looking for a cleaner way to do this.
View Replies!
View Related
Cells Values For External Cell References
Is there a way of writing a formula to create a reference to an external spreadsheet? I have a folder containing 100s of workbooks all based upon a template. I can create a directory listing of this folder using a macro within Excel. e.g. Directory listing C:My DocumentsReturnsFile1.xls C:My DocumentsReturnsFile2.xls C:My DocumentsReturnsFile3.xls I would then like to use this list of file paths to create links to several different cells within worksheets contained in each of these. I know that if I did this manually for each worksheet the formula would be a variation on =[C:My DocumentsReturnsFile1.xls]Sheet!Cell. Is there a way that I can automatically create these external references? i.e using values in column A (directory listing) to create many external references, say in column B I use my directory list and then a formula or macro populates automatically these external references? The file names in the directory change daily (100s of worksheets!) so I don't want to have to manually input these each time. If the folder containing the workbooks was held on an intranet would there be a similar solution?
View Replies!
View Related
|
TRACKER
