Missing Gridlines When Table Data Subsequently Deleted From Sheet

Jul 23, 2014

On occasion I cut a table from a website and paste into Excel. If/when that data is subsequently deleted from the sheet the gridlines no longer show. I've tried turning the gridlines on and off to know affect. I've tried setting and clearing borders, but that made no different to the gridlines either. Even if I highlight the entire sheet and delete it makes no difference. I can't think of anything else to try.

View 2 Replies


Missing Gridlines In Excel?

Jul 3, 2014

Not sure how it happened, but I lost all gridlines in Excel. I was working in a workbook this morning and something happened while in there. I have gone through options/advanced and my gridlines are colored to black. Then I have also highlighted the entire sheet and selected no fill from the Home Tab. Nothing is working.

View 9 Replies View Related

Deleted Data Stays In Pivot Table..

Apr 22, 2007

I have been setting up some pivot tables. Down the left side of the pivot table is the values from a column labelled TRACKS. There are about eight different TRACKS. Say Track1, Track2, Track3, etc. I have set the data range up as a dynamic named range so that if more rows or columns are added to the data then the pivot table will automatically incorporate these.

Today I wanted to test that the pivot was working as expected. One of the things I did was stick a few extra values into the TRACKS column on the data source. These were TEST1 and TEST2. These appeared in the TRACKS down the left side of the pivot table as expected.

When I deleted these two test values, however, they would not disappear from the pivot table. There seemed to be nothing I could do to make them go away, except to rebuild the pivot table from scratch.

View 3 Replies View Related

Excel 2010 :: Remove Gridlines And Customize Background Colour Other Than Table

Mar 8, 2014

Is it possible to remove all gridlines (except in a table) and change the background colour to plain colour (except the table) in Excel 2010?

View 2 Replies View Related

Interpolating Data In Excel Table With Missing Data

Jun 1, 2014

I have a table with 21 numbers two rows with x and y e.g.


so on ........

Now I wish to make a table with 100 numbers in wish y is interpolated based on y in the given table. Using many IF statements (in Dutch "ALS") isn't useful since the number of statements is becoming too large and besides is a number is missing e.g. nr7 x=0,7 the y is 0.

This is not working properly:

[Code] .....

View 11 Replies View Related

Take Missing Data From First Sheet?

May 15, 2014

I need to take in third sheet all data which are missing in second sheet from first sheet, for example:

IDName LastName
1John Doe
2Jany Tine
3Paull Marphy
4East Doe
5West Marlyn
9Doe Tryer
Sheet 1

IDName LastName
1John Doe
4East Doe
9Doe Tryer
Sheet 2

And in Sheet 3 to get result

2Jany Tine
3Paull Marphy
5West Marlyn
Sheet 3

View 14 Replies View Related

Pull Data From Sheet Based On Criteria - Populate UserForm And Ask For Missing Data

Feb 8, 2014

I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...

First I was using a msgbox function to get the info:

HTML Code: 

For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b

But it can be up to 30 different new employees... and that is time consuming.

I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....

HTML Code: 

Private Sub CloseButton_Click()
Unload UserForm1
End Sub

Private Sub ComboBox1_Change()

[Code] ......

View 2 Replies View Related

Pivot Table Group :: Fill Missing Cells With A Data On Workbook Open

Sep 18, 2008

new data goes into the report, the pivot table looks at a dynamic range and confirmed that the range doesn't select any empty cells.

Just wrote a macro to fill missing cells with a data on workbook open.

all the data is date form, yet still when i refresh the pivot table i look my montly grouping, when i try to group again it says "unable to group"

View 9 Replies View Related

Expand Table To Include Missing Times From Data And Sort Based On Multiple Criteria

May 6, 2014

I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)

How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.

View 1 Replies View Related

Compare Two Columns, If Missing Insert Missing Data

Jul 8, 2008

I have two columns which i want to compare, they contain text data such as A123.

what I'd like is if its in column A and not in Column B then add to bottom of column A.

Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.

View 9 Replies View Related

Excel 2010 :: Shading Of Table Automatically When Lines Inserted / Deleted?

Nov 16, 2012

i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?

View 6 Replies View Related

Formulas To Not Change If Cells Where Deleted From Sheet

Sep 18, 2007

I have a sheet (sheet2) that has cells in column L, M and N referencing cells in another sheet (sheet1)(same workbook).

If I delete a row out of Sheet1 it causes the formula in Sheet2 to go to #REF.

Normally the formula would be something like
How can I get these formulas to not change just because cells where deleted from Sheet1?

View 9 Replies View Related

VBA Code For Listing The Sheet Names Of Deleted Sheets

Oct 20, 2008

I have a workbook with 20+ sheets in it, I add sheets and delete sheets on a daily basis, except for one sheet that is like my summary sheet.

Is there a code, formula, or magic spell that will list the names of the sheets that I have deleted? For instance, if my workbook has 50 sheets and I delete 49 of them, I want to see cells A1 thru A49 (or where ever I wish to place them) filled with the names of the sheets I just deleted.

View 9 Replies View Related

Sheet Event Code Errors If Target Deleted

Mar 25, 2008

I have added this bit of code to change the apperance of entered time from 0835 to 08:35

UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If

And it works like a charm. Except that if the content in one of the cells later is deleted a "Run time error 13" is the result. Debug leeds to the line "If Userinput >1 Then"

Can this error be avoided..?

View 3 Replies View Related

Workbook Code Errors When Orginal Sheet Hidden Or Deleted

Oct 14, 2007

I copied the sheet and redid the format. I renamed the original dashboard sheet from count to "keep" and named the copy after the original "count" Everything still works great - until i either hide or delete the original count which is now named "keep". I get an error at the red colored line below ".publish false".

I have attached an image of the error....

View 7 Replies View Related

Deleting Rows From Sheet 1 When Table On Sheet 2 Does Not Have Data Anymore

Jul 3, 2014

Currently, Im running a button macro. When this button is clicked, the table from "315 Employee Data" will copy the names from column C to "315" sheet B12 onwards.

Now, what i want is when I update the employee data on "315 employee data" sheet, I want "315" sheet to automatically match the names from "315 employee data", delete and add names when I add or remove employees from the column the next time i click on that button again.

After the above is achieved, when i add new employees,run the macro and it displays the updated list of names, I want "315" sheet column A to do automatic numbering.

I have attached the file for your reference.samplesample.xlsm

View 2 Replies View Related

Summary Sheet Calculate Results Change If Rows Added/deleted In Detail Spreadsheet

Feb 27, 2009

[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.

I'm using dynamic name ranges, as follows, for the detail:

WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)

Other detail data is defined as these examples show:

GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)

All detail data begins in Row 6 in the detail spreadsheet.

In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.

Here are two examples of the calcs I'm using in the summary:


Can someone tell me what is going on? What I'm doing wrong?

View 8 Replies View Related

Missing Category Field In VBA Pivot Table

Jan 11, 2013

I have a macro to create a report and it selects to place in the pivot table. The problem is that sometimes in my basic data for creating the pivot table, not all the same fields are there. Here is what it looks like for this section now.

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Category Id")
.PivotItems("ARMS/AMMO/EXPLOSIVES").Visible = False
.PivotItems("COMPUTER").Visible = False
.PivotItems("COUNTERFEIT GOODS").Visible = False
.PivotItems("DRUGS").Visible = False
.PivotItems("GENERAL MDS/OTHER").Visible = False
.PivotItems("PROHIBITED ITEMS").Visible = False
End With

In the instance for today, the field "Drugs" is not in my main data. So I get an error box and it stops because the category is not there. How can I get this to continue if one of the fields is not found?

View 1 Replies View Related

Conditional Format Table Row If Entries Missing

Feb 26, 2008

Please find the attached sheet.

I just want to know whether is it possible to condional format multiple cells based on multiple cell values.

Eg: In the attached sheet,when a driver is standby and the vehicle no column should be empty,if accidently a data entry is done in vehicle no column it should highlight the whole area (ie,from A3 to E3).

View 8 Replies View Related

Deleted Data Leaves A Trace

Dec 8, 2009

I have a worksheet where I've deleted data in cells and rows. I am left with alternating rows of data and blank rows however a macro that I've been running to delete blank rows does not work on some of the rows.

To delete the data I used the Find and Replace feature and it seems that afterward the cells where this data was is now somehow marked as not completely empty. It appears empty but if I use the Control-Right Arrow combination from Column A, it skips over some cells but stops on a cell that used to contain data. If I press the Delete key it clears the cell completely and if I do that on all the cells the Ctrl-Right Arrow stops on until the end of the row, the macro works on that row...but not the next row of seemingly empty cells.

It's not spaces and there's no apostrophes and the cells are all formatted as General (Number Style) and Normal (Styles). I've even copied the format of a row of all deleted cells but that still does not completely clean out the "empty" cells. I've also run the Inspect Document command to remove what may be there but same results.

Is there something I can do to locate and remove this mysterious data?

View 7 Replies View Related

Keep Cell Adding After Data Is Deleted

Jun 5, 2007

I've a worksheet with 36 columns to keep track of a football confidence pool,I've a CF in which a W is entered in every other column from B1:AG1 and it highlites the whole column yellow,in column A is all players names,from column B2:AG2 I enter the 32 NFL teams,in row B3:AG3 I enter points from 1-16,in column AI it adds all numbers in yellow per row which gives me a weekly score,in column AJ I've the players name and there year to date points and finally in column AL it ranks the players by highest point total.

I will use this on a weekly bases for 16 weeks,what I need is for column AJ to keep adding the players points once I delete there respective numbers from rows B3:AG3 and add new ones for the following week.

Formula in cell AI3 =sumif($B$1:$AG$1,"W",$B3:AG3) copied down
Formula in cell AJ3 =A3 & " " & AI3 copied down
Formula in cell AL3 =rank(AI3,$AI$3:$AI$8,0) copied down
I also have the following macro an my sheet which allows me to use the formula in column AI3.
Function ConditionalColor(rg As Range, FormatType As String) As Long
'Returns the color index (either font or interior) of the first cell in range rg. If no _
conditional format conditions apply, Then returns the regular color of the cell. _
FormatType Is either "Font" Or "Interior"
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long

View 11 Replies View Related

Merged Cells With Data In Are Being Deleted

Jul 23, 2012

I have a spread sheet with merged cells in that contain data, when i ask excel to seek blank cells and delete them moving the others up it also deletes the merged cells.

View 1 Replies View Related

How To Calculate Average Without Counting Missing Values In Pivot Table

Jan 23, 2014

I have a problem when Im trying to calculate averages in my pivot table. I dont know how to handle my missing values. If I leave them blank I cant get the average at all because the values end up under the dates instead of in a column next to the dates where I want them to be. If I write 0 it uses it as a value, eg 2+2+0 and i get 1,33 instead of 2. Also the zero could be a value for some of the parameters, so it's no good.

I am new to pivot tables.

View 5 Replies View Related

Formula In Lower Table To Fill In The Missing County Cells

Sep 1, 2009

I am looking for a formula that I can use in the lower table to fill in the missing County cells, based on the values in the Town/Zip columns on the top table (I apoligize for the crudeness):


This would consist of hundreds of Zips and Towns and this is just an abbreviated mock up.

View 9 Replies View Related

Missing From One Or Another Sheet

Apr 16, 2007

I have 2 spreadsheets. One has one type of info and another has another type of info for the same items.
For example:

1st spreadsheet has: server name, serial number, model.
2nd spreadsheet has: amount of memory for each server, number of CPUs, etc.

Note that some servers are missing from one or another sheet. So all info needs to be combined.

What is the easiest way to combine all info in a single spreadsheet?

View 9 Replies View Related

Number Formatting: The First Three Digits Will Be Separated And Then Subsequently 2 Digits

Oct 31, 2008

i need to format my numbers in the following format


the first three digits will be separated and then subsequently 2 digits

View 2 Replies View Related

Excel 2013 :: Missing Items From Pivot Table Filter List?

Apr 1, 2014

Using Excel 2013,

I clicked on a field in my RowLabels

I then clicked on the Filter Arrow for the RowField

The SelectedField prompted with the correct field of 4 possible fields

However the item I am looking for is not in the list but I can plainly see it on the screen.

View 3 Replies View Related

Protecting One Cell Value From Being Deleted / Changed (Data Validation)

Dec 5, 2012

I have a cell in my spreadsheet (J3) that I need to never be changed or else other macros in my workbook will not work properly. I decided to try and use data validation since it is easy to change the rule every month and I can stay away from messing with passwords.

My cell value is a date, so I set data validation to have that cell equal only that specific date. If a user tries to overwrite the cell value, the error messages is prompted correctly. The one BIG problem is that I can just press the delete key and delete the contents of the cell. Is there any way of preventing this from happening without having to lock the cell and password protect the sheet?

View 3 Replies View Related

Pivot Tables Do Not Refresh Contents When Data Is Deleted An

Apr 11, 2003

When we create a database in an Excel Spreadsheet and then some pivot tables, we use to copy the spreadsheet with another name, in order to clean the data and update it with another date (for instance, one excel spreadsheet for one BU or country, another one for other bU or country), to avoid creating the pivot tables again and again.

However, when we clean up all the data and enter the new one, on the Pivot Tables fields, you still see the "old data that was deleted"... Do you know if there is a way to delete that data without recretaing the pivot table?

View 9 Replies View Related

Dynamic Named Ranges Where Data Is Manually Added And Deleted

Apr 22, 2013

A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:

Calculate the length of the data set:

=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))

(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.

Example named range for the data in column A:

=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)

I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.

The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.

View 7 Replies View Related

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