User Profile
sivakumarrj
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Using Excel Spreadsheet to log calls by month, where each tab is a day of the month (M-F)
To automate insert sheets, better to use VBA, here I have given code for inserting March 2024, Sub InsertMarch2024Tabs() ' Set the desired month and year Const DesiredMonth As Integer = 3 Const DesiredYear As Integer = 2024 ' Get the start date of the desired month Dim StartDate As Date StartDate = DateSerial(DesiredYear, DesiredMonth, 1) ' Determine the number of days in the desired month Dim DaysInMonth As Integer DaysInMonth = Day(DateSerial(DesiredYear, DesiredMonth + 1, 0)) ' Loop through each day of the month Dim i As Integer For i = 1 To DaysInMonth ' Add a new worksheet Sheets.Add After:=Sheets(Sheets.Count) ' Name the worksheet with the current date in "dd-mmm-yyyy" format ActiveSheet.Name = Format(StartDate + i - 1, "dd-mmm-yyyy") Next i End Sub Before that, please make sure that proper changes for specific months to insert sheets in worksheet. ThanksRe: Mix between vlookup and sum command?
It is better go with Sumifs with Index match function. Incase only two columns are there, you can for sumif with index match function. More than two columns better go for sumifs with index match function. sumif with index match =SUMIF(Range,Criteria, INDEX(Range,0,MATCH(lookup,Range,0))) More than 2 columns to match =SUMIFS(INDEX(Range,0,MATCH(Lookup value, search range,0)),Criteria range1, Criteria1, Criteria range2, Criteria 2) Based on data, criteria 1 and 2 can be decided. ThanksRe: Formulas
Excel basics are very important, for every formula it should start with = and correct spelling of formula. In case, first step of prefixing of = with formula, then check whether cell is left aligned or right aligned. Left aligned means it considered as Text (by default) and right aligned means numbers. Rest of things need to be checked settings. ThanksRe: Formula help
Best way is to provide file, Still Vlookup or Xlookup can be used with search and * for lookup value for example, Product Id Description Sales Qty 100S4000 Small T Shirt 12 100M4000 Medium T Shirt 15 100X4000 Large T Shirt 17 100XL4000 Extra Large T Shirt 12 To get quantity of Prod id 100S4000, use Vlookup or Xlookup =VLOOKUP(100&"S"&"*",$A$2:$C$5,3,0) here the lookup value search for 100 and S then any number, based on lookup it gives result. ThanksRe: SUMIF BASED ON MULTIPLE RANGE WITH SINGLE CRITERIA
TEED2019 It seems that needs formula for specific sum for each item number , for example item 1 appears two times means it should be 65 + 65. in that case you need to create all item number should be created with sumif 65 Item 1 Item2 Item3 Item4 Item5 Item6 Item7 65 Item3 Item3 Item2 Item2 Item4 Item4 Item5 Item 1 65 65 Item5 Item4 Item2 Item2 325 39 Item5 Item3 390 65 Item3 Item3 Item2 Item4 Item4 325 65 Item3 Item5 234 Item6 65 Item7 65 =SUMIF($B$2:$B$7,J3,$A$2:$A$7)+SUMIF($C$2:$C$7,J3,$A$2:$A$7)+SUMIF($D$2:$D$7,J3,$A$2:$A$7)+SUMIF($E$2:$E$7,J3,$A$2:$A$7)+SUMIF($F$2:$F$7,J3,$A$2:$A$7)+SUMIF($G$2:$G$7,J3,$A$2:$A$7)+SUMIF($H$2:$H$7,J3,$A$2:$A$7)Re: How to enable the Filter by Color
Please check this Microsoft Link https://support.microsoft.com/en-us/office/filter-by-font-color-cell-color-or-icon-sets-4af3e865-3e34-4d81-9814-1893ffaada3c#:~:text=On%20the%20Data%20tab%2C%20click%20Filter.&text=in%20the%20column%20that%20contains,and%20then%20click%20the%20criteria.Re: Need help with multiple IF formulas in a single cell
just try with IFS FORMULA =IFS($A2='Quarterly Goals'!$A$2,'Quarterly Goals'!C$2,$A2='Quarterly Goals'!$A$3,'Quarterly Goals'!C$3,$A2='Quarterly Goals'!$A$4,'Quarterly Goals'!C$4,$A2='Quarterly Goals'!$A$5,'Quarterly Goals'!C$5,$A2='Quarterly Goals'!$A$6,'Quarterly Goals'!C$6,$A2='Quarterly Goals'!$A$7,'Quarterly Goals'!C$7,$A2='Quarterly Goals'!$A$8,'Quarterly Goals'!C$8,$A2='Quarterly Goals'!$A$9,'Quarterly Goals'!C$9,$A2='Quarterly Goals'!$A$10,'Quarterly Goals'!C$10,,true,"")Re: Goal seek not giving the answer
anderspore Excel designed for getting specific number by changing only one cell. First you need to set cell i.e.B4 then to value 10 and finally give cell reference in by changing cell address B2 or B3. There must be formula for total in B4 to get adjusted. We have given target value 10, then B3 value will be changed to 6.Re: MULTIPLE IF CONDTITIONS OVER 64
For multiple nested if, it is better to use IFS formula =IFS(B2=0.1,Gas!C35,B2=0.2,Gas!D35,B2=0.3,Gas!E35,B2=0.4,Gas!F35,B2=0.5,Gas!G35,B2=0.6,Gas!H35,B2=0.7,Gas!I35,B2=0.8,Gas!J35,B2=0.9,Gas!K35,B2=1.0,Gas!B36,B2=1.1,Gas!C36,B2=1.2,Gas!D36,B2=1.3,Gas!E36,B2=1.4,Gas!F36,B2=1.5,Gas!G36,B2=1.6,Gas!H36,B2=1.7,Gas!I36,B2=1.8,Gas!J36,B2=1.9,Gas!K36,B2=2.0,Gas!B37,B2=2.1,Gas!C37,B2=2.2,Gas!D37,B2=2.3,Gas!E37,B2=2.4,Gas!F37,B2=2.5,Gas!G37,B2=2.6,Gas!H37,B2=2.7,Gas!I37,B2=2.8,Gas!J37,B2=2.9,Gas!K37,B2=3.0,Gas!B38,B2=3.1,Gas!C38,B2=3.2,Gas!D38,B2=3.3,Gas!E38,B2=3.4,Gas!F38,B2=3.5,Gas!G38,B2=3.6,Gas!H38,B2=3.7,Gas!I38,B2=3.8,Gas!J38,B2=3.9,Gas!K38,B2=4.0,Gas!B39,B2=4.1,Gas!C39,B2=4.2,Gas!D39,B2=4.3,Gas!E39,B2=4.4,Gas!F39,B2=4.5,Gas!G39,B2=4.6,Gas!H39,B2=4.7,Gas!I39,B2=4.8,Gas!J39,B2=4.9,Gas!K39,B2=5.0,Gas!B40,B2=5.1,Gas!C40,B2=5.2,Gas!D40,B2=5.3,Gas!E40,B2=5.4,Gas!F40,B2=5.5,Gas!G40,B2=5.6,Gas!H40,B2=5.7,Gas!I40,B2=5.8,Gas!J40,B2=5.9,Gas!K40,B2=6.0,Gas!B41,B2=6.1,Gas!C41,B2=6.2,Gas!D41,B2=6.3,Gas!E41,B2=6.4,Gas!F41,B2=6.5,Gas!G41,B2=6.6,Gas!H41,B2=6.7,Gas!I41,B2=6.8,Gas!J41,B2=6.9,Gas!K41,B2=7.0,Gas!B42,B2=7.1,Gas!C42,B2=7.2,Gas!D42,B2=7.3,Gas!E42,B2=7.4,Gas!F42,B2=7.5,Gas!G42,B2=7.6,Gas!H42,B2=7.7,Gas!I42,B2=7.8,Gas!J42,B2=7.9,Gas!K42,B2=8.0,Gas!B43,B2=8.1,Gas!C43,B2=8.2,Gas!D43,B2=8.3,Gas!E43,B2=8.4,Gas!F43,B2=8.5,Gas!G43,B2=8.6,Gas!H43,B2=8.7,Gas!I43,B2=8.8,Gas!J43,B2=8.9,Gas!K43,B2=9.0,Gas!B44,B2=9.1,Gas!C44,B2=9.2,Gas!D44,B2=9.3,Gas!E44,B2=9.4,Gas!F44,B2=9.5,Gas!G44,B2=9.6,Gas!H44,B2=9.7,Gas!I44,B2=9.8,Gas!J44,B2=9.9,Gas!K44,B2=10.0,Gas!B45,B2=10.1,Gas!C45,B2=10.2,Gas!D45,B2=10.3,Gas!E45,B2=10.4,Gas!F45,B2=10.5,Gas!G45,B2=10.6,Gas!H45,B2=10.7,Gas!I45,B2=10.8,Gas!J45,B2=10.9,Gas!K45,B2=11.0,Gas!B46,B2=11.1,Gas!C46,B2=11.2,Gas!D46,B2=11.3,Gas!E46,B2=11.4,Gas!F46,B2=11.5,Gas!G46,B2=11.6,Gas!H46,B2=11.7,Gas!I46,B2=11.8,Gas!J46,B2=11.9,Gas!K46,B2=12.0,Gas!B47,B2=12.1,Gas!C47,B2=12.2,Gas!D47,B2=12.3,Gas!E47,B2=12.4,Gas!F47,B2=12.5,Gas!G47,B2=12.6,Gas!H47,B2=12.7,Gas!I47,B2=12.8,Gas!J47,B2=12.9,Gas!K47,B2=13.0,Gas!B48,B2=13.1,Gas!C48,B2=13.2,Gas!D48,B2=13.3,Gas!E48,B2=13.4,Gas!F48,B2=13.5,Gas!G48,B2=13.6,Gas!H48,B2=13.7,Gas!I48,B2=13.8,Gas!J48,B2=13.9,Gas!K48,B2=14.0,Gas!B49,B2=14.1,Gas!C49,B2=14.2,Gas!D49,B2=14.3,Gas!E49,B2=14.4,Gas!F49,B2=14.5,Gas!G49,B2=14.6,Gas!H49,B2=14.7,Gas!I49,B2=14.8,Gas!J49,B2=14.9,Gas!K49,B2=15.0,Gas!B50,B2=15.1,Gas!C50,B2=15.2,Gas!D50,B2=15.3,Gas!E50,B2=15.4,Gas!F50,B2=15.5,Gas!G50,B2=15.6,Gas!H50,B2=15.7,Gas!I50,B2=15.8,Gas!J50,B2=15.9,Gas!K50,TRUE,0) Excel provides multiple solution for similar one issue.
