Return Information On Same Row If Starting Value Is Correct

Feb 11, 2009

I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:

I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)

=IF(ISNA(MATCH("N06*";Sheet1!AI:AI;0));"error";INDIRECT(ADDRESS(MATCH("N06*";Sheet1!AI:AI;0)*1;1;1;T RUE;"Sheet1")))

I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.

To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.

View 3 Replies


Pop Up If Information Entered Is Correct

May 23, 2006

I have a barcode scanner I have setup with Excel to scan PC barcodes at the office.

Previously what used to happen is I would get a list of all PC's on level 6, under data validation I had an error alert setup. The source code was =$A:$A (A:A being the list of PC's). So basically If I scanned a PC that wasn't on the list a error alert would popup saying STOP - Take down PC details. This worked Fine.

I now need to use it for the opposite.

I have a list of PC's that are missing in column A, If I scan a PC on level 6 or wherever and its in that list I want the Error Alert to popup to advise me to take down the details. So the =$A:$A won't work anymore.

View 8 Replies View Related

Return Nth Character In A Cell Starting From The Left

Nov 15, 2006

How do I return the Nth Character in a cell starting from the left.

For example, cell A1 has the word "Baseball". What formula will give me the 6th character to the right, which would be "a"?

View 9 Replies View Related

Nested Vlookup Not Return Correct Value?

Feb 20, 2014

I have the following formula:

=IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)<(VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)-((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03))),'CORE Data 3 Month #2'!A3,IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)>(VLOOKUP('CORE Data 3 Month #2'!$A:$K,6,FALSE))+((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03)),'CORE Data 3 Month #2'!C3,'CORE Data 3 Month #2'!B3))

The strangest thing is happen. The part of formula underlined and the part bolded are identifical. For some reason, excel is unable to find the value in the underlined portion but is able to find the value in the bolded portion. As a result, I'm getting a #N/A result. how this can be corrected?

View 3 Replies View Related

Return Value When Another Value Is Present And If Value Is In Correct Date And Time

Apr 11, 2012

I have done a VBA course but am struggling to apply it to my situation. What I want to do is on one spreadsheet, I have a column with "shift, date, grade 1, grade 3, quarantine" in it, and in the cells next to it I want an operator to enter either "day" or "night" next to shift, and the date. Next to the others I want the total weight of the appropriate product made in that time. The spreadsheet I will be feeding off is quite big with a lot of information in it. Down the left hand side is the roll number (we make rollstock). Further along on that same row will be the date, the weight, and a grading (putting it into grade 1, 3, quarantine etc). Our shifts run from 7am to 6:59pm, and 7pm to 6:59am. Which shift the roll comes under is determined by it's end time (also in that row). So basically, I need VBA to say sum up all of column [weights] if column [date] = [date cell in working spreadsheet] and if working spreadsheet says day then column [roll end time] =7:00-18:59 and if working spreadsheet says night then column [roll end time] =19:00-6:59 and also if column [grade] = [grade cell in working spreadsheet] ....

Oh, also on the feed spreadsheet i have a column that i put in a little code date, "AP04" for 4th of april kind of thing, rolls being made before 6:59am on the 5th of april being put into the 5th april as they need to be counted under 4th april as they are 4th april night shift (even though the date will stay 5th april)...

View 3 Replies View Related

HLookup - Return Correct Number Of Rows Downwards In Table?

Feb 8, 2013

=HLOOKUP(G8,'Headline Assumptions'!$C$32:$F$37,E8,FALSE)

Above formula, in cell E8 is the number 3, however excel does not recognise this and returns 1. is there a way i can get E8 to see that it is a 3 in that cell and return the correct number of rows downwards in my table?

View 1 Replies View Related

Basic Cell Referencing - Return Correct Names And Values On Another Sheet?

May 13, 2013

Here's the data table being referenced

Week Ending


[Code] ........

Now on another sheet, I want to return the top two 'Name's and their values like below:


There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?

I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..

View 1 Replies View Related

Index And Match Formula: Return The Correct Serial Number Based On Both The Matching

Jun 24, 2009

I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.

View 4 Replies View Related

Return More Information From The Row

Feb 12, 2007

I have an existing table with times on the left most colum and diferent areas and events on the next 12 meaning a count of events and outcomes for 6 areas per hour. (X=QTY.throughputs; Y=QTY. Errors)
This would be the headers.

Time, XY, XY, XY, XY, XY, XY.

I need a formula to return the max "X" but also return the time it was recorded.

View 10 Replies View Related

MAX Return Cell Information

Feb 8, 2006

I have a table in the format:
01/01/06 1 3 7 8
02/01/06 2 5 4 4
03/01/06 3 9 5 7

I want a formula to analyse the entire table (Which can change) and to
return me who has achieved the Max value and on which date.

The answer here should be MR B on the 03/01/06.

View 11 Replies View Related

Countif Formula: Added The Correct Amount In The Correct Cells

Feb 15, 2010

I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.

View 4 Replies View Related

Check Cells Are Correct Format And Contain Correct Data

Jan 26, 2014

We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.

I envisage something like an output report:

149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.

View 4 Replies View Related

Sumproduct- Counts The Correct Amount But Not With The Correct Dates

Jul 3, 2008

I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.

I need to see how many items processed for each set per day.


The problem is that it counts the correct amount but not with the correct
The formula that I use is:

View 14 Replies View Related

Return Information From Column Respectively Into Another Worksheet

Mar 9, 2009

If column F, G, H says PAP then I want it to return information from column A, B, C, D, E respectively into another worksheet. I have attached a sample

View 6 Replies View Related

Search Engine Will Return All The Information

Apr 29, 2007

i want is to create a search engine in excel that allows users to input the clients name and the search will return all the info on the client such as the issue and their policy number.

Is this even possible? Using Access is not an option. I know I can use Access but most of my users dont know how to use Access and I want to make things as simple as possible.

Thanks so much in advance who ever succeeds at provding an answer u're a genius cuz I have no clue where to start lol.

View 9 Replies View Related

Return Related Information From Another Workbook

Nov 4, 2009

we have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product). So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.

My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly. Here is the code so far:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
If Target.Cells.Count > 1 Then Goto exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then Goto exitHandler
Application.EnableEvents = False
Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0)
End If...........................

View 3 Replies View Related

Textbox Return Information From Cell `

Jun 26, 2007

I wos wondering if anyone knows how to return the information that is in the cell to a textbox. Ex. i have text in b5 and when i activate my userform and enter in text it erases my text from befor.

View 5 Replies View Related

Lookup & Return Information From Another Worksheet

Nov 19, 2007

In one worksheet there is a code for every customer and their details e.g. Name, medication they are on, what they are alergic to etc then in another new worksheet can we just type in that code and their individual information comes up automatically?

View 4 Replies View Related

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 View Related

Search One Sheet For Updated Information And Return That Data To Another Sheet

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

Userform Data (allows Input Of Information And Then Deposits The Information On A Specific Sheet)

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

Write Information Into New Row In Different Sheet But Keeps Overwriting Old Information

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:

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

Sum Starting From A Row?

Oct 26, 2011

I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).

I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.

1 150
2 200
3 250 50
4 120
5 260
6 157

View 2 Replies View Related

How To Get The Starting And End Shift

Apr 4, 2014

I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.

View 5 Replies View Related

Bar Graph Not Starting At Zero?

Jun 5, 2014

I'm working on a stacked bar graph based on the below info...




I'm trying to adjust the starting point so that it does not start at 0.

For example...

N1 - $21.00 - $38.00
N2 - $26.00 - $45.00
N3 - $30.00 - $53.00

is there a way to do this?

View 1 Replies View Related

Starting With UserForms

Jan 24, 2008

1) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?

2) How do I get the options from the userform to the worksheet?

3) How do I put an input box on a user form?

View 9 Replies View Related

Sums Starting Over After Zero

Feb 25, 2009

I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.

View 9 Replies View Related

Hot Key Not Starting Macro

Oct 4, 2006

I have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.

Sub PO_Flash_Report_Detail() ....

View 3 Replies View Related

Selecting A Starting Line

Feb 10, 2010

I have a code that print previews a certain amount of rows that I define. This basically says print preview the first 10 lines down.

Can the code be modified so that i can select a start cell. So instead of always starting at A1 I could select say start at G1?

View 14 Replies View Related

Sum Subsequent Numbers Starting From 1

Aug 31, 2012

Is there a built-in function in Excel to sum subsequent numbers, starting from 1?

If I give for example 5 as input value to the function, it should return 1+2+3+4+5 = 15.

If I give 3 -> it should return 1+2+3 = 6 and so on.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved