I have been trying to break my new chart that combines stacked bars on the primary axis with their labels on a transparent line graph tied to the secondary axis. The secondary axis allows me to show the labels I want as categories and position them relative to the visible part of the stacked bar (waterfall style) proportionately outside of the bar. The trouble comes when my label positioning is less than or more than the primary boundaries. I can solve this manually by changing the max or min value of the secondary axis, but what I really want is for the secondary axis to be locked to the primary axis' max and min automatically.
I have a chart with sales data on one axis and inventory on a secondary axis. I have the average of each columns of data and want to add a reference line to each axis with it's average. I have added the averages to my data set, but it only adds the reference line to my primary axis. I actually began by manually inserting two lines to the chart (which seems asinine). They actually didn't print because they weren't actually on the chart, but rather just on the spreadsheet behind.
Is it possible to use a formula to replicate primary and secondary lookup in SQL and is it possible to retrieve data from Excel spreadsheets using SQL.
It all works fine but I've noticed an odd quirk which I can't explain nor can I seem to fix. Here's the (reduced) code :
Code: Dim appExcel As Object ' Excel Application Dim chtPareto As Object ' Chart Set appExcel = CreateObject("Excel.Application") Set chtPareto = appExcel.Charts.Add With chtPareto ' Primary category axis
[code]....
The category (x) axis title should be horizontal, whereas the value (y) axis title should be rotated. Stepping through the code, when I .SetElement for the category axis title, it appears horizontal as planned. However, as soon as I .SetElement for the rotated value axis title, the category axis title also rotates.
I am trying to add a secondary axis for a graph but it is not displaying as it normally does, instead of the columns remaining side by side they overlap. I don't normally have any issue with this so I'm not sure what I'm doing wrong. I've attached images to show what i mean.
How do I select all of the series on my chart and flip them over to the secondary axis? I can't seem to find an answer via google... Either VBA or straight Excel would be fine.
For the record, this is part of a larger question. I've been creating a chart using a couple for loops to work through a 2D array (VBA, clearly). At first I would just set each series to the second axis as it was created, but I've found that if I use collapseable groups to turn off different values...well, if I collapse the first series it all dissapears (still shows up in the legend, but the lines are gone.)
If I leave them on the primary axis I have no prolblem. So now I'm working on the theory that it's the way they are being added. So I'm looking for another way to put them on the other axis.
how do you put all series in a graph on the secondary axis all at once. Either an Excel based or VBA answer is fine (I can turn the Excel into a Macro if push comes to shove.)
When ever I add a secondary axis to my chart my bar chart sub type changes to a stacked bar chart. How do I keep the sub type bar chart? I have attached an example. I have tried playing with the gap and width of the bars but to no avail.
All I want to do is color the numbers on both the primary and secondary axis. Primary i want the shade of blue below. Secondary I want the shade of green below. Only the primary axis part of the code does its job. The secondary axis remains black. The code does complete without errors. Excel 2010.
sub color_axis() ActiveChart.Axes(xlValue).TickLabels.Font.Color = RGB(38, 40, 118) ActiveChart.Axes(xlSecondary).TickLabels.Font.Color = RGB(0, 153, 0) end sub
1) How do make values on x-axis as words? E.g. apples, oranges, etc.
2) Is it possible to have the numbers on x-axis in descending order? If I can figure out 1), I can do this as well, but it would be nice to know if it is possible to have the numbers on x-axis in descending order.
I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.
I have some numeric data that has to be put on a log chart. but there is no option for log charts. so i converted my data to log and then drew a chart but i am unable to edit the axis intervals and starting axis value. whenever i change the value it gets back to its default value,ie 1. can anyone tell me how to draw log chart or how to edit axis intervals and starting axis value?
I need the two x axis to be pressure and solubility and the y axis to be velocity, with the data being demonstrated by a single plot (I have only managed it with two plots which does not demonstrate solubility's relationship to pressure ).
For example at a pressure of 200 PSIA (lower x-axis) and solubility of 0.024 mol % (upper x-axis) velocity was found to be 1 m/s.
Here is something I have found while sorting data from a much larger spreadsheet but is quite disturbing that Excel will not sort very specific lines based upon a secondary value. From the data below try the following:
Select all the data and sort the rows by a primary column of E and a secondary of D both "Descending" and no header row of course.
In my Excel 2003 on two computers you hopefully will notice that row 1 and 2 refuse to sort based upon the secondary D column descending however rows 3,4 and 5,6 will sort correctly. Flip it back to "Ascending" for the secondary Sort of column D and it works correctly for 3,4 an 5,6.
Why does Excel refuse to sort rows 1 and 2 correctly based upon the secondary column D?
I am creating an Access database from excel. In the code I get the Database and Tables name from Excel spread sheet and assign them to variables.
In the first table, & tblNamePrime &, I create a Primary Key called, [PrimeRecId]. I create a second table, & tblName & and try to create a Foreign Key called, [RecId] and try to Reference it to the table, & tblNamePrime & [PrimeRecId]. I always receive the following error message:
Syntax Error in CONSTRAINT Clause -2147217900(80040e14)
If I remove the code in blue it creates the tables but then I need to go th access and link them by access
I have tried many different ways and have followed instructions from SQL, Access VBA and I am unable to Link the Primary Key to the Foreign Key.
I am new at trying to tie Excel to Access and SQL.
The code below is just 2 tables but when I am done there will be a total of 4 tables all linked back to the first table.
Private Sub RDCModel() Dim dbConnectStr As String Dim Catalog As Object Dim cnt As ADODB.Connection Dim dbPath As String Dim tblNamePrime As String Dim tblName As String
'Set database name here 'MsgBox "Current user is " & Application.UserName dbPath = ActiveSheet.Range("C1").Value 'Database Name.........
how to incorporate another category and value axis to my existing chart.
1. I need to add a rank axis derived from my data column (C6-C15). This column should rank from highest to lowest and create a tie if any number is repeated. I have arranged the rank column in cells B38:L38 as they would appear. I would like this to appear on the top or bottom of the bar graphs.
2. Second I need to add the frequency of hits which = 3 derived from cell J1 This should appear on the chart were it is currently titled. This should create another bar graph colored green and labeled 3.
3. Lastly the numbers axis can remain the same as it appears on the chart or if it would be better to align as shown in cells B43:L43.
I know it is possible in Access auto assign a unique ID (primary key?) to each record in a table. I am creating records in a table using Excel and Visual Basic to write to a ".mdb" file without using Access. Is there a way to auto assign a unique ID within the coding?
The reason I ask this is if I had two users writing to the ".mdb" file at the same time, I don't want them to end up with the same ID's for two differend records.
Sub UploadRecord() Dim DBName, DBLocation, FilePath As String Dim DBConnection As ADODB.Connection Dim DBRecordSet As ADODB.Recordset Dim XLRow As Long, XLColumn As Long Dim DataRange As Long Application.DisplayAlerts = False Application.ScreenUpdating = False
I would like to generate a primary key for each line of my spreadsheet. My spreadsheet flows like so: Each row is an individual record. I have a status column that represents the status of each record. Ultimately, thie represents different forms of open status & then of course a closed status. The only other status is blank. When I go from blank status to non-blank I would like to generate a unique # that I can use key that I can it use refer to this unique record.
Column C validates on a list called "Division" Column D is supposed to validate based on the selection in Column C, and give the list based on Column C.
I had it working awhile ago, then I went and changed the lists. Now, the lists are not validating appropriately (I'm not getting the expected drop down in D based on the selected in C) and I don't know why its not working anymore.
I have a VBA program in Excel that creates an Access Database. There are 4 tables and below are the SQL statements that create the Database. There are more fields associated with each table. When I go to Access the tables look great and I do not get any error messages.
[code] ...
When I send, “write” records from Excel to the tables the record's data is written correctly, the AcountInformation [RecId] updates with an auto number correctly the Primary Key auto number of each of the subsequent tables updates correctly.
The Foreign keys do not contain the record number from the, “refers back to AcountInformation [RecId] Primary Number”. Every thing works except the updating of the Foreign Key
I have tried everything even going into access and manually deleting a record from AcountInformation table to see if it deletes the appropriate records in the other tables and updating a record. It does not update and or delete the records from the other Tables.
The bottom line, Access does not pass a Primary key number to the Foreign Key.
My Excel gets stuck in a strange world sometimes where it seems like a pop-up blocker is turned on. By this I mean that i can't get question alerts to show in Excel. My only relief has been to reboot.
Here's what I mean - I generally see this two different times:
If I'm in a document that I've modified and click the "X" to exit the file - it never asks me to save, just closes without saving.
When I do a "find" on an item, I do get the first box where I can specify my search criteria. If there is a match, it will take me to the match if I do a find next. However, if I do a find all or there is not match, nothing else happens. I can see the cell addresses flickering in the name box.
I have a data set that is a spectrum of (y)intensity vs (x)energy I was wanting to add an additional X axis at the top of the graph showing the wavelength conversion for the energy value, is that possible in excel? and if so how?
I have a document to track funding and would like to be able to auto-populate individual task sheets within the workbook based on a task drop-down. So if I choose Safety from the "Task Area" drop down it would auto populate the full row from the Master Sheet to the Safety Sheet. I have uploaded my document so you can better understand.
