I have the word department followed by the department code in column A in an excel spreadsheet ie Department 50. Then say after 30 lines it may say Department 51 and so on. I want to copy down the cell containing the text "Department 50" in coulmn A until it reaches the word "Department",I then want it to copy down that cell ie "Department 51" until it reaches the word department again and so on. How I can do this?
View 4 Repliesif there is a formular that would say if a certain cell reaches a certain value it will add 1 to another cell for example
if cell B2 reaches 24 then 1 would be added to cell B3
I am wanting to put a "1" in ROW 2, starting with COLUMN B until the sum reaches the value in cell A3.
Example: I have 10 in A3, I would like the macro to place a "1" in B2-K2.
example.png
I am sure this is simple and easy to do, but i am having difficulties achieving this.
I want the cell to fill as it gets closer to 100%, I am sure its conditional formatting but I cant seem to get it to work.
When a task is completed I want the row wher it is in to be placed to another sheet(Archief). When in column F the value is changed to 'FIN' the row(A5 till K5 in the file I attached) has to be placed to the sheet 'Archief' below the last completed task.
View 3 Replies View RelatedI'm trying to come up with a way that will populate a table after each cell reaches a certain value.
My first column has rows with the total time required to perform a certain operation. The columns after that, represent operators that should be picking up a certain amount of tasks that do not exceed a set time.
Let's assume that the first row (total operation) has a total of 550 sec. The second column (1st operator) should assign a a value of no more than 200 sec. The third column (2nd operator) another 200 and the fourth column (3rd operator) another 150. So as to create a spill over effect.
The second row has a different operator with a different time. Let's say 300. These tasks should be picked up by a fourth operator with a value of 200 and then a fifth with 100. All in all I would like the table to populate as below.
xxxxxxxxxxxxxxxxxxxxxx operator1operator2operator3operator4operator5
operation 1550 xxxxxxxxxxxxx200 xxxxxx200 xxxxxx150
operation 2300 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx200 xxxxxxx100
ignore the "x". I used those to make sure that the rows aligned with the columns
the code below works fine except for the fact that it takes FOREVER to complete as it has to loop through almost 2000 rows... How to optimize this code so the macro stops as soon as it encounters an empty cell? There is currently only 50 entries in the database, but with time it will exceed 1000 entries and therefore i can't simply say Range("H2:H50").
Code:
If OptionButton1.Value = True Then
'Show all Car Cases
Sheets("User-defined Database").Activate
For Each cell In Range("H2:H2000")
[Code]....
Cell I5 has the sum of cells A5:H5. Every time the value of cell I5 reaches the next million, I'd like for the date that it reaches the next million to be displayed in J5.
View 9 Replies View RelatedI have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'.
Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.
formula which uses 4 cells
the 4 cells are
Cell B2 - this is a set figure which is the initial base figure - currently set to 43
Cell C2 - This subtracts Cell B2 figure (which is 43) from Cell E34 [=SUM(B2-E34)]
Cell E34 - Is a cumulative total of 4 cells [=SUM(E4,E12,E19,E26)] and displays the total.
Cell D2 is a set figure of 49
The formula I am looking for is once C2 reaches zero, I need C2 to remain displaying zero and the remaining figure to be deducted from D2 and displayed in that cell so C2 figure would descend from 43 until it reaches zero. but would then start decreasing D2 whilst C2 would remain static at zero and carry that the remaining figure to D2 which would be deducted from the starting total of 49 and display the figure.
I'm trying to copy data from one excel sheet to another excel sheet. However, the data to be copied is dependent on the 'client name'.
To explain this further, in the first list I have a detailed report on our clients and the services provided to every employee of that company/client.
However, the sheet two only needs the names of the employees that belong to a specific client.
This can be done manually by setting a fliter on the name of the client/company, but I need to be automated. To ensure only that specific company/client company's employee name is copied.
How would you write a custom function that you could plug in a cell that would do the following.
If the value in the cell is greater than 0, traverse up that column to clear the values until it reaches the cell in that column that has been colored orange.
This is probably far easier then I am making it but I need to take the information that is in a textbox created through the control toolbox and copy and paste that information into a cell on different tab in the workbook. I have tried the infamous google and haven't been able to find much. The excel books that I have don't really touch on the control toolbox functions.
View 2 Replies View RelatedHere's an interesting one:
A1 contains a text string which is both bold and unbold (
B1 is blank
I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.
Example:
A1
The sky is blue
Macro is run
B1
sky blue
I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.
Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)
Useful information are included !!!
I'll give an example of a record...
Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "
Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...
Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:
Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function
Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...
I have a list of text quotes in column A. I then have column B which will have a Y entered if the quote is used in a presentation. I currently have around 100 quotes.
On a separate sheet, I want to be able to effectively say in one cell - If column B has text in it, copy the quote from column A. But the difficult part is how do I make this happen so that I can have multiple quotes being pulled into one cell?
way to do this in plain excel (no macros, I have sufficient knowledge to do it in VBA but certain circumstances do not allow me to).
I want to insert a relative formula into a cell based on a condition.
For instance, I have a number N=10 (changeable).
And the following cells with corresponding formula:
A1=1
A2=A1+1
A3=A2+1
........
Is there a way to automatically insert that relative formula to the next cell until its value is larger than N?
I don't want to use IF and then copy to 65000 rows either, it will create blank cells that I don't want to be there (and it affects the printout as well as the scroll on the right).
The problem that I have is rolling over the sum total after the meter read reaches thousand it starts at 1 again, but I need the total KMs traveled for example in the car dash board the odometer shows 945 when I started my trip and end at 24, how would I set up a formula in excel to calculate the total KMs traveled
View 9 Replies View RelatedI have some information in a column where I have different types of references in each cell with some blank cells in between. I am trying to write a macro that will go down the column until it gets to a cell containing the reference "THIS PAYMENTS" and then copy that cell reference down until it reaches a cell containing "Y-T-D AMOUNTS".But I want the loop to carry on until it reaches another cell containing THIS PAYMENTS.I want this to repeat for about 3000 rows See here before and after result.
Before
After
PERIOD
PERIOD
[Code]....
I have a spreadsheet where a cell equals another cell. but i want to convert it to a value.
how can i do that for a range of rows. i know how to do that for one cell.
Code:
If Range("B4:B25").Value = "" Then
Else
Range("B4:B25").Value = Range("B4:B25").Value
End If
I have a list:
Item Code - Description - Quantity - Date
02099 - Blue Widgets - 500 - Jul 1
02099 - Blue Widgets - 200 - Jul 2
02099 - Blue Widgets - 300 - Jul 5
02099 - Blue Widgets - 500 - Jul 9
What formula can I use that returns the date (Jul 5) once the Widgets reaches 1,000 pcs?
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 am having trouble setting up my cycle count spread sheet. For instance if my starting number is 1000 and when it reaches 0 or below it starts to count in minus when I just want it to automatically reset to 1000
View 3 Replies View Relatedi am trying to create a spreadsheet that will automatically plan a days work in manufacturing based on the items the factory is making, the different items obviously have different hourly production targets and id lke to create a spread sheet that knows how many we want to create what the targets are and how long it will take. i also want it to be able to know when it has reached the needed total and automatically plot the targets for the next product, so what i need is a way to change part of the formula when the column reaches a set culmative total
View 3 Replies View RelatedI have a table array which shows me that amount of each I have in each spot.
What I need is a warning to auto popup when it reaches a certain number.
I need this for many different cells.
So if cell m20 changes from 3 to 2 it should popup and say only two xx are available and keep going down from there.
In a workbook I need to find all items belonging to a certain person and then copy those items to a new sheet, possibly separated by commas. To make it simple, see example :
Sheet 1 :
Fruit owner APPLEPEAR GRAPE BANANA
Fruit ownersJimJulia MirandaJason
JuliaMatthew MatthewJim
Sheet 2 : this is where I want to put the results
JimApple, Banana
JuliaPear, Apple
JasonBanana
MirandaGrape
MatthewPear, Grape
Need aid in copying certain cells with text/labels to clipboard to format a body of email to be sent via Yahoo webmail, so I can paste it where needed.
I did a couple of Excel VBA's back in 2000 or so at last job but haven't seen/used VBA since.
Have a spreadsheet with customer data, (one cust per row) and wish to create something like the following in clipboard (in this example, assuming row 2 is the selected row)...I'll only be sending out one or two of these per day so nothing fancy is needed, just a way to avoid retyping critical data that could get mistyped.
--------------------
A8
Rental details for 2008
Customer Name : A2
Unit nbr : A3
Nbr of weeks : A4
Balance due : A5
--------------------
I'm using Office 2000 still...I've done some searching but results found seem to complicated for my situation or not quite close enough for me to see how to apply it.
I want to copy a bunch of data from a text file and paste in into an excel worksheet I have open. I want to paste it at the end but I don't know how large the data range will be each time so I can't select that size range. I have this code so far:
FileToOpen = Application. GetOpenFilename("Text Files (*.txt), *.txt")
If FileToOpen <> False
Then
Workbooks.Open FileToOpen
Else
Exit Sub
End If
Cells.Select
Selection.Cut
Windows("myfile.xls").Activate
Range("A1").Select 'THIS IS THE OLD CODE FOR THE FIRST IMPORT
ActiveSheet.Paste 'I NEED TO REPLACE THIS WITH THE CODE FOR APPENDING
'OR PASTING AT THE END OF MY RANGE
I'm trying to create a sub that will save my worksheet to a tab delimited text file anytime there is a change in the worsheet data (all cells are linked to cells in other workbooks). I've figured out the command to save the file
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsChrisMy DocumentsBook1.txt", FileFormat:=xlText _
, CreateBackup:=False
but I'm not sure how to get a sub routine to start running when the file opens and to have it run continuously while open. I've found the command:
Application.Volatile
that will flag when any cells in my range are recomputed and run a function, but functions don't allow me to save the file.
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?