I was wondering if ther is a way to delete what is in one cell, once something has been enterted in another?
Example A1 has In-progress written in the cell.
Once B1 has Completed.
So once B1 has been entered, the info in cell A1 will be deleted.
I have a Main sheet that has many formula on it that link to several Data sheets. Now sometime I want to delete the data sheets and recreate them by copying a Data template sheet and remaming it to the same name as whatever my data sheet was called.
I see that this basically causes a Ref#! error in all the formula on my main sheet so when the data sheet is recreated it wont recognise it.
How can i stop it changing the formula path to Ref#!?
I want to establish a link from my worksheets. Using the conventional link method I was able to link the values from my first worksheet to the second worksheet. My problem is when I delete a particular row. The reference of the second worksheet will have an error #REF! since I deleted those cells. Is there any way that I can link my two worksheets without any error that even if I deleted a particular cell/row the reference is still intact?
View 2 Replies View RelatedI have this worksheet with 2 buttons and 2 textboxes.the first button is named search literally to search the items inputted in the textboxes and the second button is clear which clear all the inputted items in the textboxes and the search field. I manage to code those things however I have a main problem. How am I suppose to linked a checkbox with those items?
For example I searched this particular item so the checkbox corresponding to that item must show up in the left most column of it. and if I searched multiple items those checkboxes must show up too. I am having a hard time coding and trying since I am new in vba. I also attached a sample worksheet. I am using excel 2007.
Search.xlsm‎
I am trying to create conditional formating of a cell based on the value of another cell whish is linked to another sheet. I an using the "The formula is =$AA$30=1" where the cell AA30 is the cell linked to the other sheet. the problem is I have to do thiss for approx 200 cells ie =$AA$30=1, =$AB$30=1, =$AA$31=1, =$AB$31=1 --- =$AZ$42=1. Do I have to enter the formulea for each cell or is there a way to automate this?
View 2 Replies View RelatedI 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
The problem I am trying to solve is to have a cell return a cell value based on another linked cell. The cells I want to link are in separate tabs or could be another workbook.
For example if cell A5 in sheet2 is linked to A20 in sheet1...A5 has formula(=Sheet1!A20). I want the next cell B5 to look at A5 and return the value of cell Sheet1!D18.
So in essence I want the second cell to look at the first cell and return a value that is 3 columns over and 2 rows up.
I have several excel documents all linked to one unique excel document which I will call document A.
Document A is a performance spreadsheet where I have percentages, sales figures and times, for my sales team.
Each sales person has their own unique dashboard (another excel document) that I am currently trying to finalize.
Their personal dashboards have pie charts with text boxes. The text boxes within the charts link up to their relevant cells in document A.
When Document A is open and I open 1 of the sales person's dashboards. Everything looks good and works, however.
When I close document A the dashboard information changes. For example, the dashboard information could be showing 25% but when I close document A it reverts to 0.25
If I close down the dashboard and open document A back up, change it to 30% then close it. The dashboard recognizes the change BUT reads as 0.30. This is the same with them all.
The only fix I have figured out is creating another tab in Document A then duplicate of all the information required for the dashboards then save the cells as TEXT (as opposed to currency, percentage etc).
This kind of defeats the object because I have formulas set up in Document A and don't want to be doing twice the work.
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.
I have the following details
A B
1 Jim 3rd
2 Jack 5th
Now in the new cell i need A1 + B1 with / in between
Like Jim/3rd
Jack/5th
I have several cells in my spreadsheet A which are linked to cells in another spreadsheet B in the same workbook. My problem is that the cells in spreadsheet A only display the link formula in the cell and not the value of the cell in spreadsheet B which it is linked. I have several other cells in spreadsheet A that are linked to cells in spreadsheet B and they display the values correctly.
View 12 Replies View RelatedI 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...
With the following code I can get shape properties, but I can get it for the linkedcell of the shape.
VB:
Sub GetShapePropertiesSomeWs()
Dim sShapes As Shape, lLoop As Long
Dim WsNew As Worksheet
Dim wsLoop As Worksheet
Set WsNew = Sheets.Add
[Code] ....
I'm using the following code to get the linked cell and return the value to a range. Is there anyway I can achieve this WITHOUT selecting the shape to get the linkedcell. I find using 'select' is cause me formatting problems.
VB:
Sub finalizeclrx()
Dim s As Shape, finRNG As Range, pk1 As String, SphFill As Long
SphFill = Worksheets("input").Range("K65536").End(xlUp).Row
Set finRNG = ActiveSheet.Buttons(Application.Caller).TopLeftCell
For Each s In ActiveSheet.Shapes
If Not Intersect(finRNG.Offset(-5, -11).Range("A1:A1"), s.TopLeftCell) Is Nothing Then
[Code]....
I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet. I have linked all the relative cells to the first sheet but would like create a formula that changes the row reference in each of the links rather than the tedious task of updating each individual link in every sheet.
I have tried using the indirect function but constantly get errors returned, the basic formula i have been using is: =INDIRECT("Summary"!C&,X1)
'C' is the column in the summary sheet that the information should be taken and 'X1' is the cell in the worksheet that will define what row the data should be taken e.g in one sheet the reference for that specific cell will be C5 and the next C6 and so on.
I have a spreadsheet with two tabs.
On tab 'sheet1' I input data in cells A1:A5.
On tab 'sheet2' cells G1:G5 are linked to the cells above, so the same data appears.
Now if I go back and highlight cells A1:A5 in yellow on 'sheet1' ..... how do I get G1:G5 to also automatically highlight in yellow?
I have a worksheet, and I want to place a combobox in a cell, but being able to add values not on the corresponding list. Done almost everything, but cannot find a way to use the position of the cell, so I can dinamicly place a combobox. Ex.- place a combo in a cell, then after you get the value, delete the combo and put it in the cell bellow, etc. In order to do this I need the position of the cell: left, right, height, width.
View 2 Replies View RelatedI 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.
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 2 Replies View RelatedThis is the current layout I have:
Sheet 1: A1:B20, is linked to Sheet 2: A1:B20.
I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.
Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?
Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?
I have a golf teetime reservation spreadsheet. For each tee time there can be up to four players names so I use 4 columns. Times are listed in rows every 10 minutes beginning 6am until 5pm for a total of 66 rows per day. I have a member/customer list that I have on a separate sheet that I've named. I am using the ActiveX combo box next to each possible entry (so 4 columns times 66 rows = 264 combo boxes). It's a little slow and takes a long time to open. I like the ActiveX combo box because it allows me to define the box format and display and it allows search/read/write capability. The idea is the customer calls, I ask what time they want and we arrive upon an available time. I ask for a last name, I click the combo box, type the first letter of the first name and then scroll if necessary to the name and click it to add the name to the time/player cell. If no name in my list we simply type it in and I'll capture it next time I refresh my customer list.
way to dynamically define the linkedcell in the properties so that instead of 264 combo boxes, I might have one combo box that I can click on and then direct my selection to the cell I want it to go into.
I am using the Format as Table feature in 2010 and I am summing a range of cells (C2, D2 and E2) with the result in F2. I have linked F2 three rows below the table in cell C5.
When I add new data in the 3rd row in the table feature I can get an updated result in F3 but my linked cell does not update as it is now pushed to cell C6.
How can I have the linked cell update with the new total from cell F3 as it moves relative to the table?
I'm trying ActiveX controls for the first time.
I need my code to start at the cell that the checkbox is linked to, but I don't know how to do that.
I have several checkboxes on the same sheet. I already have the checkboxes linked to cells, I just need to know how to tell the vba to find the right place to start.
How to link an image to a cells value? The linked image is a range of cells and that range is A5:K30
I will take a macro if that is the only way.
linked images path = cell B2
B2 = ("P:path"&TEXT(TODAY(),"yyyy")&"_stuff"&TEXT(TODAY(),"mmm")&IF(OR(TEXT(TODAY(),"mmm")="Nov",
TEXT(TODAY(),"mmm")="Dec"),TEXT(TODAY(),"y")-1,TEXT(TODAY(),"y"))&""&TEXT(TODAY()-1,"ddmmmyy")&" stuff.xlsx")
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?
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.
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 6 Replies View RelatedI'm working on a project that has a master workbook and multiple files that link to the master. The master workbook calculates values based off of a ton of information: account info, pricing info, quantity info, etc. It is a pretty massive excel file, but that is not where the problem arises.
The files that I am trying to link contain relevant information for specific accounts, including prices. The cells that contain product prices are linked to the master workbook. Example: Destination file, lets call this "Company A", Source file, lets call this "Master". In Company A's spreadsheet there is a column that contains pricing specific to that company. This pricing comes from the Master. Most of the pricing is in column C. What I have been doing is copying the relevant cell in the master and "Past Special, Paste Link" in Company A. I do this for every product in Company A's worksheet.
The goal of this is to automate pricing, so that when there are price changes or account changes, one would only have to update the master and all the separate account worksheets would populate with the correct prices. The method I've been using was working perfectly, until I had to edit the master file. I had to insert and delete a few rows from the master file. I work in excel quite often and link cells alot, so I figured that the linked cells would update to the new format, but they aren't. Ex. If a cell in Company A's worksheet is referencing F46 in the master and I delete row 44, I would like the cell to now reference F45, because that is the cell with the correct information. But instead Company A's worksheet isn't adjusting for the deleted row and is still referencing F46.
The only solutions I have found for this issue are to either have all the linked files open when I am editing the master. But seeing as there are almost 25 files, that doesn't seem very practical. The other solution is to never insert or delete rows, just to add on to the end of the master spreadsheet.
I am trying to chart data from a single cell that is changing over time. The cell is linked to another software program that provides its value. I would like to be able to chart the changes in its value either every time it changes or on some time interval (i.e. every 3 minutes).
View 9 Replies View RelatedI 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.