Re: Data Processing Problem
is this what you want
=(((2*G4+2*H4)*I4)+1)*2
put in cell J4 and copy paste down
Re: Data Processing Problem
is this what you want
=(((2*G4+2*H4)*I4)+1)*2
put in cell J4 and copy paste down
Re: Find a ship date in one table that is >= a date in another table for a batch numb
this is one approach
=IFERROR(INDEX(tblShipStatus[ShipDate],SUMPRODUCT(ROW(tblShipStatus[JobNum])*(tblShipStatus[JobNum]=Table_DMR_ShipStatus[[#This Row],[Batch]])*(tblShipStatus[ShipDate]>=Table_DMR_ShipStatus[[#This Row],[DMR Date]])*(tblShipStatus[PartsQuantity]=Table_DMR_ShipStatus[[#This Row],[Shipped Quantity]]))-1),"")
however this formula would need to be tweaked if there are multiple shipped quantity with same values on different dates (currently it seems that is not happening)
Re: Generate values in a table based on dropdown selection (complex data)
from what I have understood
First...you create a List using Data Validation (Data Validation>>Settings>>List wher you select cols B1 to F1)
Second...suppose the list has been created in Cell B28 then
in Cell H3 you put this formula and copy paste down
=INDEX(B3:F3,,MATCH($B$28,$B$1:$F$1,0))
Re: Unzip Files using VBA
thanx for your replies....Holycow that worked fantastic...many thanx
I got confused in using the "Dir" command and hence getting a run time error when I was providing this as a value
I have also removed the line for creation of new folder
the new code is here for benefit of everybody
Sub filesToUnzip()
Dim oApplicationlication As Object
Dim fileName As Variant
Dim folderFileName As Variant
fileName = "{File name with location}"
folderFileName = "{location}"
Set oApplication = CreateObject("Shell.Application")
oApplication.Namespace(folderFileName).CopyHere oApplication.Namespace(fileName).items
DoEvents
MsgBox "You have extracted the zip files to **", vbInformation
End Sub
Display More
Hi Friends...
probably this question has been asked earlier but I am not able to find a fix for the same
this code for unzipping files works great excpet that it requires the user to select the file...I wanted the same to be automated
Sub filesToUnzip()
Dim oApplicationlication As Object
Dim fileName As Variant
Dim folderFileName As Variant
fileName = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)
folderFileName = "C:\UnzippedFiles" & "\"
MkDir folderFileName
Set oApplication = CreateObject("Shell.Application")
oApplication.Namespace(folderFileName).CopyHere oApplication.Namespace(fileName).items
DoEvents
MsgBox "You have extracted the zip files to C:\UnzippedFiles\", vbInformation
End Sub
Display More
specifically can someone tell me how exactly to change this line
fileName = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)
since if I supply the filename and path as a strng then I get runtime error
help appreciated
thanx
Re: Count cells adjacent to same value until sum of those cells reaches certain value
I am not saying this is the only way and I hope some Member comes out with a combined formula but one approach can be create a handler column in this case in Col I
where you put this formula
=SUM(($E$2:E2)*(($D$2:D2)=D2)) and copy down
then on Col G you can put this formula
=SUM((($D$2:$D$73)=D2)*(($I$2:$I$73)<=F2))
this will give you the necessary values...(pl check again)
Note both these formulae are array formula and need to be conformed by Ctrl+Shift+Enter
Re: Number of working days in month following a date.
you can use this formula
=NETWORKDAYS(EOMONTH(A1,0),EOMONTH(A1,1))
Re: perform number format if command button is clicked
why dont you try couching it within commandbutton_click
for eg
then whenever you click the commandbutton the code will get executed
the commnadbutton_click will be autoimatically available when you go into the UserForm in VBA and click the commandbutton
Re: Transpose Rows to columns
so why dont u just use the TRANSPOSE function...see the help files for more info
Re: Future year asset replacement costs modelling
in your example I havent understood why you havent included the value of 951 for 2024-25 in your calculations......as per what I have understood from your logic that value should also be included
having said that see this formula
=SUM(OFFSET($F$4,IF(ROW(F4)-9>(ROW($F$4)-1),ROW(F4)-7-ROW($F$4),1)-1,):F4)/3*550
put it in Row 5 and copy down
this works after including the value for year 2025 as above
see the attached file....the formula is highlighted therein
Re: Spread a total across specific months
this is one method
=IF(F$1=$C2,$E2-(ROUNDDOWN($E2/($C2-$B2+1),0)*($C2-$B2)),IF(AND(F$1>=$B2,F$1<$C2),ROUNDDOWN($E2/($C2-$B2+1),0),0))
although for the last month it leaves a higher remainder......check if thats ok or else I will tweak the formula likewise
Re: Finding irregularities in a pattern
not sure if this is really what you want but see if this helps
=IF(CODE(C3)-CODE("a")+1=IF(MOD(ROW(C3)-ROW($C$3)+1,7)=0,7,MOD(ROW(C3)-ROW($C$3)+1,7)),"REG","IRREG")
it starts from Cell C3 and cehcks if alphabets a to g come in the correct sequence, if yes it will give REG else IRREG
one can even use it to conidtionally format
Re: Back fill data for the week based on the friday value
this is one approach
=IF(ISERROR(MATCH(A5,$A$1:$A$2,0))=TRUE,INDEX($B$1:$B$2,MATCH(A5,$A$1:$A$2,1)+1),INDEX($B$1:$B$2,MATCH(A5,$A$1:$A$2,0)))
Re: Excel "sudoku"
QuoteCAN EXCEL EVEN DO THIS?!
Not sure if you have heard of SOLVER utility within excel???
using that I got the following values for your problem
A1>>91 B1>>275 C1>>134 B2>>250 A3>>9 B3>>275 C3>>66
remaining cells zero...try it
Re: Optimization Constraints
do you really expect soimeone to reply to this kind of post
upload a file with this data and the results you expect if you want someone to seriously consider your post
Re: If Cell A1 = certain criteria how to extract the information from another sheet
you can use something like this
Sub sheetmac()
Dim mysheet As Variant
mysheet = Sheets("sheet1").Range("A1").Value
Sheets("sheet1").Range("C5") = Sheets(mysheet).Range("A1")
End Sub
a formula based approach would work like this
=INDIRECT(ADDRESS(1,1,,,A1))
Re: index and match question
you can use this formula
=INDEX($C$2:$C$23,MATCH($E3&F$2,$A$2:$A$23&$B$2:$B$23,0))
note this is an array formula and needs to be conformed by Ctrl+Shift+Enter
also for some combimnations the value does not exist and this will return an NA error hence couch this formula within IFERROR
Re: Counting with multiple entries in multiple columns
you can use this formula
=SUMPRODUCT(((Sheet1!$A$3:$A$14)=A3)*IF((ISNUMBER(Sheet1!$H$3:$H$14)+ISNUMBER(Sheet1!$I$3:$I$14)+ISNUMBER(Sheet1!$J$3:$J$14))>0,1,0))
note this is an array formula and needs to be confirmed by Ctrl+Shift+Enter
Re: Populate other cells based on specific criteria
use this formula
=INDEX($G$2:$G$297,LARGE(ROW($A$2:$A$297)*(($A$2:$A$297)=A2)*(($E$2:$E$297)=$E$9),1)-ROW($F$2)+1)
note this is an array based formula and needs to be conformed by Ctrl+Shift+Enter
also note that do not use this formula in Column G as it will replace the manual entries in that column....therefore use it in another column lets say H
Re: How to average column data based on row and column headers
this is one approach
=AVERAGE(INDIRECT(ADDRESS(MATCH($A$2,Sheet1!$A$2:$A$6,0)+ROW(Sheet1!$A$2)-1,COLUMN(Sheet1!$B$1),,,"Sheet1")&":"&ADDRESS(MATCH($A$2,Sheet1!$A$2:$A$6,0)+ROW(Sheet1!$A$2)-1,MATCH($B$1,Sheet1!$B$1:$H$1,0)+COLUMN(Sheet1!$B$1)-2)))