Posts by JF

  • Match Criteria And Return Value From Different Row

    Re: Find number That Matches Criteria And Return Value From Different Row In Same Column


    Quote from stuckwithexcel

    I have 2 rows of data and want excel to find the number of times that a number appears in the first row and then return the value of a cell in the same column but in the second row of data. I need it to repeat this until all matches in row one, and their corresponding number in row 2 have been found and then add all the results from row 2 together to give a single numerical answer.


    I have tried the 'lookup' function but this only returns the first number that matches the criteria and does not continue to find the remaining matches.


    Can you post an example of what you are trying to do with an expected output?

  • Nested IF Formula

    Re: Nested IF Formula



    Why not use a lookup table? This will allow you to adjust the commission schedules without having to go back and manually adjust the formula(s)

  • Hlookup In Different Workbooks

    Re: Hlookup In Different Workbooks



    If the target sheet is sheet 1 in book2, then in B14 enter the following text


    [Book2]Sheet1


    However, remember that the tarket workbook will need to be open or you will get a #ref! error

  • Match Values Then Add Corresponding Values

    Re: Match Values Then Add Corresponding Values



    Do you have a sample of what yoou want with the expected result?

  • Finding And Deleting Row With Match In Macro

    Re: Finding And Deleting Row With Match In Macro


    Quote from FLJohnson
    Code
    Sheets("Sheet2").Select
    Rows("4:4").Select
    Selection.Delete Shift:=xlUp
End Sub


    If you can forgive me for my laziness, could you tell me how to write this?


    If I understand correctly, if you store the value from the match in a cell, you can set a variable to that.


    Try this (edit cell referalce to sheet 1 to cell where your match is)


  • Assign Function To Msg Box Buttons

    Re: Assign Function To Msg Box Buttons


    Quote from Excel Noob


    I would like to be able to get the Msg Box to only pop up when closing the first sheet "VS Count" and only when there is a value entered into the "Used" group. Sorry I didn't notice the Names weere gone on the attachement I posted above. Don't worry about that part.(Updating on Open)


    You could have the code check the file name first...

    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
If Me.Name = "VS Count.xls" Then
   if "Used" <> "" Then
      'rest of your code here
    endif
  End If
End Sub

  • Check Mandatory Cells

    Re: Check For Empty Cells


    Quote from Dave Hawley

    Name the mandatory cell with [nr]*[/nr] then use this code in the Before Save Event.


    Dave,


    I tried doing as you said (To initally create the workbook) but it will not let me save it without completing the mandatory cells myself.


    I had to save with dataq, then reopen (disabling macros), delete the data in required cells and then save it.

  • Check For Duplicates

    Re: Cse Formula - Check For Duplicates


    My Mistake, I did not notice that you selected the entire range ahead of time so that it would automatically change the referance. Sorry.

  • Date Based on Other Dates

    Re: Date Formula Based on Other Dates


    Quote from ByTheCringe2

    JF, Yes it will go wrong if you do that, but the dates in column A are the start dates of each month. You don't change them.


    I did not change them. I Set A8 to 3 A9 to Thursday and A11 to 9/1/07


    It returns a date of 9/13/07 but the 3rd Thursday is actually 9/20/07

  • Date Based on Other Dates

    Re: Date Formula Based on Other Dates


    I noticed one flaw with the formula.


    if you set it for the 3rd Thursday of a month, and select a month where the 1st is on a Friday or Sat, then it does not calculate correctly. For example, set the date in A11 to 07/01/07, 09/01/07, or 12/01/07 and it will give you the 2nd Thursday of the Month vs the Third.


    Here is a Formula I use to calculate the 3rd Thursday:


    Code
    =IF(MONTH(($K$1-WEEKDAY($K$1,1))+6)<>MONTH($K$1),(($K$1+7-WEEKDAY($K$1+7,1))+20),($K$1-WEEKDAY($K$1,1))+20)


    K1 has the following:

    Code
    =MONTH(N1)&"/01/"&YEAR(N1)

    to calculate the first of the current month


    N1 has the

    Code
    =now()

    to calculate the current day.


    It could be adapted to the first portion of your request (getting the specific day but would need to also be modified for the second part (for 5 week month)

  • Check For Duplicates

    Re: Cse Formula - Check For Duplicates


    JBarefoot,


    One question, the formula that was provided will only check to see if the name in the first cell is duplicated. Could it be possible that g4 is one name but the other cells could be a duplicate of a different name?


    i.e
    g4 ---- h4 ------i4
    John----Bill------Bill


    If this is the case, then you would need to add conditional formatting to also look for duplicates with the other cells (H4 & I4) as the criteria as well.

  • Macro To Create Sheet For Each In List

    Re: Macro: Create Sheet For Each In List


    Why not just add another column to your master data tab for the worker that is responsible for that account/exp code.


    Then you can either use [af]*[/af] and or [pt]*[/pt].

  • Automatic Monthly Update

    Re: Automatic Monthly Update Of Main Tab Data


    Quote from impalacrazy

    The data on each tab is entered in by upper management. The sheets are broekn down into section and regions so each manager can go into his section and enter in his data for the month. I wish i would just have it in a column format but they have asked for it to be done this way.


    How about creating a user input form and have the input form transfer the information to a data page that is layout out in a database format.

  • Count Number Of Occurrences

    Re: Count Number Of Occurrences


    So if I understand correctly, you would want for example:


    POL205 used 2 times Policy Initialization/Agency Bill, DB book trns
    POL206 used 1 time Direct Bill Policy Initialization-moes GPP200 Pgm

  • Count Number Of Occurrences

    Re: Count Number Of Occurrences


    You could try an filter the list based on the Object using the [advf]*[/advf] (unique values). Copy the filtered list to a new sheet.


    The use a countif formula to count the object in the original list using the new filtered list as the criteria and then pull the place from col h. (provided that Col h has all listed places for each item. If the place in Col h only lists the place for that particular line then you would need to go about it differently).


    See also [af]*[/af]

  • Breakdown Of Currency Denominations

    Re: Currency Denominations


    Quote from ismail

    A single currency note of value one is divided into 1000 units. If one enters a value at B1 the formula will calculate numbers of note of 20, 10, 5, 1, ½ and ¼ as well as number of coins for 100, 50, 20, 10 and 1.
    I tried to calculate through a formula. But I can’t understand why excel is not showing the correct result for coin 100.
    Please help me to complete this exercise.
    Thanking you .


    Do not use the int function in the formula.


    make it


    =(b1-B10)/.1