Show Number On Sheet1 From Cell On Sheet2 If Cell Next To Cell Containing Number Filled

Aug 13, 2013

SAMPLE FILE "error_finder.xlsx (36.5 KB)" attached...

The VP of our company needs me to create a spreadsheet.

For this s/s I need the following:

Imagine 2 sheets in Excel...

sheet1
sheet2

sheet2 has sequential numbers in column1 starting in row2 with the number 1 until wherever..., so cell A2 has number 1, cell A3 has number 2, etc...

Manually, a user has to fill in certain text next to a number.

This for example would look something like this:

_|A| B
-|-|-----------------------------------|
1| | Error-description
-|-|-----------------------------------|
2|1| Whatever the 1st error would be...|
-|-|-----------------------------------|
3|2| Whatever the 2nd error would be...|
-|-|-----------------------------------|
4|3| Whatever the 3rd error would be...|
-|-|-----------------------------------|
5|4|
-|-|-----------------------------------|
6|5|
-|-|-----------------------------------|
and so on...

All this would be on sheet2

In a cell on sheet1 I now need the number from column A displayed, that has the latest entry in column B.

In the example above this would have to be the number 3 in cell A4, because right next to it (in cell B4) is the last entry "Whatever the third error would be..."

Addition: There are several sheets, each sheet stands for one error listed on sheet1 in column A

sheet1 for example would look like this:

_|____A____|_B_|_C_|_D_|
-|---------|---|---|---|
1|_________|833|933|934|
-|---------|---|---|---|
2| error 1 |___|___|___|
-|---------|---|---|---|
3| error 2 |___|___|___|
-|---------|---|---|---|
4| error 3 |___|___| 3 |
-|---------|---|---|---|

In this example, because "whatever error" (cell B2 to B4 on sheet2) was found on press 934 was the 3rd error (cell A4), the number 3 (cell A4) would have to be populated on sheet1 in cell D4.

View 9 Replies


ADVERTISEMENT

Cell Value Matches Value From Sheet1 - Compare Number From Cell2 And Show Difference?

Apr 26, 2014

Sheet1 is Main Sheet which never will be amended and these numbers will be a source for pulling mathematical functions

See attached :
Sheet1.png

Don't be bothered about cell colors etc. They are for my reference

Sheet2

Will contain same Problem Ticket numbers but different (or same) values with number of 'Linked Incidents'
See screenshot
Sheet2.png

Based on Problem ID 10248
Main sheet shows 92 Linked Incidents
Value on Sheet two brings 93

If A5 Sheet2 value can be found in ColumnA Sheet1, look for a number in ColumnC, same ROW and compare it to C7 in Sheet2 ?

Bring up difference value, which in this case would be one.

View 1 Replies View Related

Copy / Insert Row In Sheet3 If Cell Value In Sheet2 Matches Cell Value In Sheet1

Sep 17, 2013

I am working a project where I need to copy/insert a Row of data onto Sheet 3 if values from Sheet2 are located on Sheet1. Here is my code so far:

Option Explicit
Sub move_rows()
Dim ws As Worksheet
Dim ws1 As Worksheet

[Code].....

View 3 Replies View Related

Multiply From Data In Cell On Sheet2 From Cell On Sheet1

Sep 18, 2012

I have a value in a cell in Sheet2 (in this case its price per ft), on sheet1 is where I would input the number of feet. So can I multiply from data in cell on Sheet2 from cell on Sheet1and if so....

View 6 Replies View Related

Copy Data From Cell In Last Row Sheet1 To Another Cell Sheet2

May 20, 2014

I need a macro which read out the last (in this file Pos. 4) or a user seleted Position (Position 2 or 1). The macro should read out the cells in this row and put the data into the defined cells in Sheet2.

in this case:

Data from B4[Sheet1] copy to B5[Sheet2]
Data from C4[Sheet1] copy to C7[Sheet2]
Data from D4[Sheet1] copy to E6[Sheet2]

The cells in Sheet2 are the same every time. Only the data (the row) in Sheet1 does change. I add more Position every week in Sheet1.

View 14 Replies View Related

Copy Data From Sheet1 Cell To Sheet2 Cell

Feb 4, 2014

I have a workbook with sheet1 and sheet2. Each sheet I am only dealing with columns A-D. The data in column A in both sheets is the unique identifier. The below macro I am using will take sheet1 (ColumnA) and compare to sheet2 (ColumnA). If the identifies are the same in both cells it will highlight it green.

What I need to add to the below macro is the ability to add a cell copy over. So sheet1 being my master, if sheet1 and sheet2 have a equal identifier in column A, the data from sheet1 B,C,D for that row will be copied over to sheet2 of its equal identifier sheet2 B,C,D.

I have attached a sample : Sample Copy Over.xlsx‎

View 4 Replies View Related

Hide Columns On Sheet2 Based On Cell On Sheet1?

Jan 25, 2010

This is what I would like to do if possible: If E9 on Sheet1 is 1, then hide columns F-L on Sheet2.

View 4 Replies View Related

VB To Copy Cell Value In Sheet1 To Cells In Sheet2 If Criterion Met

Feb 27, 2014

This following is just an example:

In sheet1 I have 4 columns; Name, Age, *** and Job.

Sheet2 has three columns; Name, Age and Job.

What I need is VB that will check the *** column from Sheet1 and when it is Male, the Name, Age and Job fields from that row are copied into the cells in Sheet2.

View 10 Replies View Related

How Copy The Data From More Cell In Sheet1 To A Row On Sheet2 (others Sheet)

May 19, 2009

I want entry the data with different row and different cells, so when I click the button, the data on sheet1 is clear and copy to empty row on sheet2 and automatic create a border and automatic insert new row, so we can entry again and next. And I attached my sample Excel File with the description too.

View 10 Replies View Related

Changing Color Of Object In Sheet1 From A Cell In Sheet2 Using VBA

Sep 14, 2012

I have an Oval (Oval 1) in Sheet1, and I'd like to change it's color from cell A1 in Sheet2 using VBA.

The colors are: Red- (255, 0, 0)
Yellow- (255, 255, 109)
Green- (41, 247, 46)
and Grey- (127, 127, 127)

I'd like these colors to Fill the Oval object. I'm not too worried about the line color at this point.

View 6 Replies View Related

Excel 2010 :: How To Populate Sheet2 From Sheet1 After Finding Matching Word On Sheet2 From List In Sheet1

Oct 4, 2013

I have 2 Worksheets in an Excel 2010 Workbook -

Sheet1
Column1: contains the word "dog"
Column2: contains the word "bark"

Sheet2
Column1: contains the sentence "I like dogs a lot."
Column2: is blank

What I need to do is search Sheet2/Column1 for the presence of "dog" and if it's present, populate the word "bark" in Sheet2/Column2 from Sheet1/Column2.

How can I do this?

View 4 Replies View Related

Copy Selected Cells From Sheet1 And Paste Them In Sheet2 Based On A Cell Value?

Dec 27, 2012

have two worksheets, "Entry form" and "Database" in my workbook. I am trying to put together a macro button to find the cell value D5(Entry form) in the column A:A (database), if found, copy selected cells (B5:D5,B7,B9) from entry form and paste in the adjacent cells of the row with the value in the database sheet.

VB:
'Match value D3 and replace data
Dim sht As Worksheet, outsht As Worksheet, r As Long
Dim rfoundCell As Range

[Code].....

View 9 Replies View Related

VBA Code To Copy Cell Contents In Column1 Sheet1 To Row1 Sheet2

Nov 27, 2013

I have data beginning in cell A6 and continues through cell A13 for this instance. I want data in cell A6 on sheet 1 to be copied to cell B1 in sheet 2; data in cell A7 sheet 1 to be copid to cell C1 sheet2 and so on till all of the data in the continuous range beginning in cell A6 of sheet 1 has been copied to row 1 beginning in cell B1 of sheet2. This seams relatively easy but below is my failed attempt at this.

Code:
Sheets("Data Request").Select
Dim DATAREQUEST As Long, TYPESRws As Range
TYPES = WorksheetFunction.Max(Range("A" & Rows.Count).End(xlUp).Row)
DATAREQUEST = Range("A6", "A" & TYPES).Rows.Count
Sheets("Data Retrieval").Select
For i = Range("B1", "1" & DATAREQUEST).Columns.Count To 1 Step -1
Sheets("Data Retrieval").Select
[B1] = i
ActiveCell.Offset(1, 0).Select
Next i

View 7 Replies View Related

Cell To Show Number Based On Sum Of Another Cell

Nov 15, 2013

I want a cell to show a number based on a sum of another cell i.e.

50 shows 1
51 shows 2
101 shows 3

View 5 Replies View Related

In Other Cell Show After 4th Number

Feb 12, 2014

I have e.g.

1000, Amerika (USA)
2000, Germany (DEU)
3000, Spain (ESP)

How can i put in another cell with formula:
Amerika (USA)
Germany (DEU)
Spain (ESP)

View 6 Replies View Related

If Cell Has Number Then Equal, Else Show Nothing

Sep 20, 2007

If square A1 says deposit account and c1 has a figure in it, i want g1 to repeat the figure,but if a2,a3 etc says something other than deposit account i want g2,g3 to remain blank.What is the formula for square g1etc?

View 9 Replies View Related

Format Cell To Show % As The Number Type-in

May 1, 2014

(3) examples when I type a number to a cell:
If I type a number "1", I want that cell to show 1.000% but not 100.000%.
If I type a number ".2", I want that cell to show 0.200% but not 20.000%.
If I type a number "25.5", I want that cell to show 0.255% but not 2550.000%.

I play around with the below custom format cells but they do not work.
_0.01*#.000%
_0.01*#,.000%
0.01*#,.000%

I could use two cells one for number, other cell has a % mark. But I rather to format a single cell if it is possible.

View 4 Replies View Related

Show Only The Number Of A Cell Containing Text And Numbers

Nov 23, 2009

Lets say cells A1 to A5 contain these lines of information:

Till 174 (T0215) - till keeps turning itself off.
Till 245 - stuck on windows screen
116 - keyboard is unresponsive
Berkel Scale is constantly beeping
ped not reading cards Till 156

How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.

View 5 Replies View Related

Finding Duplicates (cell At The Top Show Row Number Of Duplicate)?

Mar 3, 2008

i have duplicate cell entries occuring. I have a column of about 8000 entries (Column B) and would like to have a cell at the top of my spreadsheet that displays where the first duplicate resides (Row No. will suffice).

At present i have a conditional format on dupllicates, but is is a big task to scroll down through all the data looking for them.

View 9 Replies View Related

Multiply Every Integer Less Than The Number In Cell A1 And Greater Than Zero By The Number Cell In A2..

Feb 5, 2009

Cell A1 = 3
Cell A2 = 4

Is there a formula to calculate
(3*4)+(2*4)+(1*4)

I need to multiply every integer less than the number in cell A1 and greater than zero by the number cell in A2. I was thinking factorial, but that's not it... Can't remember from my math days.

View 3 Replies View Related

Enter Number Into Cell Based On Multiple Of Number In Another Cell

Jul 18, 2013

I have numbers in b2 to b5 say
10
22
35
50

I have numbers in c2 to c5 say
1000
1580
3000
45000

I have numbers in cells d1 to kq1 (1 to 300) d1=1, e1=2, f1=3 and so on

What I want to do is:

In D2 through to kq2 it puts the number in to the cell that correspnds to a multiple of the number in b2.
That would put 1000 in cells m2,w2,ag2,aq2, ba2 and so on
In D3 through to kg3 it it puts the number in to the cell that corresponds to a multiple of the number in b3
that would put 1580 in cells y3,au3,bq3,cm3,di3 and so on

View 2 Replies View Related

Compare Sheet1 With Sheet2 And Copy Unique Values In Sheet1

Aug 13, 2014

I am using the below code and it is not performing the operations of comparing and deleting the duplicate values from sheet1 and pasting unique values in sheet 1, p.s. Do not need values from sheet 2, just want to compare the sheet 1 with 2 and delete dups in sheet1.

[Code] .....

View 2 Replies View Related

Assign Number To A Cell Depending On Number In That Cell

Mar 8, 2012

Trying to have a formula assign a number to a cell depending on a number in that cell.

Example

In all cells in a range from C8 to G13, if there is a 1, i want the number in C20 to show, if there is a 2, whats in

D20, 3/E20, 4/F20, 5/G20, 6/H20

View 3 Replies View Related

Match Value In Sheet1 For Sheet2 And Copy To Sheet2?

Mar 4, 2014

I have a large file + 400 000 rows that has zip code but not city name in sheet2, in sheet1 I have a list of zip code and city name. I need to get the correct city name for the zip code in sheet2 by using the info from sheet1. I attached a file as an example how it looks. I do have lots of other data in the original files that I have removed. This is not doable manually by using filter, to many rows so I need a script to run it.

View 2 Replies View Related

Send Row Number Of Changed Cell To Cell Where Row Resides In Cell

Apr 26, 2008

I have the following range of cells B2:Y3700. I want that when a value from 1 to 9999 is inserted in one cell of the above mentioned range, to copy the row number of that cell in the cell that is located in column Z and row is the number inserted in that cell. For example, if the number "1234" is inserted in one cell from range B2:Y3700, to enter in cell Z1234 the number of the row where is located the respective cell. At same the time is very important to me to not permit to insert the same number in B2:Y3700, so i believe that is need a macro code to check the cell from column z to see if value already exist .

View 5 Replies View Related

Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red

Dec 10, 2008

can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

View 4 Replies View Related

Excel 2013 :: Formula To Auto Delete Number In Dropdown List When Number Appears In Another Cell

Apr 27, 2014

I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.

View 11 Replies View Related

Copy Cell Data To Other Worksheet Cell - Leave Blank If Not Currently Filled

Jul 8, 2014

I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.

I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).

View 4 Replies View Related

VBA Code To Populate A Cell Automatically Whenever Another Cell Is Filled In Manually?

Mar 29, 2014

I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.

When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.

I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.

View 8 Replies View Related

Performing IF Cell Contains Number THEN Copy That Number ELSE Give 0

Jun 14, 2013

Looking for a way to search an active cell (that has been found using macro) for numerical data and if there is some present paste this in a different location

If there is no numerical data in this this cell then paste a 0 in a different location

An example of the string contained in the cell

Product 7.5% wheat <- i would want 7.5 (or 7.5%) copied to a different cell
or
Product X <- i would want a 0 copied to the cell the 7.5 would be in from the above

I can find the cell to search in, i just do not know the correct way to extract the information I need.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved