Hello,
Does this work as expected?
Column N needs to be empty though.
Sub TRACK_HOURS()
Application.ScreenUpdating = False
'PUT WEEK NUMBER IN COL N
For MY_ROWS = 10 To Range("A" & Rows.Count).End(xlUp).Row
Range("N" & MY_ROWS).Formula = "=weeknum(A" & MY_ROWS & ",2)"
Next...
Hello,
Oops. Just spotted that. The formula in M10 should be:
=IF(AND(O9>40,O10>40),K10,IF(O10>40,O10-40,0))
copy this down as far as required.
The reason I have blank cells, is that in the FILE, OPTIONS, ADVANCED. Display Options for this Worksheet, I have unchecked the display zeros...
Hello,
You are correct, I did mean Cell A10, not C10.
Have attached the file I created. I also copied the data down to try and replicate what you found, but it still appears to give correct results.
----------------------------------
Hope this helps.
----------------------------------
been...
Hello,
If you can add two columns (to make this easier) then you can do the following:
In N10 enter this formula:
=WEEKNUM(C10,2)
In O10 enter this formula:
=SUMIF($N$10:N10,WEEKNUM(C10,2),$K$10:K10)
In L10 enter this formula:
=IF(O10>40,K10-M10,K10)
In M10 enter this formula...
Hello,
select the cells, select CONDITIONAL FORMATTING and select Cell Value is, NOT EQUAL TO, MWZ-6006, format as required.
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now back
Hello,
This code replicates your code for Row 9. I have then made some assumptions:
Column C on multi test has data every other row so the next macro copies:
C11 to H15:L15
O11 to L23:O23
O10 to P23
P11 to L24:O24
P10 to P24
Sub COPY_OVER()
Application.ScreenUpdating = False
With...
Hello,
if you are still going with the multi sheet option, there are a couple of ways you can still get the sheet tab names and the link to the cell
Sub GET_TAB_NAMES()
For MY_SHEETS = 2 To ActiveWorkbook.Sheets.Count
Cells(MY_SHEETS + 4, 11).Value = Sheets(MY_SHEETS).Name &...
Hello,
no need for two cells, as long as your tabs are Sheet2, Sheet3 etc then on the index page in Cell K6 enter this formula:
=INDIRECT("Sheet"&ROW()-4&"!$F$9")
and copy down as far as required
----------------------------------
Hope this helps.
----------------------------------
been...
Forgot to say that the code does not take into account any error checking. It will also fail if the sheet names do not exactly match the list in the Trade List spreadsheet.
In your code, doesn't the set rng code cause problems if there are gaps in Column E?
Private Sub Worksheet_Change(ByVal...
Hello,
The code you have runs when you activate the macro. If you want the code to run when you enter a 0 or a 1 in column E then you can use the code below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Selection, Range("E12:E110")) Is Nothing Then Exit Sub...
Hello,
don't seem to be able to access your file from the link.
So have created a file for you to view.
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now...
Hello,
AS you have renamed it, you have not put the macro in the correct place.
When you go to the VB code window, you need to look in Microsoft Excel Objects under your spreadsheet name. Here you will find a list of the sheets/tabs in you spreadsheet. Find the one that has your drop downs...
Hello,
can be done via macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value = "YES" Then
With Range("A2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _...
Hello,
how about (in F2)
=INDEX($L$3:$L$326,MATCH(C3&D3,$I$3:$I$326&$J$3:$J$326,0))
and enter with CTRL + SHIFT + ENTER
then copy down as far as required.
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now back
Hello,
Have tried this code on a few different entries, and it seems to work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Format(Target.Value, "0" & "'-" & "0") <> Target.Value Then
MsgBox "WRONG"...
Hello,
this code can rename named ranges, not sure if it will work though.
Sub named_region()
MY_SOURCE = InputBox("Enter Source Sheet Name")
Sheets(MY_SOURCE).Range("AA1").Select
With Selection.ListNames
For MY_ROWS = 1 To Range(("AA") &...
Hello,
should have mentioned this only works when cell A1 is changed. I guess you have a formula. Try this
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
Select Case Range("A1").Value
Case "GLOBAL"
Range("C7").Locked = True...
Hello,
you originally asked for a cell to be locked. This can be done, although your sheet protection and cell protection could affect the outcome.
I have removed cell protection from all cells and have password protected the worksheet with no password.
This code needs to go into the relevant...
Hello (again).
Sorry above formula not accurate
try
=TEXT((A1*1000),"000")&TEXT((B1*1000),"000")&TEXT((C1*1000),"000")&TEXT((D1*1000),"000")
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now back
Hello,
not sure what the exact result you require, but something on the lines of:
=TEXT(VALUE(A1*1000&B1*1000&C1*1000&D1*1000),"000000000000")
is this of any use?
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now back
