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 RepliesI'm working on a criteria matrix in Excel 2010 that automatically plots a single member in a Scatter Chart based on the two values. There are 4 suppliers listed in Column D starting in cell D4 thru D7. The "x" value is listed in Column E starting in cell E4 thru E7. The "y" value is listed in Column F starting in cell F4 thru F7.
The scatter chart will plot the points correctly, however, there are two issues: 1) If I try to insert a data label using the "Series Name," or in this case, the supplier's name, it will lists ALL of the suppliers Column D. It will not list the single supplier listed in cell D4. 2) The scatter chart appears with gridlines as a 4x4 matrix with a total of 16 cells. The "x" and "y" axis both start at 0 and go to 4. I can shade the entire chart one color. However, I want to shade some of the cells with darker and lighter shades.
I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:
Current Conditional Formatting Formula: =MOD(SUM(1/COUNTIF($a$2:$a2,$a$2:$a2)),2)=1
STATES (unfiltered)
AL
AL
AK
AK
AK
AR
AR
AZ
AZ
AZ
When I apply a filter, the rows remain shaded as they were originally:
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I want the conditional format to change when I have filtered out items (DESIRED RESULT):
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.
I need to detect when some Chart is being deleted. This is part of an XL2010 VSTO addin I an developping. I could not find any event matching my need.
Did I miss some event somewhere? Would you know a (simple) method to detect when some Chart is being deleted by the user?
build a spreadsheet that reads information off of a Master sheet onto 4 other sheets. The hope is that by making changes ONLY to the Master sheet that the other 4 will update automatically.
And then we ran into the trouble of not being able to insert new lines onto the Master sheet without throwing everything off on the other sheets.
(I've attached an example.)
For instance, if I go to the Master sheet in the Test.xlsx attachment, I've left out Lima from the alphabet. So, I insert a new row onto the Master, switch back to the Formula sheet where it should (theoretically) just update the cells to display the new data.
Not so. The Formula sheet just skips the new A13 and keeps on going.
the primary one seems to be using Offset. Well, the coworker will be adding and deleting many, many rows over the course of the year, as it is a product log and we change our products often.
I am using Excel 2010 and have the problem as shown in the attached file.
Input Sheet shows the Data I have at present
Output Sheet is the desired result.
I need a macro which should create an "Output" sheet by doing the following on the Input Sheet
1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.
Please note that the number of Data Rows will vary for each Region.
I have shown two Regions for explanation purpose only. There will be several Regions in reality.
The result is shown on the Output sheet
Merge Problem - Forum.xlsx
I am using excel 2010, windows 7. When i make a new worksheet or update it randomly blanks out lines that already have information in them. It blanks out the row number also.
View 1 Replies View RelatedI have been trying to chart temperature differences over 10 day's time for six cities using a line graph. For some reason, the lines are all clustered at the bottom of my graph, with values of 0. My spreadsheet is accurate, without any blank cells. I'm using Excel 2010.
View 1 Replies View Relatedi have done in Word VBA successfully (basically, change the Style for a certain number of lines):
VB:
Dim myRange As Range
Set myRange = ActiveDocument.Range(ActiveDocument.Range.Start, ActiveDocument.Range.Start)
[Code].....
I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.
If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
View 3 Replies View RelatedI have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
I want the 2 graphs in "Graph" worksheet to change automatically when a row is inserted in "Data" worksheet . Every time i have to change the graph manually to contain the latest 20 days value. I want some offset or something which can be put in the range provided below to do the work.
Chart Data Range =Data!$A$1:$A$22,Data!$F$1:$F$2,Data!$L$1:$L$20
Legend entries (Series) - Series Name =Data!$F$1 ( this will remain constant everyday as this is a header field)
Series Values =Data!$F$2:$F$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$F$3:$F$23)
Legend entries (Series) - Series Name =Data!$L$1 ( this will remain constant everyday as this is a header field)
Series Values =Data!$L$2:$L$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$L$3:$L$23)
Horizontal (Category) axis series -Axis Label Range =Data!$A$2:$A$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$A$3:$A$23)
Sheet attached : Devicess.xlsx
I have a formula in column G of a worksheet, however when a new row is inserted within the formula range, the formula does not continue in the new row.
Is it possible to make excel continue the formula without having to drag the formula down again?
Need automate a process that I would like to implement into my spreadsheet,
It involves inserting comments,
I want to be able to select a particular cell anywhere in my spreadsheet, and then click on a command button and have a comment automatically inserted into the active cell, I tried to record the macro by hand but I do not know how to state that it is the active cell the comment should be added to
What I would like to do is on a sheet when I insert a new row that it will "FILL" the formulas that are the row above it. For example I have cells A1-F1. On cell A1 there is 1, B1 there is 2...etc. When I then insert a new row I would like the row below A1-F1 to read. A2 = 2, B2=3 so it had a linear growth. I want to do this with my formulas so whenever someone adds a new line it knows to copy the formula as well but only in certain cells if possible.
View 9 Replies View RelatedCan periodical fixed deductions/payments be inserted automatically to the individual MONTH sheets of the workbook I uploaded ? To reduce the size of the file, I have left only ONE of the twelve month-sheets - the one for January.
Rephrased :
How to automate the entry of fixed amounts at regular intervals?
My guess is that from time to time, a macro would have to be run
- one of the macro's inputs would be the computer date and the other two inputs would be the value of the entry to be made and the value of the intervening period.
In practice there would be more than one regular entry and the values would be both positive ones and negative ones.
I guess that means one macro for each financial pattern.
Feasible or feesible (a multi-million-dollar-fee task)?
If I have a worksheet and it has 100 rows, I would like for column D to auto-populate with a predetermined formula all the way down to row 100, not row 101.
View 3 Replies View RelatedI'm using Excel 2010 and have run into the following problem. I have data in a text editor that I need to separate. I copy and paste this data into Cell A1, then do a "Text to Col" function that separates the data correctly. Now I have other data to paste into other cells on the same sheet. Here's the problem....The "Text to Col" function runs automatically when I paste other data into any other cell on the WS. How do I turn this off without closing the WS?
View 4 Replies View RelatedI have 5 worksheets that I currently have to add information to and cut/paste information from one to another. The initial information stays the same but I have to cut and paste it into one of the other worksheets based on wether or not we; need to decide on a job, are working on the job, lost the job, won the job or the job is complete.
I would like to create one master worksheet where the information can be entered with a drop down cell stating the status of the job (listed above). I created the master tab and linked the 5 subordinate tabs using an IF formula, but how can the subordinate tabs filter or sort the references and organize them on the top of the page rather than leaving a bunch of blank rows (because the info in these blank rows went to a different subordinate tab)? I want to enter the info in the master tab and simply change the pull down cell to change which subordinate tab the information shows up on. This should allow me to print the subordinate tabs as reports without having to manually cut and paste the info or filter it, correct?
On my work computer (Excel 2010, Win 7) when I am stepping through my VBA code using F8 and come to a Workbooks.Open line, the macro automatically kicks in and runs through to the end instead of just stepping to the next line of code. This happens nearly every time but occasionally it works as it should. To prevent this, I have to put a breakpoint on the next line following Workbooks.Open and after that I can step (F8) through the rest of the code. I have never had this problem before and it doesn't happen on my home computer.
View 4 Replies View RelatedI am attempting my first working macro. I am currently using Excel 2010 on Windows XP. Here is what I am attempting: I need a very user friendly macro that will allow my staff to click on a button to run the macro. Each month they receive multiple workbooks from one of our clients. The workbooks are always stored in the same location. I need the macro to go in and pick up the first worksheet of each workbook and copy it into a new workbook. Each original sheet1 of each workbook should have its own sheet in the new workbook, i.e. if there are 5 original workbooks then the macro should create one workbook with 5 sheets. Here is the programming I have been using:
Sub GetSheets()
Path = "C:Documents and Settingse462863My Documents
utterblotter"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
This works, but I am running into a few problems. I need to create a "run" button but I'm unsure how to build it. Also, when I run this macro there are tons of extra sheets and the worksheets tend to duplicate. I am wondering if the "loop" is picking up more than I need.
I'm relatively new to Excel and I'm currently making a basic spreadsheet for my personal income/spending.
How would I make the "Earned this Month" and "Spent this Month" tabs in the top update on a month to month basis automatically?
For example, in the month of may, it would display may's values, june would be june's etc. etc.
(Excel 2010 w/ Windows 7)
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I work for an insulation company and we have all of our jobs, completed and in progress, on a master worksheet.
Currently there are 437 rows of data (but will increase), and columns A to N with various bits of data.
Row A is a location field - there are 5 locations currently.
I would like to be able to add a new line at the bottom of the master sheet, and then this automatically identifies the location from column A and which worksheet is it to be copied to and then copies the data from that new row to the bottom of the relevant location sheet.
I would also like to be able to update the data in the existing entries (e.g. when a job has been assessed initially, and then completed, I need to put the dates in) and for this to update on the relevant worksheet.
Each worksheet has the same format (columns A to N have the same headings in row 1, then data to begin in row 2).
I wouldn't say I'm an advanced Excel user (otherwise I wouldn't be asking this question), but I do have a fairly good working knowledge of it. Currently using Excel 2010. Would ideally like to be able to do it without VBA as it needs to be uploaded to Google Docs and for others in the company to access online.
what is the proper terminology for the Outlook "Thing" that opens up in excel (shown below)? Is it simply a toolbar? Add-in?
Second question, I have a excel file that automatically loads the outlook "Toolbar" each time it is opened.
The file was made in 2010 and password protected and locked, sent to a worker who edited the appropriate fields and saved, and then they sent the file back to me (2003 Format). Now this file automatically loads the outlook toolbar. I am trying to trouble shoot so what would cause this behavior?
1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
I use 2010 and I will try and I need to complete a relatively simple project that I am working on.
I have a range of cells, "D7:Q30". This range has numerical values that are updated automatically (every cell within the range has a numerical value) from an "old value" to a "new value". The refresh rate is not constant, that is the values may change at any time depending on when a value changes I think it is called "pushed" data. Once a value is updated ("new value"), it stays at that value until one of the updates changes its value (and then it becomes the "old value").
I would like a message box to display the following EACH TIME A CELL CHANGES ITS VALUE when the NEW updated value is LESS THAN the OLD value:
"B51, B52, **5, has a changed value from "old value" to "new value".
The ** above in the message box represents the following: If for example cell F23 changes, ** in the message box should be the value that is in cell F5. If Q10 changes, the ** value in the message box should be Q5. It will always be the value in Row5 but have a different column depending on where the change is detected.
B51 is the text value in B51..same for B52 (text) etc...these values change depending on what spreadsheet I have open, but will always be in those cell positions. But the values within the range D7:Q30 are always numeric.
Basically I first want to be able define around 100 variables such as:
Dog = 1
Cat = 2
Mouse = 3
And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.
So typing in
Dog
Mouse
Mouse
Cat
Dog
would automatically result in
1
3
3
2
1
In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?