Formulas Change When Column/row Is Deleted. How Can I Stop That
Nov 11, 2006
I have a spreadsheet that holds the NHL schedule for the season. (See thumbnail)
In the picture I've highlighted the formula I use to determine how many games each team has over the next 7, 14, 21 and 28 days. What I've been doing is deleting the column for each day after that day has passed. (So tomorrow, I'll delete column K, and all the other days will move up one. Sunday will become column K)
My problem is that the formula =7-COUNTIF($K3:$Q3,"—") in cell G3 will read =7-COUNTIF($K3:$P3,"—") after I have deleted Saturday, and thus will only count 6 days worth. (The 14, 21 and 28 columns will only count 13, 20 and 27 as well.) Is there any way I can write this formula so that it stays as K3:Q3? Right now each day I modify the 4 formulas for the 1st team and then copy/paste then over the other 30, but this is rather tedious.
View 11 Replies
ADVERTISEMENT
Sep 18, 2007
I have a sheet (sheet2) that has cells in column L, M and N referencing cells in another sheet (sheet1)(same workbook).
If I delete a row out of Sheet1 it causes the formula in Sheet2 to go to #REF.
Normally the formula would be something like
='Sheet1'!L13
How can I get these formulas to not change just because cells where deleted from Sheet1?
View 9 Replies
View Related
Oct 16, 2007
I have an Excel workbook that I do not want deleted. Is there any VBA code I can do to the document to stop this from happening? which Ic ould turn on/off?
View 4 Replies
View Related
Sep 6, 2013
I have a lot of formulas that I don't want to be changed or deleted, but I still want the user to be able to enter data into the cells.
If I use "Protect sheet" I can't select a cell or enter something into it. I sure there is some setting I have to change, but I'm not sure what to select.
I'm using excel 2007
View 3 Replies
View Related
Dec 6, 2006
I have made a sheet that contains a formula that links to another page and until I have a number in that cell the cell with the formula says NA is there a way I can clear that so it wil be a blank cell until I put a number in.
View 9 Replies
View Related
Nov 13, 2006
when I apply an = 'Worksheet1'!A1. formula to a cell, the respective cell will contain a zero. Is there any way that the cell can be shown as completely blank and still hold this formula?
View 3 Replies
View Related
Nov 20, 2006
I have a list that I have validated as a list with a blank on top to enable data override.
I have a formula that uses the content of this cell, c7 say, to perform so vlookup function and return a result. However, when the cell c7 is blank( meaning the blank cell in the list is selected), the result is #N/A.
I am trying to have that result be a blank.
The formula is as follows, assuming c7 is the data containing cell:
=IF(OR(LEFT($C7,1)="W",VLOOKUP($C7,Table2B_1,7)>9,ISBLANK(C7))," ",VLOOKUP($C7,Table2B_1,2))
View 3 Replies
View Related
Mar 23, 2007
I have this macro that adds whatever formula that is in L1 to the same cell down 66 rows. But it doesn't work like the way I want it to because it's stopping when it sees empty cells...so the problem is that my colum is actually completely empty...so how do I get it to stop when I want to?
Sub every66rows()
Dim i As Integer
i = 1
With Range("L1")
Do Until IsEmpty(.Offset(66 * i))
.Copy .Offset(66 * i)
i = i + 1
Loop
End With
End Sub
View 9 Replies
View Related
Feb 27, 2009
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
View 8 Replies
View Related
Sep 21, 2008
I had a sheet that had a web query imported into it which took up columns A to F. The web query varies in length upon every refresh. In columns G onwards i have lots of formulas that read the information from the query to produce results.
When i imported the web query, on some refreshes it has the ability to push my formulas down in certain columns. I'm assuming this is because the length has changed of the web query maybe? Although it can do it on the opening import of the query which i find strange.
I was told the best way around this would be to put my web query on another sheet, however this doesnt appear to have fixed my problem.
Lets say for example #Sheet1!A1 looks at #Sheet2!A1, #Sheet1!A2 looks at #Sheet2!A2 and so on. This will work fine. When the web query is imported into Sheet 2, its data goes down to cell A72. Upon another refresh though, the length will changed and now the data might go down to cell A81. However, #Sheet1!A72 will have changed its formula to look at #Sheet2!A81 and my formulas on Sheet 1 will now read as follows:
#Sheet1!A69 = #Sheet2!A69
#Sheet1!A70 = #Sheet2!A70
#Sheet1!A71 = #Sheet2!A71
#Sheet1!A72 = #Sheet2!A81 <----- Problem!
Basically the problem with this is Sheet 1 will not be including Sheet 2 A72:A80 which i also need.
View 9 Replies
View Related
Feb 24, 2010
I have a worksheet "Expenses" with columns Month, Category, Expense, Amount. There are four categories and, obviously, 12 months.
In the workbook is another worksheet, "Totals" which lists the months across the top, and the categories down the left. Then I have array formulas in each cell, that look like this:
=SUM(IF(Expenses!$B$2:$B$990="July",IF(Expenses!$C$2:$C$990="Acquisition",Expenses!$E$2:$E$990,0),0) )
This works fine, until people start moving cells around in the Expenses sheet. Say they sort everything they've put in by month, and then they want to move July up to the top because it's the start of our fiscal year. So say they have six expenses for July and they cut and insert them in at B2. Suddenly the formula now says:
=SUM(IF(Expenses!$B$8:$B$990="July",IF(Expenses!$C$8:$C$990="Acquisition",Expenses!$E$8:$E$990,0),0) )
I thought by using absolute cell references instead of relative ones, I could avoid this problem, but that's apparently not the case. I tried changing the cell references in the formulas to relative ones but it still happens then too.
View 2 Replies
View Related
Sep 13, 2006
How would I write this in vb. If a row or column deleted in sheet1 insert in sheet2 the date it was deleted, the coloumn or row cell name?
View 9 Replies
View Related
Nov 28, 2007
I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)
I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!
At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.
When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.
I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.
View 9 Replies
View Related
Mar 17, 2009
I am inputting rows of data to test my spreadsheet and all of the sudden the formulas stop working??
Can someone take a peak at it and see if they can tell why its no longer working at I19?
View 5 Replies
View Related
Jan 29, 2010
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
View 3 Replies
View Related
Jan 25, 2010
The entries are made in Columns A thru C. In Columns F and G I want any value over 100% to have a red background, any value less than 100% to have regular formatting, and I'd want percentages to have white font if an employee is deleted from Column A.
Here's an example of how I have the formatting set up:
Condition 1: Formula is =$E$2="" --->white font
Condition 2: Cell value is >1 ---->red background
Condition 3 Cell value is </= 1 ---->normal format
View 3 Replies
View Related
Nov 6, 2006
I want to be able to reset the value in a combobox, but without the combobox executing code, when it resets. Is there any way of doing this?
I have tried the code below but the ComboBox still executes when its value is changed.
Sub Reset_combobox()
Worksheets("Sheet1").ComboBox1.Enabled = False
Worksheets("Sheet1").ComboBox1.Value = 1
Worksheets("Sheet1").ComboBox1.Enabled = True
End Sub
The workbook containing the above is attached.
View 7 Replies
View Related
Nov 22, 2006
how I can disable an InputBox? I've got some code that whenever someone selects a cell in a specified range, an input box pops up (running a macro) - this can get annoying sometimes though if just browsing. Does anyone know a macro where I can "disable" this?
View 4 Replies
View Related
Oct 12, 2007
I've set up code where when a cell within a specified range of cells is selected, a macro will run. This works all well and good except for when a whole row, column or range containing the defined cells is selected, there is a run time error. There is no situation where I want multiple cells selected to run the macro, so I only want to run the macro when only a single cell within that range is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("GoToRange")) Is Nothing Then
Application.Run "'Macro Test Current MY PFEP Metrics.xls'!PFEP_Filter"
End If
If Not Intersect(Target, Range("GoToRange2")) Is Nothing Then
Application.Run "'Macro Test Current MY PFEP Metrics.xls'!PFEP_Filter"
End If
End Sub
The ranges defined are non-contigious ranges.
View 3 Replies
View Related
Jun 27, 2013
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
View 8 Replies
View Related
Aug 17, 2009
I've got a worksheet_selectionchange macro on a sheet, and another macro that you can run after it. The issue is that when the second macro runs, it also runs the selectionchange macro, and wipes some of the info that the second macro should be copying.
Is there a piece of code that I can use in the second macro to block the selectionchance code from running until it's compelte?
View 6 Replies
View Related
Jan 5, 2012
I've created a very simple formula which counts the number of days from Today and excludes blanks.
I'm trying to set it up so the formula *stops* counting the days once a cell in column C is populated.
Formula so far...
Code:
=IF(A3="", "",A3 - TODAY())
It looks exactly like this this [URL] .....
View 7 Replies
View Related
Feb 8, 2010
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
View 4 Replies
View Related
Sep 4, 2009
First, to i change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.
Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. but its hideing the formulas. once i protect the cells it wont let me to edit the worsheet (eg- cell height, cell width)
View 4 Replies
View Related
May 20, 2012
I am looking for the best way to change the font of cells in a Worksheet that contain a formula. I have used Go To-Special-Formulas but have one slight problem with this method. It highlights all cells that contain an "=" sign. Some cells may be linked (ie cell A2 may say =cell A1). How would I change the font only for cells with a Function such as Sum or Vlookup and not for cells that simply link to another cell?
View 7 Replies
View Related
Feb 14, 2007
I have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this
range("dateranges").values = range("datranges").value
this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way.
Sub FinishOU()
Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range
With Application
. ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
Set wb1 = ActiveWorkbook
With wb1
.Save
For Each sh In Worksheets
Set TestRange = sh.Range("A1:S4")
For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
View 2 Replies
View Related
Jan 19, 2014
In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.
I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.
Book0.5 template.xlsx
View 3 Replies
View Related
Apr 7, 2014
I have a workbook, everyday 2 new worksheets get added to reflect the previous day's work. How can I reflect in a formula to go to next sheet instead of the conventional naming 'Sheet1' G7?
View 6 Replies
View Related
Aug 3, 2006
I am building a small accounts package and need certain work books to open with no toolbars or row and colum headings showing.
View 7 Replies
View Related
Mar 27, 2007
the following macro works great if there is data below cell O4. but is there isn't any (e.g., at the beginning), then the cursor runs down to 65,536. ?if no data exists, how can i stop at cell O5...
Range("o4").Select
'Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
View 2 Replies
View Related
