Excel 2007 :: Insert Random Number Preceded By X If Adjacent Cell Has Data
Nov 24, 2011
i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager Rule
Stop if true
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
Conditional formatting rules manager Rule
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
I am trying to write a Macro that will insert a Text Box that auto-fits the shape of a cell to hide its content. Once finished, the Macro will need to lock the cell and the text box so the contents of the cell are hidden. The idea is that I want to share a spreadsheet with someone but want to hide individual cells for various reason.
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
I have an excel file of 154 pages. I want to print only pages 1,151 and 154. what i am doing is print pages one by one.first give the command to page i then 151 then 154. how can i give the command in one go so that it is printed the required pages only.
I have a data set that tracks ingredients in chemical products. For example sake the chemical product is a mixed color, and the calculated ingredient is a primary color.
I need to calculate the quantity of two primary colors based on their percentage of the mixture.
Column A contains the mixed color names. Columns B and C contain the percentage of the primary in each mix.
I began tracking this data in January 2009. Each month I add 3 columns to the worksheet. The 1st column contains the monthly mixed color quantity used. The 2nd and 3rd columns contain the calculation of the two primary colors.
I need the formula to calculate the 12 month rolling total for each primary color. (For July 2014 I need to calculate July 2013 through June 2014)
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
I have six complex functions, which I would like to place in [A2] depending on what is placed in [A1], The functions themselves are quite extensive and I don't want to use any active functions (like INDIRECT) as this will cause slow calculations. [A1] will just be a given name to each of these functions.
Is there a really easy code which will insert the current users ID into a cell adacent to the one previously completed automatically? For example if a date was entered in A2, then B2 would automatically be completed with the user identity?
There's got to be a way to do this (mail merge style perhaps...) I would like to insert the entry/ies under a heading in a table in one worksheet whenever that heading matches the data entered in a separate worksheet.
I want to use VBA to insert an Organisation Chart in Excel 2007 (ie if I did this manually it would be Insert, SmartArt, Hierarchy), but I cannot find anything on line that shows how to do this in Excel 2007. I know the code for Excel 2003, but that does not work in 2007.
I need to insert a drop down box into my spreadsheet, and depending on the option selected, the formula used to calculate the value into another cell changes. How do I implement this in Excel 2007?
To put this in context, I need a list of acids in cell C3. Depending on the acid selected, the formula used to calculate acid concentration in another cell (D3) changes. I need a way to implement this.
I am using Excel 2007. I would like to be able to type "dlta" followed by a space and have autocorrect change it to the symbol for Delta (a triangle). I got it to work in Word because I could right click and paste the symbol in the autocorrect dialogue box. Excel won't let me do that. I also tried typing Alt+68 because I believe that is the code for the symbol I want but all I get is a "D".
with the following: The VBA macro I am using, is derived from the excellent "Table of Contents" macro written by Erik Van Geit, a few years ago.
I have a workbook (in Excel 2007, Windows XP) with an Index sheet and 200 other sheets (called ScreenShot1 to ScreenShot200). Each of these 200 sheets has had an image pasted in the top left corner.
At present, using the macro below, a hyperlink is placed in cell A1 on each of these 200 sheets (it's behind the image) that links to cell M1 on the Index sheet. What I would like this macro to do is make each image the hyperlink back to cell M1 on the Index sheet. Please note that the images are not named.
VBA code below,
Sub create_TOC() Dim i As Integer Dim msg As String Dim fc_order As Range Dim sht As Object '**** EDIT the following lines **** Const TOC = "Table of Contents" Const Index = "Index" Const CellLink = "A1" '**** END EDIT ****
I have a form that collects four pieces of information from the user and places each into columns A, B, C, D on a sheet. I would like to automatically add an option button to column E so that a user could select it and then click a control button to delete the corresponding row.
Is this possible or is there maybe a better way to do this in Excel 2007.
I am currently using MS Excel 2007. Is there a way in VBA to automatically insert missing columns based on a defined series or range of values?
For example, we have a report that displays columns of: Year 2004 to Year 2012 per column (9 columns in total). Sometimes, it displays missing a year or years (thus; our report has less columns). For example, it only shows 2008 to 2012 (missing 2004 to 2007). At times, it misses some years in between. What we do is manually add those missing years by manually inserting columns then inputting the missing year as their column headers.
Is it posisble for VBA to check if the columns contain all of the defined Year range (for example, you've initially defined: Year 2004 to Year 2012)? Afterwards, all missing columns are automoatically inserted (in chronological order or based on your defined values or range).
Using excel 2007 I've created a new tab and wanted to place the 2003 file menu on it. I don't know how to include it automatically in the tab using a combination of XML and VBA. So I created a button with the callback to run some code. Unfortunately, it still places the item in the Add-in tab. How can I get the undernoted code to operate within my new tab with either XLM or VBA?
Code: Sub InsertFileMenu2003() Dim cb As CommandBar Set cb = Application.CommandBars.Add("xl2003 Menu", , True) CommandBars("Built-in Menus").Controls("&File").Copy cb Application.CommandBars("xl2003 Menu").Visible = True End Sub
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!
I have a table in Excel 2007, one set of data in a column contains cells with data such as = AA701 DIS34 5283000, Z00IS0750 Now these AA codes DIS codes 5283 codes and Z00 codes can all be different!! and they are all within the same cell.
What I need to be able to do is find out which AA code which DIS code which 5283 code and which Z00 code is reported within each cell on each row?
So could I split them out into different cells via VBA? or can I do some kind of lookup that picks up a AA code (for example) ?
The constraints I have is this is downloading from a web query, I do not have the ability to alter the way the original data is coming across, I can only alter it once I have received it - refresh and then alter it again.