Formula: Find The “best” Contact Information And Return The Result To The Merge Sheet
Feb 17, 2009
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
View 2 Replies
ADVERTISEMENT
Jul 1, 2013
I am creating a spreadsheet to track customers contact information and purchase history.
A B C D
1 First Name Last Name Phone Number Etc.
2 Mike Jones
3
I wanted to know if theres a way to click on a customer (say Mike Jones) and be able to enter and view purchase history information relating to that customer in either a drop down box or different sheet?
ex.
Mike Jones
Date Item Spent Last Contacted
4/24 Toy $500 4/31
5/16 Movie $15 5/20
View 3 Replies
View Related
May 7, 2007
I have two workbooks Raw File and Lookup_Test, I want to find the value of Column A from Raw File, in Column A of Lookup_Test, if the value if found then i want the value in Column D from Lookup_Test, to be copied to the Column B of Raw File .
Well its like using vlookup , however i am trying to avoid using Vlookup and find any other way of doing the same.
View 9 Replies
View Related
May 4, 2013
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5
and
Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
View 3 Replies
View Related
May 14, 2009
I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
View 14 Replies
View Related
Mar 3, 2007
Any other day but today (I've been starring at numbers too long) I could do this.
I need a simple formula that will return:
a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if
View 9 Replies
View Related
Nov 6, 2006
When i have a formula standing at A3 fore example =A1-A2
and when i fill in a number like 100 at A1
And i fill in a number like 150 at A2 then at A3 where i had make my formula in there stands -50
Now i want the -50 not to disapear but instead there must be stand a 0(zero)
What is the formula i can use
i dont mind to put in at another cel like A4 for example
View 9 Replies
View Related
Oct 15, 2008
I have a workbook which I use for invoicing customers. Sheet one is the inovice template. Sheet two is the contact details of all my clients (i.e. column headings are Name, Address 1, Address 2, Town/City etc.).
In the invoice sheet I would like to be able to select the name of the client from a dropdown so that it automatically fills in the address into the other cells of the invoice.
View 3 Replies
View Related
Nov 7, 2013
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
View 6 Replies
View Related
Sep 27, 2006
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
View 9 Replies
View Related
Aug 24, 2006
Dim myDynRange As Range
Dim myLastRow As Integer
Range("H:H").Select
myLastRow = Range("H:H"). CurrentRegion.Rows.Count
myFirstCell = Cells(Counter, 8).Address
myLastCell = Cells(myLastRow, 8).Address
myDynRange = myFirstCell & myLastCell
im having a problem with the last line
myDynRange = myFirstCell & myLastCell
my eventual aim is to do an average on this column with a line like
ActiveCell.Offset(Counter, 1).Value = Application.WorksheetFunction.average(myDynRange)
the reason why my range declaration is so strage is because i was trying to change the dimensions of the range with an offset function because i had many ranges to average but the offset function didnt want to work. this way goes like this....................
View 2 Replies
View Related
Mar 8, 2007
I have a excel file which shows some information about mobile numbers but i don't know from where that excel file get that information one formula have some reference of sheet2 but there is no sheet2 in excel file. how this excel file get information?
download excel file
http://www.geocities.com/mirag2001/celldecoder.zip
View 2 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
Feb 23, 2010
I have a report that the name and address was put into one cell. I need to put this into numerous columns so I can do a mail merge.
View 9 Replies
View Related
Sep 27, 2011
I have a query on retrieving the result from a set of range from other sheet.
Query: In the Sheet1 Column B has to take first two words from the
Column A & lookup the matching results from Sheet 2.
(The search range should be the first two words of each cell)
Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B
Example: The Expected result should be as mentioned below. (Blue in color should be the results)
Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5
Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A
Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14
This is my query
View 7 Replies
View Related
Feb 11, 2010
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
View 5 Replies
View Related
Jun 3, 2014
I have a table with customers and their addresses (sheet2) and I need to find their GPS locations in sheet1.
I guess the logic would need to be something like this:
find CityName and StreetName in sheet1 where StreetNo is between minStreetNo and maxStreetNo return GPS X and GPS Y
with the formula or VBA code?
Example.xlsx
View 2 Replies
View Related
Apr 25, 2014
I have excel result sheet which contains students information. i.e. name, subjects and their corresponding marks, grade, percentage etc. So from that sheet I want to print result cards for each students separately from the data (result sheet).
View 4 Replies
View Related
Jun 11, 2014
In the attached sample work book Col E has text that I want to check if it is also in Col G and return Yes or No into Col F
View 4 Replies
View Related
Sep 30, 2009
I have a column with 11 different numbering schemes that go from 736466.01 through 736466.11 In the group from 736466.01 through 736466.07 I need to have the adjacent column look to those cells and if it finds 736466.01-.07 return "CM" if it is 736466.08-.11 I need it to return "TC". I created the formula below but it doesn't like something I have done unless I change the format of the 736466.xx to text. Anything other than 736466.01-.11 should return a "null" or "void" Can you help me improve this formula, or show me a better way of creating it?
=LOOKUP(E2,{"736466.01","736466.02","736466.03","736466.04","736466.05","736466.06","736466.07","736 466.08","736466.09","736466.10","736466.11"},{"CM","CM","CM","CM","CM","CM","CM","TC","TC","TC","TC" })
View 4 Replies
View Related
Apr 6, 2013
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
View 3 Replies
View Related
Jun 10, 2014
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
View 3 Replies
View Related
Mar 24, 2014
Column A:
pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....
I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:
Column A: Column B:
pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white
I have different product feeds (csv) and i will like to search for colors in the product names.
View 2 Replies
View Related
Apr 4, 2013
I need a formula to add two (2) cells (one cell in sheet 1 and the second cell in sheet 2) and return a value of the two (2) cells in sheet 3. I would like the formula to return a zero (0) value instead of #VALUE! ERROR displayed in sheet 3 if a cell in sheet 1 has zero (0) value and the second cell in sheet 2 is blank. For example: sheet 1 D2 (0) + sheet 2 D2 (blank cell) = zero (0) instead of #VALUE! ERROR
In addition, if cells B11:B12 are blank in both sheet 1 and 2, I would like the formula to display blank in sheet 3 for cell C11:C15 instead of displaying #VALUE! ERROR.
Here is the formula I have: =IF(OR(ISBLANK(Sheet1!D2),ISBLANK(Sheet2!D2)),"",Sheet1!D2+Sheet2!D2)
Note: I have attached a sample data set for better conceptualization of the problem. Sample_Formula.xls
View 2 Replies
View Related
Oct 18, 2013
I have two spreadsheets.
spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
View 6 Replies
View Related
Apr 5, 2008
I've created a userform that has one ComboBox (ComboBox1) and two text fields. I am trying to get the userform to return information to my worksheet in the same row as the name that is displayed in the ComboBox. This is my VBA code.
Private Sub Cmdpayment_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Sheet4
iRow = Cells. Find(What:=Me.ComboBox1.Value, After:=C5, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ws.Cells(iRow, 12).Value = Me.txtpdate.Value
ws.Cells(iRow, 13).Value = Me.txtpayment.Value
Me.txtpdate.Value = ""
Me.txtpayment.Value = ""
End Sub
View 8 Replies
View Related
Sep 6, 2013
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies
View Related
Oct 26, 2009
I have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2.
Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" .
sheet1
DATE ABC DEF
2007-04-01 £55.60 £61.13
2007-04-02 £56.33 £60.27
2007-04-03 £52.14 £62.58
2007-04-04 £51.69 £61.28
sheet2
CUSTOMER DATE RATE
DEF 2007-04-04 This should be £61.28
ABC 2007-04-02 This should be £56.33
View 3 Replies
View Related
Jul 7, 2014
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
View 3 Replies
View Related
Sep 3, 2006
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies
View Related
