#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

Allow Contents To Be Replaced

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

Text Replaced With ####

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

Formula Is Being Replaced By The Data

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

Period Replaced By Colon

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

Links To Other Workbooks Breaking When The Files Are Replaced?

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

Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

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

IF Blank Or If Not Blank Function That Works With Adjacent Cell With Formula In It?

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

IF Statement To Leave Cell Blank If Multiple Cells Are All Blank?

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

How To Populate Blank Cells With Sequence Until Non Blank Cell Is Encountered

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

List - Filling In Each Blank Cells With Value Contained In First Non-blank Cell Above It

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

Replace #VALUE! Error For Blank Cells With Blank Cell

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

Making A Cell Blank Referring To Another Blank Cell?

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

Keep Cell Blank If Referencing Cell Within IF Formula Is Blank

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

Insert Blank Row If Adjacent Cell Value Is Blank

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

Return Blank If Concatenating With Blank Cell?

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

Copy Or Create 'blank Cell' As 'blank'

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

First Blank Cell In Column & Offset To 1st Blank In Row

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

Decimal Sign Replaced With ';' Sign

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

Word Typed Is Replaced With Another Word?

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

Set Date In Table Cell To Be Date In Another Cell Plus 30 But Leave Blank If Other Cell Is Blank

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

Make A Cell Blank If Another Cell Is Blank?

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

Blank Cell If C3 Is Blank

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

Formula To Create Blank Cell Based On Dollar Amount In Another Cell?

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

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

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

Copy Cell Data To Other Worksheet Cell - Leave Blank If Not Currently Filled

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

Find Last Cell In Column And Paste Formula In Next Blank Cell Then Repeat On Remaining Columns

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

Conditional Formatting Blank Cell Based On Another Cell Date

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

Get Text Inside Cell Which Is To Left Of First Blank Cell Of Column?

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

Search Cell If Blank Delete Cell Shift Values Up

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







Copyrights 2005-15 www.BigResource.com, All rights reserved