Bypassing #N/A's And Using SUM's
Jan 20, 2010
I'm attempting to SUM a column of data (5 cells). It's possible that I will have #N/A in any number of them, as they call their data from another cell. Specifically, if C16 is:
=INDEX(plan_minutes,MATCH(B16,plan_name,0))
Then it checks for the text in B16 (which is populated via a dropdown list), matching the index to minutes billable (plan_minutes) and returns the value that corresponds. For example, B16 is "Monthly 50" and includes 50 billable minutes, C16 returns "50" as its result.
This becomes an issue when a billing plan isn't selected in B16, and returns #N/A for C16. For formatting and cleanliness, I have conditional formatting on C16 that states =ISNA(C16) and changes the font color to white.
WE'RE STILL NOT TO MY PROBLEM!
I can deal with the #N/A in C16-C20 with conditional formatting. What I can't deal with is my billable minutes total cell (E50).
The cell should add the billable minutes in C16-C20, but doesn't. I believe it's because the information in C16-C20 is actually text, returned by the index, and not a true number. Is there a way around this or should I change my method in some way?
View 9 Replies
Jun 2, 2014
I am creating a worksheet that needs to input information from other worksheets that have not been created yet. I will be using this to input information as time goes on (monthly); however when trying to create the cell, I get a message that says "a formula in this worksheet involves one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell reference." The worksheets I am trying to reference will be created eventually, but I want a formula that will not have to be updated monthly and I can just use it to roll over month to month.
My current formula looks as such:
=IF((IF($A$2=1, '[2014 Corp Engineering Project Codes.xls]Period 1'!$A13, 0))
(IF($A$2=2, '[2014 Corp Engineering Project Codes.xls]Period 2'!$A13, 0))
(IF($A$2=3, '[2014 Corp Engineering Project Codes.xls]Period 3'!$A13, 0))
[Code] ........
I have up to 'Period 6' created so far, the error highlights 'Period 7'.
View 3 Replies
View Related
Jan 27, 2007
The spreadsheet basically copies data by looking in all workbooks in different folders (27 in total) and copies the data across. The only problem is that it keeps asking for the password on all of the spreadsheets when it is opening up the files (all have the same password) - how do I get the code to "know" the code and bypass asking for it?
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh. Cells. Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error Goto 0
End Function
View 7 Replies
View Related
Feb 4, 2010
I have modified the export a range to HTML code from Mr Walkenbachs excellent book and it all works well (still learning my trade with vba!!). The only issue I have is when the code a save as dialogue box appears. As I am looking to automate this process I was hoping to get this code to automatically save preferably to a path ("c:dailyrange.htm" for eg). I have tried various permutations but am really struggling with the concept.
I am using excel 2003.
The code
Sub ExportToHTML()
' Dim ws As Worksheet
Dim Filename As Variant
Dim TDOpenTag As String, TDCloseTag As String
Dim CellContents As String
Dim Rng As Range
Dim r As Long, c As Integer
'Create 7 htmls one for each column of the specified range
For Column = 1 To 7
Range(Cells(14, Column), Cells(40, Column)).Select
' Use the selected range of cells
Set Rng = Application.Intersect(ActiveSheet.UsedRange, Selection)
View 9 Replies
View Related
Feb 17, 2010
I want to be able to run a certain chunk of code in the macro if a certain condition is true, or if that condition is not true then I want to bypass that chunk of code. It doesn't matter if this is accomplished with an if... then... else statement, or some other way.
View 6 Replies
View Related
Jan 5, 2012
I am in the process of transferring data from one workbook to another.
I am doing this simply by clicking on the cell where I want the data to be, typing "=", then clicking on the cell in the other workbook I want it to match. While there might be easier ways to do this, it isn't too terrible and my columns are not in the same order in the two workbooks.
The problem I'm running into, is when I click on the cell I want to copy over, the formula defaults to the fixed formula (puts $ around the cell name) and I want to be able to copy down the formula over multiple cells.
I can click on the cells and press F2 once then F4 three times to get the formula over to the desired non-fixed format, however, I have to do this for 12 different workbooks and on roughly 40 columns for each.
Is there a way to expedite the process or turn off this fixed-formula default?
View 2 Replies
View Related
Jul 7, 2008
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies
View Related
