No of consecutive days without sales.

[seedpan]

Hi,
I have an excel sheet that I want to do conditional formatting in.
this sheet is a product of an export from some other software and is sent to me on a daily basis.
in it are columns titled "today sales sec" and "today sales ter". I want to be able to see how many consecutive days I have had with no sales on today sales sec & ter.
the complication is that I will have different worksheets everyday. so I plan to have one copy on which I will write formulas and copy paste the cells that matter from the daily worksheets.
so summarising.. I need to know how to get the number of days with no sales (0 zero value) in one cell (this cell will be replaced daily with a new value. if the updated value is again zero then the no of days with no sales should go up by 1). I will only update the excel sheet once per day.
hope I have made myself clear. and I hope it isn't too difficult.
thanks.

 

[bhos123]

The simpler way could be.

Create a new file with different sheets.. Day0, Day1, Day2 ...Etc.

Create a sheet with name 'Results' and then Create a formula concatenating all the values in different sheets... each time you add a sheet with new data, you can update your formula and drag to other cells. you can actually see how many consecutive zeros you have in each cell.

 

[seedpan]

The simpler way could be.

Create a new file with different sheets.. Day0, Day1, Day2 ...Etc.

Create a sheet with name 'Results' and then Create a formula concatenating all the values in different sheets... each time you add a sheet with new data, you can update your formula and drag to other cells. you can actually see how many consecutive zeros you have in each cell.

Thanks for the quick reply.
However this solution will create a really big workbook which I don't need. And I only want to be notified when one cell (under total sales sec or ter) crosses 3 consecutive days without sales (3 consecutive days of 0 Value). I intend to use conditional formatting to highlight these cells if the counted value is over 3. But for that I want the cell that I create to count how many days the value is 0 and this cell returns to 0 if the cell being monitored shows any value that is not 0.
Concatenation would return a '000' I am guessing. How to use conditional formatting with that.
Please think of another simpler method that will not make the workbook big. Cos I will have sheets 365 days a year.
Thanks.

 

[bhos123]

'Keep all the files from Day0 to Day365 in a folder and create a file named "New" somewhere on desktop and create a sheet named 'Results' in that. Run the macro. after getting the results you can do custome filter on "_0_0_0"


Sub ProcessAll(Optional sPath As Variant)
Dim WB As Workbook, sFile As String
Dim i As Long

' This procedure will open all the files in a directory.
' Perhaps process them then close the files

Application.ScreenUpdating = False

If IsMissing(sPath) Then
sPath = "C:\Users\1307608\Desktop\Temp"
sFile = Dir(sPath & "*.xlsx")
End If

'Loop through all .xls-Files in that path
Do While sFile <> ""
Set WB = Workbooks.Open(sPath & sFile)
For i = 1 To 100
'Do something with that Workbook, insert whatever you want to do here
Workbooks("New").Sheets("Results").Range("A" & i).Value = Workbooks("New").Sheets("Results").Range("A" & i).Value & " _" & WB.Sheets("Sheet1").Range("A" & i).Value
Next
'You can save it, if you like, here it's not saved
WB.Close False

sFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[seedpan]

Sorry I know you are trying to help but this is way too complicated for me. I put all those sheets in one folder called New and then created a file called Results and saved that on desktop. I copy pasted the macro on to the results sheet and assigned the macro to a shape. Then pressed it. Now the rest is greek to me. I have no idea how to write a macro. I only know how to create one by recording steps. thank you for your help. if theres a simpler way please advice or if you believe I am on track and theres hope please explain in detail.
thank you so much.

 

[bhos123]

Please do this sequentially.
1)Create a folder on your desktop Naming 'Temp'. change the path(in my code sPath = "C:\Users\1307608\Desktop\Temp") as per your system
2)Put all your files like Day0 Day1 etc. in temp folder. assuming they only have one sheet.
3)create a excel file naming 'New' on your desktop
4) create a worksheet with name 'Results' in that file.
5) Run the macro.

This macro concatenates A1 from all the files to Results worksheet in the A1 cell.
This also does the same for all cells A1 to A100.

 

[seedpan]

Hello.
I figured out a way to get how many days I have with no sales. And it is updating everyday. I used dates to do this. I mean today()- last updated date (which in turn is updated automatically when the sales cell receives any input other than 0). I need to write a macro cos I am on libreoffice now and it does not have the record macro option.
I only need to copy paste a few columns.

So firstly let me ask you something. Assuming I copy the contents from the daily sheet and then I open this document.. If the first line of the macro reads
range ("a1") select
Selection .pastespecial paste:=xlpastevalues, operation:=xlNone, skipBlanks _
:=False, Transpose:= False

Will it paste the cells copied (which are now in the clipboard) to the cell A1?
Please tell me how to tell it to do this in the sheet called "RawData"
I plan to use a autoshape to assign the macro. And I will place this in another sheet say "results" is the name of the sheet. The macro has to go to that sheet and copy paste from and to a few cells. Another question I have if I can hide RawData sheet and only rely on the macro to do the initial pasting without actually seeing the page.
I request you to tell me how to write the following in a macro
1) paste items (that are in clipboard) in sheet 1
2) go to sheet 2
3) copy from cells Q10-Q110
4) paste special (only values) in cell P10-P110

 

[bhos123]

Sheets("Sheet1").Range("A1").SelectActiveSheet.Paste
Sheets("Sheet2").Range("Q10:Q110").copy
Sheets("Sheet2").Range("P10").select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[seedpan]

Thank you so much. You are the best.
I haven't made the macro yet cos Libreoffice says I need to install something before trying again. but It will work on my work computer where I have MS office.
I did the following for the number of days without sales thing.
=IF($Sheet1.Q5=0,$G$2-P5,0).... Q5-Q250 (sheet 1) is the today sales column, G2 = Today(), P5 -P250 is where your macro will paste the dates from Q5-Q250 in sheet 2.. and column Q contains =IF($Sheet1.Q5>0,$G$2,P5).. I think there will circular reference warnings but I think it will work. Hope it works. I will not be able to try for another week.
thanks.