Change Number In Repeating Cells That Also Contain Letter Such As D1 / D2 / D3
Jun 15, 2014
i need a formula for a sign in sheet for the academy i work at. we have night and day classes of the same sessions, in the planner they are listed as d1, d2 d3 and n1 n2 n3 and so on for each.
so basically im trying to create a formula to quickly ender the d1, d2, d3 ect into the cells on eachpage for the students to sign in for each day it works out to be about 190 calls and 9 diferent classes to you can see why id like to speed this up. problem is i need the number to change not the D and excel want to use the cell allocation.
as you can see from the document i have not attatched, i have created a formula to add in the dates automatically, i would like to do the same with the session numbers or the D numbers, this is one of the shorter courses and has about 190 days
I have a form that users will fill in, and I need to print a set number of labels (all identical) based on the info that is entered.
I have the form set up with data validation and indirect references(Lists on a separate sheet)
Example...
User enters in: Customer name Product name Lot # PO# Date made Number of items
From there I have a separate set of cells that reference the above info formated to how I want the label to appear when printed.
Now, I need to repeat that label the set number of times (# entered in as "number if items") in a label sort of format (not sure if a mail merge will work for this??)
Then, print off and stick identical labels onto each part that is manufactured per PO/Date etc.
I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones Column B : payroll number ie 123456 Column C : shift times ie 1245-2124 Column D : job title ie floor Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
I need code to change a letter such as A into a column number i.e 1. Everything i can find is to do with changing column numbers into letters, surely you can do it the other weay round aswell? So when the macro is given the letter AA it returns the column number as 27 etc, but i need it as a defined variable such as "i = column number" so i can use this information further on in my code.
I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).
I have completed the first section of my spreadsheet.
Section 1 has a number of mandatory fields. Once these are complete and the user changes a combo box then Section 2 opens
I now want to repeat the same again for section 2. Making fields mandatory before a user can change a combo box to yes, which then opens Section 3
From looking around the forum / google I think I can only have one worksheet_change per sheet. If this is the case, how do I now start my next section (to basically repeat Section1 again)
The code I have is:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("E20"), Target) Is Nothing Then 'Section 1 Range("A22:A130").EntireRow.Hidden = True
sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
I am trying to create a formula that would provide a result a specific number of times based on a table in a seperate worksheet. Here is an example:
Person: # of times John 2 Joe 2 Frank 2 Jack 1
Based on an IF statement, the cell should bring back John, the next cell down should bring back John, the third cell should bring back Joe as John has reached his limit of 2, and have this repeat until all criteria that meet the IF statement have been met.
Been trying to think of a way to get random numbers 1 to 36 generated without repeating in the same column, however also getting it to perform the same opertaion in 9 more columns (B:J) without the same number appearing in the same row.
example
1 2 5 4 2 5 3 1 3 4 1 5 4 1 2 3 5 3 4 2
Unsure if this is possible, have created a basic script for random number generation but have no clue how to expand across columns.
I have an excel sheet that we need to type in an account number it always starts with 8774100 then has 9 more numbers. If I leave it on number formating it always changes the last number to 0 so I format the cell as TEXT. Anyway is there a way to get the 8774100 to automatically go in and only have to type the last 9 numbers? I know I can copy paste but then I have to double click or make sure I enter the last 9 numbers at the end or use the top entry. The data entry people just want to hit enter and go from line to line fast. In the past I just divided the cell into 2 cells and made the 8774100 in column A and columb B was the ending 9 numbers. But they need to be in the same cell.
I have letter and number combination code in two collumns and they differ for 10.000 numbers:
BAM98314 BAM88314 BAM90000 BAM80000
As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.
How to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
I cannot change the number format in a single sheet of my spreadsheet. I can go to format cells and select colour etc but it will not change the number format.
Another issue on the same sheet is that linked cells i.e cell b3 formula "=A3" displays "=A3" not the actual value in A3. Again, i cannot change the number format of this cell and yes, auto calc is on.
Whilst not wanting to offer a partial explaination (per the rules) just before this happened i was pressing a combitation of Shift+Control+` - not sure if that is relevant.
I want to get the sheet back to its 'normal' working order.
Some of the cells contain a variety of dates (formatted as d-mmm) Other cells contain either "i", "n" or are blank - or contain a text string, eg "his name"
I'm looking macro code that will: Look at the range and only for any cell containing a date, replace it with "y".
I just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
I have a user whose links in his spreadsheets are in UNC format. He changes them to drive letter mappings, but when he opens the workbook again, the UNC format returns. how to change the links so that they remain as F: etc.. rather than \servernamesharename The user has MS Excel 2000 and Windows Xp Professional
Sub uncheck_all() Dim sh As Shape Application.ScreenUpdating = False For Each sh In ActiveSheet.Shapes
[Code]....
Currently, the button just unchecks a bunch of checkmarks. I want to modify the code so it also makes the value in cell C13 change to -9999, C14 to 9999, C16 to -9999 and C17 to 9999.
Is there a way to get Excel to automatically change the first letter of single word entry to lowercase in each cell of the first row of my worksheet leaving the case the same for all other letters?
