# Using If And Formula To Bring Back Company Name

Jun 5, 2007
Name Qtr Year

Org 112007

Org 222006

Org 332006

Org 442006

Org 542007

Org 612006

I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.

The code should loop through a range of constant data (Region Name), until the Region Name is blank.

Dim OrgCell As Range

Dim OrgTargetCell As Range

Dim RegionCell As Range

Set OrgCell = Sheets("DATA Removed").Range("A2")

Set OrgTargetCell = Sheets("Overdue").Range("A3")

Set RegionCell = Sheets("DATA Removed").Range("C2")

i = 0

OrgCell.FormulaR1C1 = _

"=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)"

Do

OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value

i = i + 1

Loop Until RegionCell.Offset(i, 0).Value = ""

End Sub

View 9 Replies
ADVERTISEMENT
Jun 4, 2014

=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx

Following on from a previous post need to add to my (forums) array.

I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.

View 3 Replies
View Related
Nov 12, 2009

create an array of active printers. I edited the code to write the array to a range, then populate a data validation list with the printer names. All works great. The goal was to select a printer name from the list and run a macro to print some sheets. However, the function doesn't bring back enough of the name to do this. how to edit the function to bring back the whole name.

For example: after running the function the 1st printer returned ="\rizzoHQ-ENG-04"...when i record a macro to print to this printer it needs ""\rizzoHQ-ENG-04 on Ne05:". Here is the function unedited, I bolded the part i think is controlling the name:

View 5 Replies
View Related
Jan 19, 2013

I am running an Index, Match off of totals, what I would like to do is when the formula finds the column_index_num to move up one row and bring that value back.

For example: if I am going to sheet2 and my formula would normally pull back the information on row 7 with a column_index_num of 5 (or column E), I want to bring back the information on row 6 column E.

How do I add the variable to bring back the information one cell above?

View 9 Replies
View Related
Jul 13, 2007

I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.

Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?

Sub PopulateReport()

Application.ScreenUpdating = False

Dim MyFilter1 As String

Dim MyFilter2 As String

Dim MyFilter3 As String

MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string

MyFilter2 = CStr(Sheets("Report").Range("C4").Value)

MyFilter3 = CStr(Sheets("Report").Range("C6").Value)

Sheets("Waste").Select

Dim Rw As Long

Dim Rng As Range

Rw = Range("A65536").End(xlUp).Row

Set Rng = Range("A1:W" & Rw)

With Rng

.AutoFilter

.AutoFilter Field:=20, Criteria1:=MyFilter1

.AutoFilter Field:=2, Criteria1:=MyFilter2

.AutoFilter Field:=13, Criteria1:=MyFilter3

End With

how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!

View 9 Replies
View Related
Mar 3, 2009

I want to lookup some data and bring back the info in the last populated cell on the row, rather than column 4. I have written the below but it is bring back info from Column Index number 4.

=VLOOKUP(D4,'A2'!A:Z,4, FALSE)

View 9 Replies
View Related
Jun 25, 2014

Median Indirect: I would like to find the median in a range of cells and then bring back the 2 adjacent cells

Range...............Adj Cell 1............Adj Cell 2

1.......................L1......................L2

2.......................P3......................P4

3.......................O9......................010

So the median of the range is 2, thus we would want the formulas to bring back P3 and p4

Column 1..............Column 2

P3...........................P4

View 12 Replies
View Related
May 22, 2008

To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.

I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.

View 14 Replies
View Related
Apr 25, 2008

i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results

LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row

Application.ScreenUpdating = False

For i = 26 To LastRow

Target = Sheets("design owb").Cells(i, 10)

With Sheets("design")

For J = 2 To 54

For K = 4 To 11

If Target = .Cells(J, K) Then

Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "

End If

Next K

Next J

End With

Next i

Application.ScreenUpdating = True

View 9 Replies
View Related
Jun 28, 2009

I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:

Account NumberDateComments2343566/2/2009 $ 111.43 3453465/1/2009 $ 89.34 5676552/5/2008 $ 643.23 8078989/3/2008 $1,245.34 12543612/5/2008 $ 56.65 2343562/2/2009 $ 343.54 3482459/9/2008 $ 78.76 9345641/2/2009 $ 356.22 2343565/3/2008 $ 529.66

The idea is to enter an account number like 234356 click a button and bring back:

Account NumberDateComments2343566/2/2009 $ 111.43 2343562/2/2009 $ 343.54 2343565/3/2008 $ 529.66

I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.

View 9 Replies
View Related
Sep 4, 2013

I need a formula to check a range of parts and bring back a value of 0 if matched or the value in another cell if it doesn't match.

Part number H-200-3

Part number H-200-3-A

Part number H-200-3-B

If I enter any of the above part numbers then cell B1 returns 0 if it is a different part number then B1 would need to match cell A1.

View 2 Replies
View Related
Jul 27, 2007

I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.

I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.

Here is my formula now:

=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)

J2 is the customer name

L2 is the week

"East Data" is the spreadsheet with all of the promotions and customers.

View 9 Replies
View Related
Mar 17, 2009

I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009

$23.95 $24.15

Using excel 2007

View 3 Replies
View Related
Jun 1, 2006

I have a nested if formula that isn't working and I know there is a real simple fix.

Here is the situation: If Company A (A1) and Company B (B1) both have indices greater than 125, I want that to show that in my recommendation. For example: I used if(A1>125&B1>125,"Company A and Company B",if(A1>125,"Company A", if(B1>125,"Company B","None"))). For whatever reason, the first nested if statement isn't working correctly. what I am doing wrong with this if/and statement?

View 3 Replies
View Related
Jun 15, 2009

I'm trying to get a cell {in the attached spreadsheet 'Order Form' Sheet- Cell 'B13'}, to bring up the corresponding product description {'Products' Sheet- Column C}. For the cell that is to bring up the product description {'Order Form' Sheet- Cell 'C13'}, the following is the formula I'm trying to use, but is coming up "#N/A":

=IF(C13>1000,VLOOKUP(A13,Products!$A$2:$D$12,3),IF(C13>287,VLOOKUP(A13,Products!$A$14:$D$25,3),IF(C1 3>0,VLOOKUP(A13,Products!$A$28:$D$38,3)," "))).

I haven't attempted the "Cost/Bottle" formula yet {'Order Form' Sheet- Cell 'D13'}, but it needs to correspond the product number {'Order Form' Sheet- Cell 'A13'} and the quantity ordered {'Order Form' Sheet- Cell 'C13'}, with the cost/bottle on the that quantity found on 'Products' Sheet, Column D.

View 5 Replies
View Related
Mar 1, 2009

Is it possible to make a back-reference in a cell formula similar to regular expressions?

Example:

=IF(I15=FALSE, [H17 * INDEX(Percentages,I17+1)], IF(A17="", $1, 0))

...where [ ] indicates the actual reference and $1 indicates the reference number

View 9 Replies
View Related
Aug 11, 2012

I have a layout something like the following:

A1

A2

A3

[Code]....

Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).

Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".

Ultimately I am looking to have a matrix which contains all the distances between each location:

A1

A2

A3

B1

[Code].....

View 4 Replies
View Related
Feb 7, 2008

I have a list of companies, each company has it is own name and particular code.

I specify Cell D12 For Company Code and Cell E12 for Company name.

How to write code that write company name automaticlly in cell E12 when I write the company code in cell D12?

View 9 Replies
View Related
May 30, 2014

Here is my formula:

=IFERROR(INDEX(Stored!$D$22:$AN$795, MATCH(D11,Stored!$E$22:$E$795,0), MATCH(E10,Stored!$C$22:$AN$22,0)),0)

For some reason it keeps pulling back the value that is one cell to the right, so one column off.

What could it be.

In this example it should pull back N363 and it is pulling back O363.

View 1 Replies
View Related
Jan 17, 2012

know if there is a formula for counting back a year from a date?

Basically what I have is we operate a rolling absence/ sick year and so what happens is on a staff members return from absence/ sick we look back exactly one calendar year from the last day of absence and count the number of days off absent in that period for comparison against their paid allowance.

I have posted a mock up sheet and what I am looking for is a formula for column C. The highlighted cells are the period it would be if today was the last day of absence.

Sheet1 Â ABC1Days AbsentDateRolling2201/01/2010Â 3302/02/2010Â 4403/03/2010Â 5207/06/2010Â

6310/11/2010Â 7431/12/2010Â 8601/01/2011249202/02/2011

2410103/03/20112211307/06/20112112510/11/20112413201/01/20121914317/01/201216

View 5 Replies
View Related
Aug 26, 2009

cell AT7 is a formula which brings back a numerical value i.e. 15.

in cell AS7 i need a formula that displays the value of cell AT7, as long as that value is less than 15, if the value is 15 or higher then 0 should be displayed.

I dont know why i cant get this, seems like a simple IF statement to me, but who knows.

Once this formula is done i need to drag it down about 2500 cells and autosum to get a column total...

View 3 Replies
View Related
Nov 11, 2009

I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.

I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.

View 4 Replies
View Related
Aug 26, 2009

I was wondering whether there is an easy way to change a formula containing a named range back into it's original cell reference in VB. For example I have a cell saying =cogs2008/revenue2008 and I want to change it back into =A2/A3. Any ideas?

View 9 Replies
View Related
Mar 24, 2009

I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row.

We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months.

For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets.

In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.

Here is the formula:

=(FVSCHEDULE(1,M79:M90/100)-1)*100

View 2 Replies
View Related
Feb 16, 2014

I've got a forecast from a customer and need to summarize it with part number, quantity and date.

The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.

my output needs to be partno, date due, quantity.

View 4 Replies
View Related
Sep 19, 2013

I have a spreadsheet with thousand company names that I would like to extract and hope there is way to not do it manually. The data is organized in each row like this:

5;"Novo Nordisk";"Medicinal og biotek";"66346000";"17097000";"31499"

I would like to only keep the company name, in this case Novo Nordisk and delete the rest of the information. How do I do that ?

View 6 Replies
View Related
Jan 16, 2007

Here is the Scenario, A friend of mine who works for a Trucking company asked me this Question; This is How their company calculates His Pay for the Trips that he Makes.

@ first 100 Meter + Loading A Value of 1 Which will be Multiplied by the weight of the Load “L” and then a Flat Fee of “X’, And Off course The weight is not of any Issue for us for time being.

@ first 100 Meter + Loading A Value of 1 * X

@ Next 500 Meter a value of Max. 500-100=400 => 400/100= 4, then A Value of 4 * Y, Then if Total Traveling is 350 Meter, There we will have 1*X*L + 2.5*Y*L

And again L is not important and for time Being Could left Out.

@ Next 10 Kilometer A value of [Number of Kilometer - .5]*Z Then Max would be 9.5 * Z

@ Traveling of Over 10 and Less Than 30 Kilometer would be [Number of Kilometer – 10] * W Then a Traveling of 27 would be 17 * W

@ Over 30 Kilometer a Value of [Number of Kilometer – 30] * V

And Finally all these would be added up accordingly.

So far so good, and There is no Problem and Easily I can Use Excel To Calculate Every thing for me Just by Entering the Total travel amount and the Weigh of the Load, Using If, And Vlookup.

But Here it gets Complicated, They have these Following additional Conditions;

1: If the Traveling Road is a High way or Major Paved Roads Then all the above Values would be multiply by .77

2: If the Road is a Semi Paved Road then all the Above Values would be multiplies by .9

3; If the Road is a Dirt Road then all above would be Multiply by 1

For Example if the Traveling is 17 Kilometer and 2 kilometer is Dirt at the Beginning, and 2 Kilometer is the semi Paved at the end and the Rest are Fine Paved Road.

1*X*1+ 4*Y*1 + 1.5*Z*1 + 8*Z*(.77) + 5*W*(.77) + 2*W(.9)

View 9 Replies
View Related
Aug 14, 2014

My company has a lot of file excel. I usually use Google driver, LAN and a lot of the other ways to share file to all member in the company. But so, it is time consuming very much and not protect information in my execl file.

View 5 Replies
View Related
Mar 18, 2012

sort out the email address as per the company name using Vlookup or any other formulae other than using text to column on "Email Adress" and then doing a Vlookup from the reference table.

Reference table:

Email AddressDomainCompanyxyz@gmail.com@gmail.comGmailabc@hotmail.com@hotmail.com

HotmailAlpha@facebook.com@facebookFacebook

Result Required

Email AddressCompanyxyz@gmail.comGmailabc@hotmail.comHotmailAlpha@facebook.comFacebook

View 2 Replies
View Related
Oct 29, 2012

I need to map two lists of company names. 1st list is master list having around 1lakh company names. 2nd list have around 500 records which i need to manually match bcoz index and match functions just dont work.

What i currently do is copy a part of company name from list2, go to list1 and apply a contain filter and if any matches are found, take the closest match to list2.

This is a tedious task for 500 companies as it involves to many mouse clicks. We just need to paste a part of company name in search box and a contain filter is applied to list 1.

View 5 Replies
View Related