I Get The Location Of The Cell Rather Than The Value When Linked The Cell
why when I linked (this cell = to this cell), I get the location of the cell rather than the value? ='[Travel Expense-Mar 09.xls]AAA-expenses!$D$4.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Get User To Choose Location Of Linked Data
I've have a spreadsheet where 4 cells are linked to another workbook via a vlookup. the problem i have is that a lot of users can update this external book, or it can be saved as a seperate spreadsheet somewhere else on the network. If it was up2 me i would have them only update the one sheet, but as it stands its not. So what i want to be able to do is put some code onto a button on the sheet, from here i want the: Application. GetOpenFileName method to open....but from here i want them to be able to pick the cells where the data is situated. Any clues..... i can get as far as them selecting a workbook. Do you think i will need to create another userform?? Maybe RefEdit? I'm not sure.
View Replies!
View Related
Increment Cell Location Up By One Cell For A Column
Increment all cells located in Column H up by one (the one above it) cell Except for the header cell (since its the header)(H1), H2 will always be a blank due to the make up of my database so that will not cause the header to be re-written. Column H is not an entirely filled column and some cells are empty (if this info was needed in optimizing the code), Im seeking the quickest and easiest way of implementing this since the database is very large.
View Replies!
View Related
Linked Cell
I have a cell with a link to a closed worksheet. The problem I am experiencing is when the linked worksheet is open the data found and the cell automatically populated. When the linked workbook is closed, I get nothing. If I check links/source I get the message Error: Undefined or non-rectangular name, neither of which are true statements. Linked workbook closed Management - Prepared HVHWHXHYHZIAIBICIDIE7 £ - £ - £ - £ - £ - £ - £ - £ - £ - TRUESpreadsheet FormulasCellFormulaHV7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'L:Customers StatsSaladsDatabase salads2.xls'!Description)),IF('L:Customers StatsSaladsDatabase salads2.xls'!Period>=$HQ$7,IF('L:Customers StatsSaladsDatabase salads2.xls'!Period<=$HQ$6,'L:Customers StatsSaladsDatabase salads2.xls'!SaladVal))))/1000}HW7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'L:Customers StatsSaladsDatabase salads2.xls'!Description)),IF('L:Customers StatsSaladsDatabase salads2.xls'!Period>=$HQ$11,IF('L:Customers StatsSaladsDatabase salads2.xls'!Period<=$HQ$10,'L:Customers Linked workbook open Management - Prepared HVHWHXHYHZIAIBICIDIE7 £ 3,624.89 £ 4,144.82 £ 373.62 £ 664.80 £ 764.06 £ 373.62 £ 235.14 £ 263.18 £ 314.38 TRUESpreadsheet FormulasCellFormulaHV7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'Database salads2.xls'!Description)),IF('Database salads2.xls'!Period>=$HQ$7,IF('Database salads2.xls'!Period<=$HQ$6,'Database salads2.xls'!SaladVal))))/1000}HW7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'Database salads2.xls'!Description)),IF('Database salads2.xls'!Period>=$HQ$11,IF('Database salads2.xls'!Period<=$HQ$10,'Database salads2.xls'!SaladVal))))/1000}HX7{=SUM(IF
View Replies!
View Related
Current Cell Location In A Cell
I need a function(s) that can indicate the current cell column and row in two different cells. e.g. Let's say in Cell A1 should indicate the column of current selected cell and B1 indicates the row of current selected cell. A1 would read - 7 and B1 would read X if the current cell is at X7. I do not know VBA. If you give VBA solution, please give me link on how to activate the codes.
View Replies!
View Related
ComboBox With Two Linked Cell
I have to fill out two cells (lets say A10 and A11), with the same data taken from a list of given data. The given data is placed in one column. I have created Combo Box where the ListFillRange is the given data area and in the LinkedCell I tried to write two cells but Excel doesn’t accept list of two cells. For example, I tried to write in the LinkedCell A10,A11 and A10:A11 and A10;A11 but none of this syntax worked. I can’t write A10 only in the LinkedCell and then to write a function A11=A10 in cell A11, because in some cases the content of cell A11 will be changed by another Combo Box linked to cell A11 and that Combo Box will override any function that will be written in A11.
View Replies!
View Related
Get The Value Of A Checkbox's Linked Cell
I am writing some VBA code in Excel 2007 and am stuck on a property with the standard form check box (not OLEObjects). I would like to check and find out what the value is of the linked cell for a checkbox... Like, if a user clicks the checkbox, it changes the linked cell value to true or false... But I cannot figure out how to "lookup" or "go and get" this true or false value through code...
View Replies!
View Related
CommandButton Linked To Locked Cell
I have created a simple excel document with Sub randomnumbers() Range("B1").Formula = "=randbetween(1,100)" Range("B1").Value = Range("B1").Value End Sub There is a button that i click and it creates a random number to cell 'B1'. it works fine, but i need to lock the document so that the user can only access one cell... 'A1' when i lock the document, and i click the button, it gives me a '400' error. i think it is because the button points to a locked cell.
View Replies!
View Related
Click-able Linked Cell To Another File
I have attached an example spreadsheet to describe what I am working with. I was wondering if there is an easy way to have it so that everything in Column A is hyper linked to the ship bill file. This list can exceed over 1000 and to hyper link each line is too much, is there an automatic way to do this? For example. I would like the value '00001 in cell A2, to be hyper linked to the file "Shipping Bill - 00001" So that I can just click on the link and open up the file. The shipping bill files are in the same folder as the example spreadsheet.
View Replies!
View Related
Auto Copy In To Cell On A Linked Sheet
I am wondering if there is a formula/macro/VBA that will find the last date entered in a column and fill that date in to a cell on a linked sheet. example: sheet ONE has columns C through S. (each customer has its own sheet) On this sheet, in column H the date of the last payment made is entered. This date of course changes as payments are made, so the row would change as well. sheet TWO has columns C through O. (all customers on this one sheet) The rows on this sheet remain the same for each individual customer. I would like the last date entered on sheet ONE column H to be auto copied to sheet TWO column O. Is there a way to have the last date entered on sheet ONE auto copy to Sheet TWO?
View Replies!
View Related
Finding The Location Of A Cell
i am trying to find the location of the current cell and use the information to print it to screen. For example, if the cell A1 is selected, i need to get the location A1 and use this value for a vlookup. is there a method that returns the current cell location "A1"?? activecell.address
View Replies!
View Related
Find And Replace :: Cell Moving Down In Linked Sheet?
I have a spreadsheet which is linked to other spreadsheets. When I use "find and replace" on this spreadsheet, it causes one of my linked cells to move down one row. Why does it do this? This particular cell has nothing to do with the "find and replace" option. I have no idea how to stop this from moving this cell down.
View Replies!
View Related
Allow Drop-down Box Selection Linked To Locked Cell
We have Microsoft Excel 2000 and i have locked all worksheets separately. I have enabled Data Validation on a range of cells and enabled a list box. The way the workbook works is you log in with a name, the cell range references the names in each cell to another cell which was your login name. Those that match, after clicking a button will unlock those specified cells. Then users can use the data validation listbox function to select only their name from the cell that was linked to the log in name box. (the users name that logged in only shows in the list). The problem is, all cells by default are locked in the range with the names in. You cannot delete the values in any other way, unless you unlock the cells. (at least thats what is supposed to happen) My question is: Is there any code anybody knows that i can use to make excel copy what excel 2003 does, im sure that the problem is a fault with excel 2000 and microsoft fixed it when they released excel 2003.
View Replies!
View Related
Compare DDE Linked Cell For Percentage Tolerance Of Last Value
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
View Replies!
View Related
Move/Cut Cell To Another Location
I would like to how I can move the words "Total List Price" over to column "C" rather than have it displayed in column "A". If someone can please advise me as to what part of the code I must change in order to have these changes take place Private Sub CommandButton1_Click() Selection.SpecialCells(xlCellTypeFormulas, 16).Select Selection.ClearContents Range("A8").Select Selection. Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 9), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Application.Goto Reference:="models2" ....................
View Replies!
View Related
Save To Specific Location As Cell Name
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View Replies!
View Related
Formula To Calculate Cell Location
i'm not sure how to describe this. i've never had to do this before. here is what i'm trying to do. and it's not working. =MIN(B2:B(G2-G3)) basically instead of saying =MIN(B2:Bx)....i want the "x" to be variable based upong the G2-G3 condition. can this be done some other way?
View Replies!
View Related
Store Cell Location In Variable
way to store a cell's location to variables. Something like: int a, b Cell(a, b) = ActiveCell I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.
View Replies!
View Related
Cell Value Based On Mouse Location
Is there a way to have excel track where my mouse is hovering(say if I have it hovering over cell A4) and have it return the value of A4 in a different cell (e.g B7)? This would occur in real time - that is, as my mouse moved over the spreadsheet, B7 would update in real time. Note I would also accept someone selecting the cell - hovering (no select) would be ideal but selecting is OK.
View Replies!
View Related
Linked Cell Combo Box Hangs Running Macro
I am trying to create a menu that calls macros based on your choice using a drop down combobox (from the control toolbox). Everything works except: When i try to reset the drop down to "Main Menu" using the Linked cell $B$1 excel hangs and suts down evertime. What i would like it to do is reset the drop down to "Main Menu" each time one of the other drop downs are selected. I have really enjoyed this forum and it has been a great resource for me in the past I hope someone can help. I have attaching the file for review.
View Replies!
View Related
Macro To Change Linked Picture (Camera) Cell Reference
I am a complete newbie with the camera tool. Is there any way to make the camera tool dynamic so that the cells the tool is referencing can change upon the click of a button or some other form of switch? I am creating my first dashboard and this is the last step for my report to be completely dynamic. ----------------------------------------------------------------------- Never mind, I apologize i just figured this one out... Create a user defined formulaIn the UDF Create an if statementThe if statement's condition is the logistics in the shift mechanismThe value if true is the first group of cells you want in the snap shotThe value if false is the second group of cells you want in the snap shot, The range you want to toggle betweenRename the cell reference of any snap shot you have taken with the UDF name. In my example my UDF is "Dude" so when you select the camera tool it shows =Dude as the cell referenceReference a scroll bar to the conditional cell you selected in the if statement or choose statement. This is the shift mechanism If you want to toggle between more than 2 sets of data use =Choose instead of =if
View Replies!
View Related
Copy Cell Values While Id Value Is Same To New Location Then Repeat For New Id Value
what I need to do is , while an id value is constant copy 2 adjacent cells to a new location on the same row as the first occourance of the id value, then if the id is the same on the next row repeat the copy and add these 2 values to the cells next to the ones previously copied. Once the Id value changes then the copy restarts on a new row which is the same as the first occourance as the new id value and repeats the same sequence as described in the first statement. I have attached a sample spreadsheet showing an example of the source and how I would like it to end up, the colour is only there to seperate the outcome from the source and is not required in the code.
View Replies!
View Related
Find MIN Location, Show Cell Next To It
I imagine this is pretty simple but for the life of me I can't figure it out. Here's what I want to do... Find MIN in Column F After finding the MIN in F (let's pretend F222 is the current min) Show the text in the field next to it (in this case display text from E222)
View Replies!
View Related
Creating Variables Based On Cell Location
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View Replies!
View Related
Add Position Shape To Cell Location
i need to loop through a column of values and get each value Dim LastRow2 As Long Windows("SCFOutput.xlsm").Activate Sheets("Q2SCNeg").Select Columns("A:B").Select ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select LastRow2 = ActiveCell.Row - 1 For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells MsgBox ActiveCell.Value Next c i did this but it selects the first blank row (row 15) and gives me an empty message box 14 times (which is right, but i need 14 values) the reason i need the loop is because the number ov values will always change
View Replies!
View Related
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
Search Multiple Columns To Provide Cell Location
I have text (eg. 1-* or 10-* or 11-*) being entered in the following columns Q21:Q128 R21:R128 V21:V128 W21:W128 AA21:AA128 AB21:AB128 AF21:AF128 AG21:AG128 AK21:AK128 AL21:AL128 AP21:AP128 AQ21:AQ128 AU21:AU128 AV21:AV128 AZ21:AZ128 BA21:BA128 BE21:BE128 BF21:BF128 BJ21:BJ128 BK21:BK128 I would like Excel to search the above columns provide the cell location (eg. 1-10-*) is located in cell BF26
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Browse For Picture File And Put Location In Cell
I am trying to code where when someone hits the command button it will popup with the browse form and they find the location of the file and it puts there choice in a cell. don't know if it matters but the location would always be a picture. Here is what I have but it shows -1 in the cell HTML Dim strFilePath As Variant strFilePath = Application.FileDialog(msoFileDialogFolderPicker).Show shUserInformation.Range("D12").Value = strFilePath
View Replies!
View Related
Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward. Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference? The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
View Replies!
View Related
Disable User Access To Master Sheet Through Doubleclicking Locked Linked Cell
I have a master spreadsheet that houses most of the information that remains in my control, and several other user spreadsheets that update cells from that master through links. I am having some problems lately with users doubleclicking the locked linked cells in their workbooks, and excel then wanting to open and give the user access to my master spreadsheet. Is there a way to disable this feature? I would like it to not even acknowledge the action if that's even possible because it confuses them as to why they are getting a prompt to open another document.
View Replies!
View Related
X & Y Scale Chart Max,min,tick Linked To Cell Values
I am using Jon Peltier's worksheet_change event code to try and automate chart axes. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$AG$5" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _ .MaximumScale = Target.Value Case "$B$3" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _ .MinimumScale = Target.Value Case "$AG$7" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _ .MajorUnit = Target.Value Case "$L$3" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _ .MaximumScale = Target.Value Case "$N$3" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _ .MinimumScale = Target.Value Case "$AH$7" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _ .MajorUnit = Target.Value Case Else End Select End Sub However, I have some of the cells setup as formulas....but worksheet_change apparently only updates values when manually changed.
View Replies!
View Related
Interpolation Given A Variable Table Location & Location Of Data Within
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow. simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View Replies!
View Related
Reference Cell Based On Button Location & Determine Button Used
I received this code from an example I found once upon a time that was originally submitted by someone else. Right now the code enters the current date in a cell of the same row as the checkbox when it's checked. I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro. First of all, I don't know how to reference a Forms button in VBA. Sub Process_CheckBox() Dim cBox As CheckBox Dim LRow As Integer Dim LRange As String LName = Application.Caller Set cBox = ActiveSheet.CheckBoxes(LName) ' Find row that checkbox resides in LRow = cBox.TopLeftCell.Row LRange = "B" & CStr(LRow) 'Change date in column B, if checkbox is checked If cBox.Value > 0 Then ActiveSheet.Range(LRange).Value = Date 'Clear date in column B, if checkbox is unchecked Else ActiveSheet.Range(LRange).Value = Null End If End Sub Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:.............
View Replies!
View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place. Is someone aware of a function which will target a relative location instead of an exact cell?
View Replies!
View Related
Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It
I have 2 problems relating to LOOKUP. Not sure if Excel can perform these calculations as they could get to complex. Problem 1 Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example: Tab 1 (Never changes) AB Bob1 Jon2 Fred3 Tab 2 (Dynamic, changes each week) AB Jon Fred Bob So it would work as follows. Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result. Problem 2 Weekly league rank table that shows position movements week by week Example. Week1 1Jon 2Bob 3Fred Week2 1FredUp 2 2BobNot Moved 3JonDown 2 Can Excel calculate/show the actual movements of league positions?
View Replies!
View Related
Join Cell Text And 1 Cell Date From Cell With 2 Dates
I have three cells in A2:C2 which require user to input some data. What I want to achieve is to combine the data from A2:C2 in D2. C2 is a field which user will input the date. He might key in 21/08/06 or 21/08/06, 30/08/06 I have tried using below formulas in D2 but without success. =A2&" " &B2&" "&(C2) =A3&" " &B3&" "&DAY(C3)&"/"&MONTH(C3)&"/"&YEAR(C3) (doesn't work if there are 2 dates. I have attached a file which shows 3 scenarios if user input 1 date and 2 dates.
View Replies!
View Related
|
TRACKER
