#N/A Is Omitted (replaced With A Blank Cell)
Jun 4, 2007
How I might be able to streamline the following formula so that the #N/A is omitted (replaced with a blank cell) but there isn't quite as much of a time lag?
-->
=IF(ISNA(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)),"",(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)))
-->
Here is the background information for what I am looking to achieve:
I have two spreadsheets I am working with. The first spreadsheet, the "What Computer has User Logged Onto Log" spreadhseet, dynamically imputs and displays data from the log file of a logon script: the date (column B), time (column C), computer name (column D), username (column E) and IP address (column F). The second spreadsheet, the "Quick Computer Jump Spreadsheet", has many pieces of information (including the computer name (column F) and computer description/room number (column P) -- which are the two important pieces of information there).
Yesterday I found a tip that allows for VLOOKUP via two different spreadsheets. Specifically to have the computer description/room number column (P) information from the "Quick Computer Jump Spreadsheet" filled in as column G in the "What Computer has User Logged Onto Log" spreadsheet. It was this formula here: =VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)
This was great because it meant not having to copy and paste the computer description and room number information into the spreadsheet I want to apply the VLOOKUP to and means I don't have to maintain and update the computer description and room number information in multiple locations, whenever that information changes. This formula worked beautifully, seems to be every bit as quick (with no visible lag) as when calling for the information from inside the same spreadsheet. In the case of dynamic data I could even use the Fill Formula all the way down to the bottom of the spreadsheet so that as other fields were filled, so was the computer description and room column. In short, it pretty much did everything I wanted it to
The next challenge I set for myself then was to see if I could get rid of the annoying #N/A when data wasn't found by the VLOOKUP function. Especially in those cases where I filled down below the last row in my spreadsheet in order to have the computer description/room number information automatically filled in as the other columns (A-F) are filled in.
I did some additional research and found this tip (here: http://www.mrexcel.com/archive2/47600/55248.htm). Using this information I was able to revise my original formula so that it now looks like this:
=IF(ISNA(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)),"",(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)))
This works but is unfortunately much slower than the first (I presume because of the double VLOOKUP required in order to compare and then execute?). Up to 30 to 45 second delays when the "What Computer has User Logged Onto Log" spreadsheet is refreshed.
I found the webpage info, here: http://www.mrexcel.com/td0110.html that had the following alternative ways to write the above formula that don't require the double lookup, thus making them much faster and more efficient to use. The problem here is the examples given are for absolute searches instead of non-absolute like my formula above and I have been unable to figure out how (or if) I can adapt any of these solutions to my own.
Does anybody know of any other way I might be able to write my formula above, to receive the same result, without having to do the double lookup, and thus decreasing the 30-45 second delay every time the spreadsheet goes out to update the computer description and room number information?
View 9 Replies
ADVERTISEMENT
Oct 18, 2007
I am writing some VB code to change a comma separated text to individual columns
Selection.TextToColumns Destination:=Range("O14"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
When I run the macro, it asks me if I want to replace the contents of the destination cells. How do I get rid of this prompt? I have tried to clear the contents of the cells prior to running the macro, and have also coded:
Application.AlertBeforeOverwriting = False
but, it still prompts me.
View 2 Replies
View Related
Apr 2, 2008
Using an excel file sent to me to fill out. File is 97-2003. When I type in text it shows up in the cell. However when I click to another cell the text in the previous cell turns to ########. I can double click it and the text reappears. I am unable to print the spreadsheet as I can only see the text when that cell is active...when I move to another cell to enter more text...can't print.
View 9 Replies
View Related
Dec 29, 2008
I have a "data" sheet that holds information on it and another sheet that has a printable form that uses formulas to extract and calculate the info based on what is entered into the user form. The issue I have having is in a certain number of cells, the formula is being replaced with the result. There are other cells that have the exact same formula and are fine. I can’t figure out why these certain cells are doing this.
There are no control sources overriding the data and I can’t see anything in the code that relates to these cells?
View 9 Replies
View Related
Oct 3, 2006
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.
View 2 Replies
View Related
Jun 12, 2013
I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.
This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.
Is there any VBA code or something that I could run that will update the links to pick up the new files?
View 2 Replies
View Related
Aug 2, 2014
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies
View Related
Aug 12, 2014
I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
View 5 Replies
View Related
Mar 12, 2014
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies
View Related
Mar 22, 2014
I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
View 10 Replies
View Related
Feb 27, 2013
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
View 2 Replies
View Related
Nov 17, 2008
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
View 9 Replies
View Related
May 8, 2014
Im currently writing a payroll sheet within excell and trying to write an IF statement to make a cell blank if referring to another blank cell but it is showing the formula is incorrect. Below is the following IF statement that im using;
=IF( F4 <97, AM4*0.8, IF( F4 >=97, AM4*1.1, IF( F4=" ";" "; AM4)))
View 5 Replies
View Related
Jul 28, 2014
My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).
The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:
Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))
The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".
[URL] ...........
View 2 Replies
View Related
Jan 7, 2013
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies
View Related
Apr 3, 2014
in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies
View Related
Feb 4, 2009
using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..
View 9 Replies
View Related
Jul 11, 2007
I have checked archives, some similiar but not quite what I want.
In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.
View 9 Replies
View Related
Dec 8, 2009
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
View 4 Replies
View Related
Jan 22, 2014
01. I want to Type "Al Raha Pharmacy" in a cell
02. When I type "Al Raha" and I press space, then the word automatically changed to "Al Rahma"
03. I tried this in other cells also but the result is same.
View 1 Replies
View Related
Nov 19, 2013
I have made a table which contains 2 date columns, Both formatted as date (dd-mmm-yy), the second column is calculated as the first column, + 30, The dates work out fine when adding to a date in the first columns, but from a blank cell it displays 30-Jan-00.
e.g
Started
Finishes
12-Nov-13
12-Dec-13
30-Jan-00
09-Nov-13
09-Dec-13
11-Nov-13
11-Dec-13
Rather than delete the rows with the blanks in the Started column, is there a formula that I can use so that the cells in the "Finishes" column is left blank when the "Started" cells are blank?
The current formula for cells in the "Finished" column is:
=[@Started]+30
View 2 Replies
View Related
Sep 26, 2008
Make a cell blank if another cell is blank? This is my formula, it checks two dates. It is in cell M5.
View 4 Replies
View Related
Feb 17, 2014
I'm using this formula
=IF(AND($C$54="C",C3="C"),"C1",IF(C3=$C$54,1,0))
Where C3 compares with C54, I want to have a blank cell if C3 is blank.
View 4 Replies
View Related
Jan 7, 2014
Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.
I tried this but it's not working:
=IF(C3="0","",C3-C2)
View 2 Replies
View Related
Jun 1, 2014
i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows
View 3 Replies
View Related
Jul 8, 2014
I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
View 4 Replies
View Related
Jan 14, 2013
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
View 8 Replies
View Related
Apr 1, 2014
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
View 3 Replies
View Related
Mar 24, 2014
I want to get the text inside the cell which is to the left of the first blank cell of a column.
I show the problema in the attach imageSin tÃtulo.jpg
View 6 Replies
View Related
Oct 22, 2009
The below code is what I have and it works but what I need is for it to loop until it doesn't delete anything. How can this be done?
View 2 Replies
View Related