What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

***Official*** Excel Help Corner (2 Viewers)

tjnc09 said:
Did you pull the two tabs from different data sources?  Any chance there is extra white space and need to TRIM one or both of the common identifiers?   

(if you click in the white function bar after clicking in a cell, does the cursor blink directly after the data vs separate from the data?)
Click to expand...
Its from 2 separate files.  Not sure.  I’ll have to check tomorrow 

ill see if I can post a sample google doc, but new job has a lot of stuff on lockdown

oh and I’m pretty much a basic excel user 

 
Hello,

I want to calculate the total hours (Column H - Other Hours) for ID's that don't match to any of the three columns (B, C OR D) in Sheet 1. 

https://docs.google.com/spreadsheets/d/1JFonSul8YJcZhzWRUZFZQ6R3alRiqEJo5fHvc72u3cM/edit?usp=sharing

- There are 6 hours associated to ID 123 that are not tied to any of the names in Name1, Name2 or Name3 fields (ex. Tim).
- There are 0 hours associated to ID 234 that are not tied to any of the names in Name1, Name2 or Name3 fields.
- There are 2 hours associated to ID 345 that are not tied to any of the names in Name1, Name2 or Name3 fields (ex. Bob).

The values in orange are the expected values that should be populated. For example, in Sheet 1, when ID = 123 and Name1 = Brian, there are 0 hours that will be aggregated (the same is true for Steve and Jim), but Tim, who is not a name in Name1, Name2 or Name3 has a total of 6 hours for ID 123. In this scenario, under 'Other Hours, a total of 6 hours should be calculated.

The values in yellow are posting the total hours when the ID has hours not accounted for in Name1, Name2 or Name3.

How do I write a formula to accomplish this? 

Thanks!

 
cubd8 said:
Hello,

I want to calculate the total hours (Column H - Other Hours) for ID's that don't match to any of the three columns (B, C OR D) in Sheet 1. 

https://docs.google.com/spreadsheets/d/1JFonSul8YJcZhzWRUZFZQ6R3alRiqEJo5fHvc72u3cM/edit?usp=sharing

- There are 6 hours associated to ID 123 that are not tied to any of the names in Name1, Name2 or Name3 fields (ex. Tim).
- There are 0 hours associated to ID 234 that are not tied to any of the names in Name1, Name2 or Name3 fields.
- There are 2 hours associated to ID 345 that are not tied to any of the names in Name1, Name2 or Name3 fields (ex. Bob).

The values in orange are the expected values that should be populated. For example, in Sheet 1, when ID = 123 and Name1 = Brian, there are 0 hours that will be aggregated (the same is true for Steve and Jim), but Tim, who is not a name in Name1, Name2 or Name3 has a total of 6 hours for ID 123. In this scenario, under 'Other Hours, a total of 6 hours should be calculated.

The values in yellow are posting the total hours when the ID has hours not accounted for in Name1, Name2 or Name3.

How do I write a formula to accomplish this? 

Thanks!
Click to expand...
Seems like a sum if formula should work but then again I am drunk posting trying to type it on Excel on an iPad.

 
A few tidbits that have helped me out of jams in the past when doing tricky vlookups....

Fuzzy Lookups - Sets a percent accuracy to match your lookup to.  Great if you have a file with misspellings, i.e. potato and pottato, etc.

Helper columns.  I haven’t used concatenate numerous times to create a unique helper column for lookups.

 
Current Formula:
=SUMIFS(ReClassify!$BE$2:$BE$106824, ReClassify!$BD$2:$BD$106824, 'Mega-Raw Data'!B2, ReClassify!$BF$2:$BF$106824, 'Mega-Raw Data'!DA2)

If I wanted to add logic to say:
IF Column CR = NO, then calculate the above forumula, else set the value above to '0'.

 
Dan Lambskin said:
Tried that and I can pull info for 1 cell in but when I fill down I get NA.

just to clarify the entries in tab 1 are in a completely different order than those in tab 2, but I can’t just sort because i need to keep tab 2 as is
Click to expand...
Would it help if you made a duplicate of Tab 2 and then sorted it to match Tab 1? Then delete the duplicate tab when you are done?

 
Walking Boot said:
I'm running Excel 16.10 on a Mac.

Yes, I know. I don't have a choice. Work computers. Fight me.

Anyway, it seems that the sheets change order randomly. Is there a keyboard combination I might accidentally be pressing that moves sheet tabs around? Or is it just random gremlin mischief?
Click to expand...
Gremlin, IMO. There's not a keyboard shortcut for that that I'm aware of. It's either drag and drop or use the context menus at the top. 

 
Any super gurus in the DC area available tomorrow? 

Most pressing formula I need is if value in column A is less than 1, "S", if greater than 1 but less than 7 ,"Y", if greater than 7 but less than 31 "Z". And can that extend to more conditions?

Please help. Time sensitive.

 
I have a bad habit of linking cells to other files in models I use monthly to forecast in order to keep an audit trail, but then I end up not documenting why or where in the file I’ve linked something. Is there an easy way to locate which cells in a workbook contain links to other files? I end up with way more links to files than I’d like and am Leary to break links. Inserting comments when / where I link something might be the best solution.

 
Apple Jack said:
Any super gurus in the DC area available tomorrow? 

Most pressing formula I need is if value in column A is less than 1, "S", if greater than 1 but less than 7 ,"Y", if greater than 7 but less than 31 "Z". And can that extend to more conditions?

Please help. Time sensitive.
Click to expand...
I used this formula a while back to do something similar to what you're trying to do for ~10 number ranges. I was using this formula in column B, looking up values in column A based on number ranges I had in column C, and returning labels I listed in column D. So in your case you'd put 1 in cell C1, 7 in cell C2, and 31 in C3 with labels S, Y, and Z in D1, D2, D3, etc. 

=IF(A1<$C$1,$D$1,IF(A1<$C$2,$D$2,IF(A1<$C$3,$D$3,IF(A1<$C$4,$D$4,IF(A1<$C$5,$D$5,IF(A1<$C$6,$D$6,IF(A1<$C$7,$D$7,IF(A1<$C$8,$D$8,$D$9))))))))

 
Apple Jack said:
Any super gurus in the DC area available tomorrow? 

Most pressing formula I need is if value in column A is less than 1, "S", if greater than 1 but less than 7 ,"Y", if greater than 7 but less than 31 "Z". And can that extend to more conditions?

Please help. Time sensitive.
Click to expand...
What if its =7?

The below will do exactly what you posted above, but 7 will fall through the cracks.  

=IF(A1<1,"S",IF(AND(A1>1,A1<7),"Y",IF(AND(A1>7,A1<31),"Z","MORE CONDITIONS")))

If you want to add more conditions, just copy/paste this over the "MORE CONDITIONS" (including the quotation marks) in the above and change the conditions in red and results in green as needed.

IF(AND(A1>99,A1<99),"AA","MORE CONDITIONS")

 
TheWalkmen said:
I have a bad habit of linking cells to other files in models I use monthly to forecast in order to keep an audit trail, but then I end up not documenting why or where in the file I’ve linked something. Is there an easy way to locate which cells in a workbook contain links to other files? I end up with way more links to files than I’d like and am Leary to break links. Inserting comments when / where I link something might be the best solution.
Click to expand...
Do a find for [ and tell it to search the entire workbook.

 
Thanks guys, I'm having some issues getting the results to return properly in some cells. Appears to be a formatting issue. But thanks so much for the replies.

 
Ned said:
What are you looking to do when you find the links?
Click to expand...
Just see what it’s linked to to see if I can break it without messing up my models. The number of links in some of my models is getting out of hand.

I have models with links that I update monthly as standard work then I always end up with several one off links from the prior month, which is what I’m trying to limit or eliminate. 

 
Last edited by a moderator:
TheWalkmen said:
Just see what it’s linked to to see if I can break it without messing up my models. The number of links in some of my models is getting out of hand.

I have models with links that I update monthly as standard work then I always end up with several one off links from the prior month, which is what I’m trying to limit or eliminate. 
Click to expand...
I'd think a macro would be the best bet.  Here's a quick and dirty that'll scan a tab for all linked cells and list them out on a new tab.  It assumes the data is in a neat/clean table with no breaks (blanks) in the rows/columns.  Just click on the upper left cell in the data and run the macro... It's hard to write these things blind, so take it FWIW.  :2cents:  

Code: 
Sub Find_All_Links()
    Dim SrchRng As Range, SrchCell As Range, NewTab As String, CurrTab As String
    Application.ScreenUpdating = False
    CurrTab = ActiveSheet.Name
    Set SrchRng = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
    For Each SrchCell In SrchRng
        If InStr(SrchCell.Formula, "[") > 0 Then
            If NewTab = vbNullString Then
                Sheets.Add After:=ActiveSheet
                NewTab = ActiveSheet.Name
                Columns("A:B").NumberFormat = "@"
                Sheets(CurrTab).Select
            End If
            Sheets(NewTab).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = SrchCell.Address
            Sheets(NewTab).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = SrchCell.Formula
        End If
    Next SrchCell
End Sub



 
Ned said:
I'd think a macro would be the best bet.  Here's a quick and dirty that'll scan a tab for all linked cells and list them out on a new tab.  It assumes the data is in a neat/clean table with no breaks (blanks) in the rows/columns.  Just click on the upper left cell in the data and run the macro... It's hard to write these things blind, so take it FWIW.  :2cents:  

Code: 
Sub Find_All_Links()
    Dim SrchRng As Range, SrchCell As Range, NewTab As String, CurrTab As String
    Application.ScreenUpdating = False
    CurrTab = ActiveSheet.Name
    Set SrchRng = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
    For Each SrchCell In SrchRng
        If InStr(SrchCell.Formula, "[") > 0 Then
            If NewTab = vbNullString Then
                Sheets.Add After:=ActiveSheet
                NewTab = ActiveSheet.Name
                Columns("A:B").NumberFormat = "@"
                Sheets(CurrTab).Select
            End If
            Sheets(NewTab).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = SrchCell.Address
            Sheets(NewTab).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = SrchCell.Formula
        End If
    Next SrchCell
End Sub
Click to expand...
Thanks!

 
  • Smile
Reactions: Ned
Apple Jack, your best bet is to create a table and use VLOOKUP.  Unnecessary to imbed that many IF/THEN formulas.

 
not an issue with excel, but i need a nerd and nowhere else to turn.

so, wife got a new work computer and all programs have been moved over; however, she had adobe acrobat standard on her old computer and while the program moved over, she has no access unless she enters the old serial number.  but no idea where to get or find.  googled a lot and maybe there is a nerdy back door way to figure out the serial number to reenter into the new computer?

thc

 
Chemical X said:
not an issue with excel, but i need a nerd and nowhere else to turn.

so, wife got a new work computer and all programs have been moved over; however, she had adobe acrobat standard on her old computer and while the program moved over, she has no access unless she enters the old serial number.  but no idea where to get or find.  googled a lot and maybe there is a nerdy back door way to figure out the serial number to reenter into the new computer?

thc
Click to expand...
still have the old computer? If so, pull up Adobe on there, and from the menu at the top: Help -> About Adobe Acrobat and on the splash screen that comes up, it usually displays the serial number there. If not, you're at the mercy of her IT, but if they installed the program, they should have serial numbers (maybe a site license for the whole company)

 
Playing around with Excel for some data for my daughter's Girl Scout Troop's Cookie sales (Cookie Dad! :hifive: ) I am trying to set up my spreadsheets now, so that when it comes up next year they are all organized and the problems I remember having are taken care of now.

I have a situation in each Scout's tab where I want to be able to change the value of a cell if another cell certain characters.

This routine works for changing one row. Where I am having difficulty is trying to rework this so that I can 'watch' a range of cells so that if C7 is altered than I check and if the change is applicable, then the value for the other cell on the same line is changed as well.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C6")) Is Nothing Then
   If Range("C6") = "L" Or Range("C6") = "O" Then
      Sheets("ScoutName").Range("Q6").Value = Sheets("ScoutName").Range("P6").Value
   Else: Sheets("ScoutName").Range("Q6").Value = 0
   End If
End If

End Sub
 
Click to expand...
The next step after that is to rework the sub and generalize it so that I don't have to have a separate sub for each tab, if possible.

TIA.

 
Last edited by a moderator:
Mr. Ected said:
Playing around with Excel for some data for my daughter's Girl Scout Troop's Cookie sales (Cookie Dad! :hifive: ) I am trying to set up my spreadsheets now, so that when it comes up next year they are all organized and the problems I remember having are taken care of now.

I have a situation in each Scout's tab where I want to be able to change the value of a cell if another cell certain characters.

This routine works for changing one row. Where I am having difficulty is trying to rework this so that I can 'watch' a range of cells so that if C7 is altered than I check and if the change is applicable, then the value for the other cell on the same line is changed as well.

The next step after that is to rework the sub and generalize it so that I don't have to have a separate sub for each tab, if possible.

TIA.
Click to expand...
I am all for using VBA and use it too much at times, but I can't figure out why you're using VBA here instead of an INDEX/MATCH or VLOOKUP instead?  You can point either formula to a standard table you have in a separate tab that will drive your If/Then/Else criteria.

 
Ned said:
Mr. Ected said:
Playing around with Excel for some data for my daughter's Girl Scout Troop's Cookie sales (Cookie Dad! :hifive: ) I am trying to set up my spreadsheets now, so that when it comes up next year they are all organized and the problems I remember having are taken care of now.

I have a situation in each Scout's tab where I want to be able to change the value of a cell if another cell certain characters.

This routine works for changing one row. Where I am having difficulty is trying to rework this so that I can 'watch' a range of cells so that if C7 is altered than I check and if the change is applicable, then the value for the other cell on the same line is changed as well.

The next step after that is to rework the sub and generalize it so that I don't have to have a separate sub for each tab, if possible.

TIA.
Click to expand...
I am all for using VBA and use it too much at times, but I can't figure out why you're using VBA here instead of an INDEX/MATCH or VLOOKUP instead?  You can point either formula to a standard table you have in a separate tab that will drive your If/Then/Else criteria.
Click to expand...
Interesting, I will look at these.

 
Mr. Ected said:
Ned said:
Mr. Ected said:
Playing around with Excel for some data for my daughter's Girl Scout Troop's Cookie sales (Cookie Dad! :hifive: ) I am trying to set up my spreadsheets now, so that when it comes up next year they are all organized and the problems I remember having are taken care of now.

I have a situation in each Scout's tab where I want to be able to change the value of a cell if another cell certain characters.

This routine works for changing one row. Where I am having difficulty is trying to rework this so that I can 'watch' a range of cells so that if C7 is altered than I check and if the change is applicable, then the value for the other cell on the same line is changed as well.

The next step after that is to rework the sub and generalize it so that I don't have to have a separate sub for each tab, if possible.

TIA.
Click to expand...
I am all for using VBA and use it too much at times, but I can't figure out why you're using VBA here instead of an INDEX/MATCH or VLOOKUP instead?  You can point either formula to a standard table you have in a separate tab that will drive your If/Then/Else criteria.
Click to expand...
Interesting, I will look at these.
Click to expand...
I have gotten it to work. I looked at the INDEX/MATCH and VLOOKUP and neither fit what I needed to do. They seemed to be more for pulling info out of an existing group of data. What I needed was to do if something is changed, then look at it and if it is X change this cell.

I made the cell that would be changing into a dropdown and added this Subroutine. The key for me was the Target.Offset. This allowed me to stay on the same row and just change a cell that was x cells away. The worksheets I am using are basically forms that I am using to maintain records for the Cookie Sales and the alteration is in rows not cells.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim controlRng, nRng As Range
    Set controlRng = Range("C2:C13")
    Set nRng = Intersect(controlRng, Target)

    If nRng Is Nothing Then Exit Sub

    If Target.Value = "Local" Then
        Target.Offset(0, 14) = Target.Offset(0, 13)

    ElseIf Target.Value = "Shipped" Then
        Target.Offset(0, 14) = Target.Offset(0, 13)
    Else
        Target.Offset(0, 14) = 0
        
    End If

End Sub
Click to expand...
What I am trying to work with now is a 'Select Case' routine that will allow me to have this little function operate on a set of worksheets in the file. Stumbling, but it's fun to work with. This is what I started with (from a help site)

Sub WorksheetsByCodeName()
Dim ws As Worksheet For Each ws In Worksheets
'Select case is case sensitive
Select Case UCase(ws.CodeName)

     Case "SHEET1", "SHEET3", "SHEET5" 'etc
               With ws

              'With Code Here

              End With

'Optional use when most sheets are to be used
'In this case, use "Case" to name sheets to be NOT used

    Case Else

               With ws

              'With Code Here

              End With

End Select

Next ws

End Sub
Click to expand...
 
Last edited by a moderator:
  • Smile
Reactions: Ned
You can use Select Case in your if/ifelse/else combo just like you're starting to do with your sheets.  It's a little easier when you're wanting multiple values to trigger the same response - especially if you ever look to expand this later with more Target.Value conditions.

Code: 
Select Case Target.Value
    Case "Local", "Shipped"
        Target.Offset(0, 14) = Target.Offset(0, 13)    
    Case Else
        Target.Offset(0, 14) = 0
End Select
 
Ned said:
You can use Select Case in your if/ifelse/else combo just like you're starting to do with your sheets.  It's a little easier when you're wanting multiple values to trigger the same response - especially if you ever look to expand this later with more Target.Value conditions.

Select Case Target.Value
Case "Local", "Shipped"
Target.Offset(0, 14) = Target.Offset(0, 13)
Case Else
Target.Offset(0, 14) = 0
End Select
Click to expand...

Nice! Was wondering how to put an 'or' on my IF...THEN statement. When I did If Target.Value = "Local" Or "Shipped" Then it didn't work, so I just stuck with the ELSEIF statement. This is much cleaner.

Will Nesting Select/Case statements cause any issues?

In reality doing what I want is probably meant for a small DB. Way back when I did some minor programing on Access but since I am on Mac now we don't get Access with Office :rant: . I will get this set up, then I will try to do some stuff with Libre's DB or even FileMaker if I can get my hands on a copy.

Thanks!

 
Mr. Ected said:
Nice! Was wondering how to put an 'or' on my IF...THEN statement. When I did If Target.Value = "Local" Or "Shipped" Then it didn't work, so I just stuck with the ELSEIF statement. This is much cleaner.

Will Nesting Select/Case statements cause any issues?

In reality doing what I want is probably meant for a small DB. Way back when I did some minor programing on Access but since I am on Mac now we don't get Access with Office :rant: . I will get this set up, then I will try to do some stuff with Libre's DB or even FileMaker if I can get my hands on a copy.

Thanks!
Click to expand...
Haha I’ve been down that road. 

Your OR didn’t work because you need to tell it what you’re testing again (but I prefer Select Case):

Code: 
If Target.Value = "Local" Or Target.Value = "Shipped"



You can nest as many Select Cases as you want.

 
Ned said:
Haha I’ve been down that road. 

Your OR didn’t work because you need to tell it what you’re testing again (but I prefer Select Case):

Code: 
If Target.Value = "Local" Or Target.Value = "Shipped"
You can nest as many Select Cases as you want.
Click to expand...
Thanks!

I like the Select Case better for this determination. Especially with the worksheets, since most of them will be using this formula, I may select those that don't fit and bounce them and do the rest in the Case Else.

 
Looking for help:

Tab A:

4,000 rows, each with a (not unique) reference number in column A

Tab B:

40 rows, each with (a unique) reference number in column A and date in column B

What's the easiest way to add a column to Tab A that will pull the date from Tab B if the reference numbers match?

 
The Noid said:
Looking for help:

Tab A:

4,000 rows, each with a (not unique) reference number in column A

Tab B:

40 rows, each with (a unique) reference number in column A and date in column B

What's the easiest way to add a column to Tab A that will pull the date from Tab B if the reference numbers match?
Click to expand...
An INDEX/MATCH nesting should do it...

=INDEX(unique40tab!B:B,MATCH(4krowstab!A1,unique40tab!A:A,0),0)

 
vlookup will do the same thing.  index/match is useful when what you are looking up is not the left-most column.  but in this case, it is.

 
Hopefully a quick question and easy answer tonight:

Current formula:
=IF(OR(A3>0, B3>0), "On File", "Not on File")

B3 can also have a value of 'NONE'. that should also resolve to "Not on File".
How would I modify the current formula to account for "NONE"?

Examples:
A3 = 0, B3 = 0 ->>>>> "Not on File"
A3 = 0, B3 = NONE ->>>>> "Not on File"
A3 = 0, B3 = 10 ->>>>> "On File"
A3 = 1, B3 = 11 ->>>>> "On File

 
cubd8 said:
Hopefully a quick question and easy answer tonight:

Current formula:
=IF(OR(A3>0, B3>0), "On File", "Not on File")

B3 can also have a value of 'NONE'. that should also resolve to "Not on File".
How would I modify the current formula to account for "NONE"?

Examples:
A3 = 0, B3 = 0 ->>>>> "Not on File"
A3 = 0, B3 = NONE ->>>>> "Not on File"
A3 = 0, B3 = 10 ->>>>> "On File"
A3 = 1, B3 = 11 ->>>>> "On File
Click to expand...
=IF(B3="NONE", "Not on File", IF(OR(A3>0, B3>0), "On File", "Not on File")

 
One last question, in the event I run into this; same logic, but just accounting for A3 = NONE as a field possibility.

Building from this formula:
=IF(B3="NONE", "Not on File", IF(OR(A3>0, B3>0), "On File", "Not on File")

IF (A3 = 0 OR A3 = NONE)AND (B3 =0 OR B3 = NONE), the result should be "NOT ON FILE"
If A3 OR B3 has value GT 0, the results should be "ON FILE"

Examples:
A3 = 0 OR NONE
B3 = 0 OR NONE
Result: "NOT IN FILE"

A3 = 0
B3 = NONE
Result: "NOT IN FILE"

A3 = 9
B3 = NONE
Result: "IN FILE"

A3 = 0
B3 = 1
Result: "IN FILE"

 
cubd8 said:
One last question, in the event I run into this; same logic, but just accounting for A3 = NONE as a field possibility.

Building from this formula:
=IF(B3="NONE", "Not on File", IF(OR(A3>0, B3>0), "On File", "Not on File")

IF (A3 = 0 OR A3 = NONE)AND (B3 =0 OR B3 = NONE), the result should be "NOT ON FILE"
If A3 OR B3 has value GT 0, the results should be "ON FILE"

Examples:
A3 = 0 OR NONE
B3 = 0 OR NONE
Result: "NOT IN FILE"

A3 = 0
B3 = NONE
Result: "NOT IN FILE"

A3 = 9
B3 = NONE
Result: "IN FILE"

A3 = 0
B3 = 1
Result: "IN FILE"
Click to expand...
You just need to nest a couple OR statements into the AND statement...

=IF(AND(OR(A3=0,A3="NONE"),OR(B3=0,B3="NONE")),"NOT IN FILE","IN FILE")

 
Hello, see the attached formulas (3 that are doing similar calculations). Currently, the formula is set to output a '1' if the OLB_END < WORKDAY is GT 2 days in the past. 

I would like to remove that logic and calculate the rest of the formula without that in the code. It is bolded below in each of the formula's.

=IF(AND([@[Setup Description]]="As-Is (No Changes)",[@[Is Automated]]=1,[@[Yearly OSL]]=1),10,(MIN(IFERROR(VLOOKUP([@Group],'Workload Look Up Table'!$Q:$R,2,FALSE),IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,IF(NOT(LEFT([@[Promotion Type]],8)="Analysis"),[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Platform Adjustment]]*[@[Order Type Adjustment]]),([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Platform Adjustment]]*[@[Order Type Adjustment]])+([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Platform Adjustment]]*[@[Order Type Adjustment]])*([@[Jobs Per Order:]]-1)*0.1))))),300)))

=IF(AND([@[Setup Description]]="As-Is (No Changes)",[@[Is Automated]]=1,[@[Yearly OSL]]=1),10,(MIN(IFERROR(VLOOKUP([@[Order Number]],'PROD Workload Look Up Table'!R:S,2,FALSE),IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,IF(NOT(LEFT([@[Promotion Type]],8)="Analysis"),([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]])),(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))+(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))*([@[Jobs Per Order:]]-1)*0.1))))),300)))

=IF(AND([@[Setup Description]]="As-Is (No Changes)",[@[Is Automated]]=1,[@[Yearly OSL]]=1),10,(MIN(IF([@CSC]<>[@Primary],0,IF(AND(NOT([@[OLB End]]:[@[OLB End]]=""),[@[OLB End]]:[@[OLB End]] < WORKDAY(TODAY(),-2)),1,IF(NOT(LEFT([@[Promotion Type]],8)="Analysis"),([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))+(IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]]))),(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]])))+(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))*([@[Job Count:]]-1)*0.1)+(IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])))+([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]]))*([@[Jobs Per Order:]]-1)*0.1)))),300)))


Let me know if you would like to see some examples.

Currently, if the OLB_END date = April 26, 2018, the formula above would result in a value of '1'.

 
Last edited by a moderator:
pbm107 said:
I don't have a question, but I came across something that is older but very clever.  An equivalent of SUMPRODUCTIF.

The site takes a while to load.
Click to expand...
I use this concept if I need to rank items that meet certain condition, kind of like a RANKIF.  (e.g  =SUMPRODUCT(--($J$7:$J$11=J9),--(K9<$K$7:$K$11))+1)

 
Walking Boot said:
I just ran across the double minus operator -- for the first time earlier this week. Something I'll have to learn more about. 
Click to expand...
This was the first time I saw the - -. I feel like I really learned most of what know about excel more than 10 years ago now and have been using the same old tricks.

 
pbm107 said:
This was the first time I saw the - -. I feel like I really learned most of what know about excel more than 10 years ago now and have been using the same old tricks.
Click to expand...
Kind of along these lines.. co-worker is trying to impress his boss and pick up some excel skills.  Asked me what are some common things he can learn and use.  I know the ones i use most often (lookups, sumifs, index, pivot tables) what are some other common beginner/intermediate level functions and functionalities that I can point out to him that you find useful?

 
Thomas The Skank Engine said:
Kind of along these lines.. co-worker is trying to impress his boss and pick up some excel skills.  Asked me what are some common things he can learn and use.  I know the ones i use most often (lookups, sumifs, index, pivot tables) what are some other common beginner/intermediate level functions and functionalities that I can point out to him that you find useful?
Click to expand...
Always use keyboard and get to know the keyboard shortcuts. The f4 key is a great time saver for me to repeat whatever action was last done. 

 
Thomas The Skank Engine said:
Thanks.  Any others?
Click to expand...
Get "fluent" with nesting formulas... So instead of having =SUM(B1:B20) in  C2 and then in col D2 having =IF(C2>100,"Too Much","Too Little") you can do =IF(SUM(B1:B20)>100,"Too Much","Too Little").  However, don't fall into the trap of thinking you have to stuff everything nested in 1 single cell when you get into some more complicated scenarios.  Sometimes it's easier to have a helper column or 2 to make things easier to understand. 

I didn't see it mentioned above, but knowing how to use the IF function is probably the most versatile function out there.  Also learning how to manipulate text via LEFT, RIGHT, MID, SEARCH, REPLACE, etc is a very good skill to have.  I'd recommend getting the most comfortable with MID since that'll do everything you want when searching through text strings.

 
FIND and LEN useful too, along with TEXT and VALUE. All string manipulators that help you template whatever data set you might need to work with.

 
so, wife working this weekend on a real estate project.  she has spreadsheet that feeds into a pivot table.  spreadsheet has multiple fields, including square footage that comes forward; however, only some sq ft comes forward and populated into the pivot table, but not all.  it leaves a blank.

any thoughts why some do and some don’t populate?

 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
986
culdeus
culdeus
L
Work Efficiency & Organization
Replies
15
Views
552
MAC_32
MAC_32
GordonGekko
What Are Harsh Truths About Real Life That You Would Prefer To Just Ignore ? (8/30)
Replies
5
Views
442
bigbottom
bigbottom

Users who are viewing this thread

Total: 3 (members: 0, guests: 3)
Top