# 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

You're quite welcome. And welcome to the forum

• ## 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

looks right.

• ## 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
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.

• ## Breakdown Of Currency Denominations

Re: Breakdown Of Currency Denominations

Ismail,

Did you try and test the other formula I posted. It worked for the two examples you had (188.7 & 188.166)

• ## Breakdown Of Currency Denominations

Re: Currency Denominations

Try the following and see if it works:

=VALUE(INT(TEXT(((b1-B10)/0.1),"#.##")))

• ## 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).

• ## 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.