I'm using a VLOOKUP function to return data from a database query on another page. If the data it is looking for is not available, it displays "#N/A". As data will not always be available, this is an intentional use of the function.
I was just wondering if there is a way to return just a blank cell insted of "#N/A" (Like turning the error message for a broken function off). It just looks like a mess with all of the NAs.
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
View 1 Replies View RelatedI have a spreadsheet with data in all different rows.
I'm trying to delete all the blank cells in between.
I tried go to special, blank, and delete cells,
but for some reason, the excel doesn't select all the blank cells.
It only selects a few blank cells, which is really strange.
I must highlight cells that appear more than once.
What I mean is that if an item in a cell appears more than once I highlight it in blue, if the same happens twice I highlight that cell red etc and so one. does anyone know how to do this in excel.
I am using Arrays throughout a large report and each time I move around in the report or simply open the report I have to wait 5 to 10 seconds to wait for the cells to calculate. Is there a way to prevent this from happening other than changing the calculation option to manual.
Is this and array problem? I don't get this when I use multiple vlookups and choose formulas? Any ideas to speed up or prevent all together?
I have uploaded a sample sheet to illustrate what I am looking to do.
I want to pull out all instances where the four sheets of a 'book' are the same and appear in the same order. (column b is not really important) I have thousands of 'books' in the sheet so doing the task manually is not really an option.
My requirement is to remove the spaces in the text in each cell in a worksheet. i have used TRIM function for this. Sheet1 will have the imported data and sheet 2 has the formula to TRIM. i have an issue when using TRIM on date format. I have used formula TRIM(sheet1!Ax) for columns 1,2 and 3 and TRIM(TEXT(pcload!D1,"mm/dd/yyyy")) for column 4. But the blanks cells in sheet 1 have a value of 01/00/1900 in sheet2. I need the blank cells to be displayed as blank in sheet2.
Sheet 1:
82909U
AB
121
[Code]....
I have a pivot table that compares year-on-year sales data for accounts. If an account didn't have sales for the earlier year, the custom calculation errors #DIV/0! which I can show as at the account level. Problem is at the subtotal levels, I also get the error when I should be getting a value for the group. The error for the one account is affecting my subtotal.
Is there a way I can Show Empty Cells as a VALUE instead of TEXT of say... 0.001 so I can eliminate the error messages?
I am trying to create a formula that will change the cell color in a range of cells in the row. I want the color to change based on the information in a particular cell appearing on a different sheet. What sheet the information is on determines the color the cells change to.
Example: I have 4 sheets I am working with. We will call them A; B; C; and D. I would like the color of cells A5-I5 on sheet D to change to red when the information in cell A5 from Sheet D shows up in any cell in column A on sheet A. If the information from cell A5 sheet D appears in any cell in column A from Sheet B then the color will be yellow, etc.
Excel Version : 2010
Attached File name : <Eliminating duplicates.xlsx>
I want to eliminate the duplicates and plot without duplicate in the yellow cells.
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of values from a 10 x 10 grid that does not have values in every cell.
(I would also like to eliminate any blank cells from the final column.)
I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of all the values from a 10 x 10 array that does not have values in every cell. (I would also like to eliminate any blank cells from the final column.) I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.
how to properly execute that function. One earlier comment was that I was not specifying criteria. I'm not sure from the description and help section of "morefunc" as to what those criteria specs are, other than specifying the whole array. I am attaching a test file with the 10x10 grid & some missing values in A1:J10. Cell L1 has the ARRAY.JOIN function with the entire grid specified, but the output is only the first cell of the grid.
I want the user to press a button on the spreadsheet and this should then result in a popup box appearing on the screen asking the user one question ie "what is the current rate"
The user will then enter for example 20%. I then want to transfer this number to cell A1 on sheet 1.
i had a situation where i import data from web.The sheet contains date & other columns.Whenever,i copy the data and paste it in my worksheet Using Paste Special >> Values i get some numbers instead of Dates.How would i correct the numbers to date format with out loosing the original date format(DD-MM-YYYY). Also the dates are aligned irregularly when i paste them to column.
View 3 Replies View RelatedI have a workbook with several drop-down lists. The names for the lists are on a seperate sheet in the same workbook. I have used data-validation and allow list in all the cells with drop-downs. I have used =NAME in the source. I opened the workbook this afternoon for the first time in a couple of days and I am no longer able to select from a dropdown. I can type in the cells, but if I type something not included in the list, I get the "The value you have entered is not valid" error. So I feel like the validation is working on the cells, I'm just not able to select from a drop-down list. "In-Cell dropdown" is selected.
View 9 Replies View RelatedI have a work sheet with multiple columns and rows. In one column there is unique number which is appearing in multiple rows but it is appearing in different rows more than once like this:
ColumnB
KA0012
KA0012
KA0013
KA0013
KA0013
Now i want to calculate this e.g KA0012 appearing twice as one number similarly KA0013 appearing thrice as one number how can i do it?
I have my results in cells B2:B55 consisting of three signs which are 1, X and 2.
AB1DateResult218-08-131325-08-132401-09-13X508-09-132615-09-132718-09-132822-09-13X925-09-1311029-09-1321102-
10-13X1206-10-1311313-10-1321420-10-1321523-10-1321627-10-1321730-10-13X1803-11-1311906-11-1312010-
11-1312117-11-13X2224-11-1312327-11-1312401-12-1312508-12-13X2611-12-1322715-12-1312818-12-1312922-
12-1323005-01-14X3112-01-14X3219-01-1423326-01-1423402-02-14X3509-02-1413616-02-1413719-02-1423823-
02-14X3926-02-1424002-03-14X4109-03-1414216-03-1414323-03-1414426-03-1414530-03-14X4606-04-1414713-
04-1424820-04-14X4927-04-1425004-05-1425111-05-14X5218-05-1425325-05-1415401-06-1415508-06-1415611-07-14?Sheet1
Is there any way the next falling sign can be predicted in cell B56? And if so, what would be the measures to guess the next sign?
I am joining two columns of data. first col. has size in fraction format: 7 3/8
second column has text string:
HABITAT CINCY POD HAT ERA BLK/RD
Concatenate= HABITAT CINCY POD HAT ERA BLK/RD 7.375
Changing fraction to decimal, needs to remain fraction.
I have played w/ the formatting to make Number w/ zero decimal places and then selected Fraction, but no luck there.
Something seems to have happened to my Excel. When I make changes and close the workbook or application, the pop-up box asking "Do you want to save the changes you made to [Book1]?" no longer shows up.
So far I've tried running a macro with Application.DisplayAlerts = True, but that only seems to work while the macro is running.
I am trying to open a workbook, testt.xls, through visual basic. Below is the code I am using, but when I execute the code, no workbook appears. Excel itself does not even appear. What am I doing wrong?
Workbooks.Open FileName:= _
"C:Documents and SettingsRaMy DocumentsPortfolio Documents estt.xls"
I have a timesheet from which i need to calculate the number of hours put in by employees on a weekly basis.The timesheet can vary with months and number of employees can also change.
Part of code is below:
Dim i As Integer
Dim j As Integer
Dim c2 As Range
Dim startday As Integer
Dim tempnd As Double
Dim tempdys As Double
Dim weekndsum As Double
Dim weekdysum As Double
Dim store_date As Date
m = 12
store_date = thisDate
For i = 20 To 400 Step 5 "value hardcoded
In this i have hardcoded the value,I need it search auto and run till the last used cell.It should replace the number 400 {in my code above}with the last row used.
Hello, I am trying to update a person's finances for this past year. She sent me a workbook that has two sheets with macros. The first sheet is linked to the 2nd. When a name and number is typed into the first sheet it automatically shows up on the 2nd.
My problem is, no matter what number I change on the first sheet under amount, the Total cell on the 2nd sheet gives me the #Value error. I have not changed anything, have not deleted any formulas but no matter what I do, that value error always shows up. I know very little about excel but I thought I knew enough to be able to update this book for her. Is there a special way I should be inputting the numbers on the first sheet so that the value error will show the running total instead of an error?
One of the multitude of areas where I still need drastic improvement is with efficiency. Specifically, avoiding loops.
Are any of you aware of any good, basic level sites, blogs, posts, etc., about better methods or processes? I'm not particularly experienced with programming (I've been a software TESTER for 13 years and just started some programming in recent months), so the more basic the better.
I d like to use a macro where I will put in my personal.xls in order to check and delete , and N/As from any given worksheet
View 9 Replies View Relatedafter i use some formula, when it is not found, it will display 0 or #NA or #REF!.
How i can hide it from printing?
I created an addin with CUSTOM tab (used custom UI editor) to manipulate the ribbon xml.It works fine at home computer and @ work I distributed and install in user> addin directory and activated using excel options> addin>
But the ribbon I created does not appearing (tried on couple of computers) no luck
I would like to parse the below :
Say this appears in A column , I want to parse all the characters appearing after the numbers (i.e., 8.625% /5.875%/6.125% ) in B column - Any excel formula ??
Alere Inc. 8.625% October 1, 2018
Delphi Corporation 5.875% May 15, 2019
Delphi Corporation 6.125% May 15, 2021
So B should column like below
8.625% October 1, 2018
5.875% May 15, 2019
6.125% May 15, 2021
I have the following code:
[Code] ........
HierLastRow shows as 165 in Locals which is correct. When I look at HierUID in Locals the Range is B2:B2 and not B2:B165. How can I make the Range set correctly?
Basically upon opening my workbook, I have some code that runs to loop through all the sheets and make certain sheets visible based upon which password the user enters. However, I also have some code upon activation of some of these sheets to have a msgbox pop up explaining what the sheet is about. I obviously dont want these to appear when the initial loops is going through activating the sheets one by one - I only want these msgboxes to appear when the user subsequently opens one of these sheets.
View 4 Replies View RelatedI have an excel table as below from where I am generating a pivot table:
Region
Project Name
Is this a new Project?
CE
AA
Yes
CE
BB
No
[Code] .......
The pivot looks like this:
I just need to show the records which are "New Projects" (for which "Is this a New Project?" is "Yes"). Hence I am filtering the pivot for "Yes" records only.
All regions are not appearing in the pivot. I also need to show other regions which are missing (viz,ESE & WE) with 0 values.
