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?
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
how to highlight entire rows within a spreedsheet based on the information in a cell. I have gone to the conditional formatting and done:
=$A4="Needs Labs" and formatted that red, but when i go to note what areas it applies to, It will not highlight the row. I use the wizard box to decide where to apply the formatting like I saw on an online tutorial and dragged across the row, but nothing happened.
1st thing id like to know. is there any way to inset a picture (not using the background button coz i dont want to tile the pic) and then be able to work ontop of it?
2nd is there a formula to change the order of a list depending on the number (high to low) in 1 of my other box's
3rd how do i take a result from one page and copy it automaticly to another?
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....
Test Data starting at cell A1:
Got most of this code from the web and I can't get it to work. The part I added was the array and loop bit. As a test I specifically renamed one of the sheets in the file to be something NOT in the array, but it still goes through like it exists (I.e. shexist=True). What did I miss?
Dim wsname As String, shexist As Boolean
myarray = Array("Statement of Values", "Vehicle", "Driver Info.", "Revenues by Discipline", "Revenues Geographically", "Employee-Payroll Info. CDN & US", "U.S. Payroll", "Employee-Payroll Info. FOREIGN")
For i = 0 To WorksheetFunction.CountA(myarray) - 1
shexist = False
On Error Resume Next
wsname = myarray(i)
shexist = CBool(Len(ActiveWorkbook.Sheets.Item(wsname).Name))
On Error GoTo 0
If shexist = False Then
MsgBox "The worksheet '" & wsname & "' does not exist in this file or has been renamed." & _
vbCr & "Please check the file and try again.", vbExclamation, "Consolidate"
GoTo THEEND
End If
Next i
I'm trying to print individualized reports from my Excel gradebook for only those students whose grades are below 70%. I thought an If, Then structure could do that, but the code I've come up with keeps printing reports for ALL students regardless of their grade.
totalgradeindreport refers to a cell in the gradebook where the student's total grade is displayed as a percentage. The value is actually a decimal number (I think), so I have tried to build a test for the If-then statement
If totalgradeindreport * 100 < Criterion Then
to control printing. But I am obviously doing something wrong.
Here is the
Sub DandFreports()
Criterion = InputBox("Print reports for all students whose grade falls below what percentage?", "Info", "70")
Test = Criterion / 100
Count = InputBox("Please enter the highest possible student number.")
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 RelatedI have wrote a simple macro/ function that used to work before I decided to improve it. When I run the macro, I can step F8 until the before last line in the following code
Function bla() As Boolean
Set rCell = Cells(1, 1)
If rCell.Value <> "" Then
x = rCell.Value * 0.02 'Works
rCell.Value = rCell.Value - x 'Doesn't work
End If
bla = False
End Function
Excel does stop running the macro, but it doesn't show any error so I don't know what to fix.
Steps to reproduce the problem:
1. Create new Excel document
2. Type any number in the very first cell
3. Open VBA, add module and copy above function
4. Type in another cell "=bla()"
I have trouble with the last part of a backup script, this part checks in subdir and should delete the oldest file (of 4) but since a while back it instead delete the new file saved earlier in the script and I dont know how to fix it. Here is the ...
View 5 Replies View Relatedwhen 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 RelatedI 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))
I have a Master sheet with data on it that corresponds with multiple sheets in the same workbook that I have links to in the Master sheet. It all works fine until I sort the Master and then my links start sending me to the wrong worksheet.
Is it just the nature of hyperlinks that you can't sort, or is something else going on here?
The sheet was working fine, then all of a sudden (i dont know what i have done) the up and down arrows (on my kayboard) wont work when the sheet is protected, when i unprotect the sheet they work fine, i can use left and right. i have checked the scroll lock isnt pressed/illuminated on my keyboard.
I have a spreadsheet gathering information on vehicles. One of the pieces of information is a 'Date Off Strength'. For the vehicles still on strength I have the symbol '#N/A' showing,, this is linked to a sheet using a VLOOKUP formula, is there a way to stop this symbol showing
View 4 Replies View RelatedI 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.
I'm working on a sheet which references a sheet in another workbook. I want to refer to the other workbook simply by it's filename (Forecast.xls) but whenever I save and reopen Excel has replaced the filename with the full path (C:Documents and SettingskoconnorMy DocumentsAuto-sheet project[Forecast.xls])
This is quite inconvinient as I am eventually going to want to move both workbooks together to a different location. Is there a way to make Excel stop working out full file paths and just look for the filename in whichever folder the active sheet is sitting in?
I have a useform that loads up with two buttons and I waat to force the user to chose one of the buttons. However, I have noticed that the user can just press the cross in the top right corner of the form to close the window and therefore not press either button.
Is there anyway I can stop the close form button working?
Is it possible to stop the #REF! error appearing in my formulae when I make a change to my spreadsheet? I have some complicated spreadsheets that take a lot of re-building whenever I need to make changes because of the #REF! error. I tried turning off automatic calculation and that seems to work, until you calculate, then the errors appear. I find myself copying formulae into a text file and then re-pasting back into the spreadsheet after making the changes - there's gotta be a better way! (Using Excel 2003).
View 9 Replies View RelatedI 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.
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.
I have got a spreadsheet with many rows of data. One row is product name and the other is date written. Now I want to calculate how long these entries have been on the log. So lets say I have 10 entries called Pension all with different dates and 10 entries called ISA with different dates. So first I need it to look for all the proucts called "Pension" and then to work out how many are 0-3 weeks old and so on. So i want it to look like this but a formula to work it out for me and to update it automatically.
0-3 Weeks 4-6 Weeks 7-9 Weeks 10 Weeks +
Pension 4 3 3 1
ISA 3 4 1 2
(this does not display very well here but I hope you understand what I mean)
I am using Excel 2003 and I dont think it as a WEEKS function so I will do it in days and then devide by 7.
I know that to look for the product i use
=COUNTIF(H:H,"Pension")
The H:H is because the product is on colum H on my spreadsheet
I know to find the date it is
=TODAY()
So to work out something 3 weeks old it would be
=TODAY()-21
The code that I'm using is supposedly not allow spreadsheet to be opened after a certain date, or does not allow a user to press any of the command buttons and comes up with a message saying the spreadsheet is closed for new entries.But is not working for me.
I placed the Code in the ThisWorkbook module.
[Code].....
I have 90+ rows of data that I want to transfer into a table format on another worksheet. At present I have laboriously been copying and pasting from one to the other but am losing the will to live:
Id like to create a macro to do this for me. Is it a matter of recording the macro to replicate as I copy and paste or is there a better way....I have dabbled with a pivot table but cannot get it to do what I want.
Here is the code from a recorded macro that does one person/row of data but I dont know how to make this automatic for all rows of data (see below):
Sub staff_rpt()
'
' staff_rpt Macro
[Code]....
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
Here is what I would like to do:
Anytime there is a 1 in column B, I would like for columns G-I and columns L-N to display "N/A". I have tried all kinds of formulas, none of which will work.
I have data on Sheet2 and would like to only copy all rows associated with column D2 to the Sheet3. For example Sheet2 has two different values Voice or Data I would like to copy all rows associated with column D that contains voice to Sheet3.
View 4 Replies View RelatedI have a spreadsheet for a couple hundred rows of data, and 6 columns. The fifth column contains a date. What I need to figure out is this:for every 3 rows of data, if the date diffes in column 5 (E), highlight this row and the previous 2 rows
Currently, I've been doing this all manually, row by row - needless to say, it takes me a few hours or depending on how much other work I need to do, a few days.
I am developing a spreadsheet that, once all the code is run has numerous sheets added. On these sheets I have a significant number of rows that contain no data and could be hidden (I dont want to remove them, because later I need to re- import all these rows back to my master sheet). I tried code I found in the forum to hide a row if it is empty but this doesnt work as some of the cells contain formulaes referencing back to another sheet that is hidden.
I am trying, and failing, to write code to hide a row that contains no actual data, but still has formulas in some of the cells.
I have no clue why these 2 formulas don't work. They show blank instead of reporting actual values: ....
View 11 Replies View Related