Linking Partial Data From One Cell If Data In Another Cell Meets Requirements

Jan 6, 2009

The merged Cell B6:G6 will receive a ten-digit number followed by a dash and then one or more numbers. (For example: 1234567890-123)

Cell B15 will then receive data shortly afterwards. I already have a validation macro for this cell which allows either 'I' or 'I I I'.

Upon exiting Cell B15, merged Cell B16:H16 needs a macro which will check Cell B15 and if it contains 'I', Cell B16:H16 will display the data from the ten-digit number entered in Cell B6:G6 minus the first five digits. (For example: 67890-123)

Now the data in Cell B16:H16 can only be somewhat editable hereafter. It can be erased or replaced with numbers in smaller or greater digit combinations than five before the dash (i.e. 67890-123 can be replaced with 123456-7), and digits can be added after the whole group (i.e. 67890-123 & SEE DWG) without any error messages. But if any five-digit number with a dash and some numbers exist in Cell B16:H16, they must correspond with the number in Cell B6:G6 minus the first five digits.

However, if Cell B15 ever receives a 'I I I' afterwards, all data in Cell B16:H16 must be erased. Cell B16:H16 can never contain data if Cell B15 contains 'I I I'.

Also, if the data in Cell B6:G6 changes later on, the corresponding digits in Cell B16:H16 must change as well, even if there are digits after the whole group.

So here is an example of what a good macro would do for me: ...

View 14 Replies


Macro To Copy Data From One Cell To Another If Adjacent Cell Meets Certain Requirements?

Feb 19, 2014

Basically I have three columns in a work Sheet F, G, & H. F is empty, G contains text and column H has both text and numbers.

I want to be able to automatically copy the value from Cell H to Cell F if cell G contains the word cost.

I would also like to delete all rows where Column G & H contain two dashes -

View 7 Replies View Related

Linking Partial Cell Data

Jan 5, 2009

I have two cells on Sheet 1 - Cell A1 and Cell A2.

Cell A1 is the first cell that will receive either a number, or nothing. When it receives a number, the number will always contain five digits. (For example: '23456'.)

The contents of Cell A2 depend on the contents of Cell A1 - If Cell A1 contains a five digit number, then Cell A2 will display that same five-digit number with '-1' following it. (In our case: '23456-1'.) If Cell A1 contains no number, then Cell A2 will just be another ordinary cell allowing the user to enter whatever he/she desires.

The extension in Cell A2 should remain editable at all times - if the user wishes to change the '-1' to a '-2', for example, he/she should be able to do so without any error messages appearing. However, the user must not be able to change any of the first five digits in Cell A2 as long as Cell A1 contains data. A message should appear stating that changes to the first five digits should be performed in Cell A1 - which would then change Cell A2 accordingly.

Also, if the contents of Cell A1 are erased for any reason, Cell A2 should keep the five digit number, but lose whatever '-x' extension it contained.

Can this be done using Data Validation?

View 14 Replies View Related

Linking Partial Data - Macro

Nov 12, 2009

I have had much assistance with this complex (I think it's complex) macro in the past, however, there are still a few bugs that need to be worked out in order for it to be completely efficient. Any help would be really appreciated, and I'll do my best to explain what's going on at this point:

I have a worksheet called the 'Data Entry Page'. You will find it attached. Here are the requirements:

The PO Number cell will only allow ten digits with a dash, and will receive values such as 1200012345-1.

The Part Number cell will receive a value which will usually be six digits long with a dash, and will receive a value such as 654321-1.

The Identification Type cell will receive at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):


or (some combination examples):
IA & I I I
I I & I I IA

View 13 Replies View Related

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

Apr 16, 2013

I am using Excel 2010. I am a novice user.

I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.


[Code] .........

Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.


[Code] ......

So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.


The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".

Similarly folder paths names can contain "_" so can't split string on this either.

As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.

I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:

Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function

If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?

Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g

26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d

So my table would show the name "data_out_reg" and the range of values 8-32

View 1 Replies View Related

Extracting Data Based On Partial Data In A Cell

Jan 23, 2013

I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.

Example data extract requirement.xlsx‎

View 5 Replies View Related

Retrieve Partial Cell Data

Jul 31, 2014

Is there a way I could achieve copying cell's content minus some characters?

For example in the cell A1 would be: "two apples"

I would put into the B1 cell something like =A1(-4charactersincludingspace) - the result in B1 should be "apples"

Is it understandable what I mean?

View 1 Replies View Related

Copy Data To Multiple Sheets (Specific Requirements)

Jun 30, 2006

I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, the name on this sheet will change daily, so you cannot use a "named" sheet in your macro. The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The data comes in such as:

MZ03948 134 126.23
PZ34533 128 12.28
IM04985 630 198.50
Z109384 40 35.65
2130494 76 12.28
2236326 12 45.63
5123132 12 12.23

Based on the FIRST TWO characters in the first column, I need to copy the data to the specific sheet. I would assume a simple LEFT() function would work for this. Here are the requirments for each sheet:

Esky - "MZ" or "51"
Indy - "IZ" or "Z" or "21"
Gfld - "PZ" or "22"
Other - Anything else

View 9 Replies View Related

Refernceing Cells: Formula That Looks At A Cell On A Front Sheet, And Then Returns The Contents Of That Cell As The Result If It Meets The Criteria

Apr 5, 2009

I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")

This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?

View 2 Replies View Related

Matching Partial Data With Full Data

Jul 18, 2006

I need to match up the columns of 1 spreadsheet with those of another. I have a partial address that only lists city and state in one workbook. I need to match these partial addresses up with the master list that contains full addresses. It seems like it should be an easy thing to do, but I can't figure it out.

View 9 Replies View Related

Color Cell If Corresponding Cell Meets Condition

Sep 12, 2007

I am currently doing a spreadsheet and require cells in column f (which have monetry values entered) to colour in different colours depending on whether there is a yes or no value in column e.

View 2 Replies View Related

Excel 2007 :: Copying Data And Graphs De-linking Source Data?

Dec 27, 2012

I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.

I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.

I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.

Excel 2007 / Windows 7.

View 3 Replies View Related

SUMIF Sum All Data That Meets 1 Or 2

Nov 28, 2005

Suppose I have some data with a code for each data point:
1 100
1 200
1 300
2 400
2 500
3 600

The first column is the code and the second is the data. I can use a SUMIF statement to sum all the data that have a certain code (like 1). What if I wanted to sum all data that meets one of a number of codes? Suppose I wanted to sum all data that meets 1 or 2. I know I can do this with 2 separate SUMIFs, but I was wondering if there was a way to do it with one.

View 9 Replies View Related

Summing Data That Meets Two Conditions

Jun 1, 2009

I want to sum certain data, which meets two conditions.

My data set contains three columns and a lot of rows. The columns are the following:

I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)

With "SUMIF" I can only include one condition.

View 4 Replies View Related

Returning Text If Data Meets Certain Criteria

Apr 24, 2014

IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).

View 1 Replies View Related

Array Formula: Add Data Which Meets Certain Conditions

Jan 1, 2009

I'm working with wookbooks used company wide and I cannot add any helper columns which would solve the problem. I need to add data which meets certain conditions see attached workbook for a sample.

View 5 Replies View Related

Pulling Data From Multiple Columns If Meets Criteria

Aug 13, 2012

Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.

Attached sample file : example.xls

View 3 Replies View Related

Finding Data In Column That Meets Multiple Criteria

Jun 16, 2014

Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.






[Code] ..........

View 9 Replies View Related

How To Make A Macro Take Action When Data Meets Certain Criteria

Feb 6, 2010

I am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.

If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)

Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?

View 9 Replies View Related

Formula To Ignore Blank Cells And Copy Data That Meets Criteria?

Apr 27, 2014

I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.

In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.

View 2 Replies View Related

Countif (check The Data For The Following Conditions, If It Meets The Crirteria Then Place A 1 In Columns)

Aug 22, 2009

the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.

Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)

What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.

Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D

Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.

View 5 Replies View Related

Coying A Cell Value If It Meets A Criteria

May 12, 2009

i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...

The first step i have taken are:

1) if the 2 date ranges are the same to say 'true' if not 'false'

I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date

View 9 Replies View Related

Copy Partial Text Within A Cell To A New Cell

Oct 10, 2008

I have a database that I need to extract information from. Column "D" has a string that runs from 25 to 50 characters long, and I need to pull specific information out and need it to be inserted into a new column for each row of my database. The data that I need to pull from column "D" is always located between the first occurance of "=" and the first occurance of "&"

View 2 Replies View Related

How To Validate If Data In A Cell Conforms To Data Validation Rules In The Cell

Oct 14, 2009

I have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number.

I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically.

To keep it simple, I'm only supporting pasting a single cell at a time.

Now my problem is this:
Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List.

The following post just suggests disabling paste whenever data validation is present:

But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.

View 9 Replies View Related

Count Single Cell If Meets Criteria?

Apr 12, 2012

I am trying to come up with a simple formula to count a single cell if it contains either a 2 or 3.

The cell can contain numbers ranging from 0-8.

Even better would be if I could some how evaluate single cells based on the contents and then count the number of cells where the criteria for contents is true. The problem is the criteria differs from cell to cell (i.e. D2 could = 2 or 3, but E2 needs to be counted only if it contains a 4 or 5).

I know COUNTIFS only evaluate a range but it would be perfect if I could somehow get it to work for single cells. COUNTIFS(D2,2,D2,3,E2,3,E2,4,F2,2)

View 3 Replies View Related

Delete Formula If Cell Value Meets Criteria?

Feb 18, 2014

I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):

Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"

Is there a way I can amend it so it only updates those cells which currently have no data?

At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.

View 3 Replies View Related

Formula To Return Cell That First Meets Certain Criteria?

Mar 5, 2014

Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.

In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?

View 3 Replies View Related

Color Cell If First Character Meets Condition

Aug 4, 2007

I currently have a report that I have code on that will change the color of a cell based on whats in the cell.( Code posted below) However I also need to look at $J and if the first letter of the value in that column is a "z" then I would like to change that whole row to icolor 13 (purple).

For example J14 might be "Z-Thomas". if it does I would like that row 14 to change to purple.

Also I would prefer that the solution be in VB, so that we do not have to do something in VB with the fix below AND do something in Conditional Formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
Case "?"
icolor = 6

View 9 Replies View Related

Copy/Move Row When Cell Meets Condition

Sep 28, 2007

I'm setting up a spreadsheet for work and i have a question. I'm not familiar with the formulas. If i want to set up a formula to where if i type "complete" in the cell it will move the entire row over to the next sheet.

View 2 Replies View Related

Show Blank If Another Cell Meets Condition

Oct 19, 2007

I have 5 cells on a sheet. When one has an X in it the other 4 go blank. It so I can keep track of who has ownership of a job.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved