What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
What I want to achieve: those cells have number 1 ( in column E) , adjacent cell values ( In Column D) are copied to G12 in the following format:: Mark,Lark ( i.e. cell value , cell value, etc)
is it possible ( with a command button) Sample attached
I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".
I have attached the work book of what I am trying to accomplish.
I have a matrix in excel to show % and $ for multiple columns - set up like this:
Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3
the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
View 14 Replies View RelatedI have a large workbook with 2 rows of data I want to format (R25:R530, M25:M530). Row M is already populated, row R is new data that I am entering. When the value of a cell in row R is larger than its adjacent cell (R25:M25) in row M, I would like the font to turn red. When the value is smaller, I would like the font to turn green.
With the amount of time I've spent attempting to conditionally format, I could have gone through and formatted each cell individually by now!
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx
I have a list of data:
2
140
1
660
10
140
0.92
660
2
130
0.18
660
4
510
0.44
820
4
510
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
Macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.
For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro do this for every row even if the word and/or the number changes.
I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.
I need to write a condition in such a way that cell IF A1= "DBR","BPR","SLR","SKR" then change cell B1 to SPECIFIC COLOUR Each condition different colour. A1 values ("DBR","BPR","SLR","SKR") is a list of selectable values. if the condition is falls then B1 should be normal (white color cell) with 0 in it.
View 1 Replies View RelatedHere's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
Here's an example of what i'm working with,...
Range is C40:D48
I have a results table that i want to be able to rank into a simple list 1 - 4. However the results are not in cells beside each other.
I tried using a helper column and then the Large function, however cannot get it to work.
I have attached an example sheet, and in column L4 - L7 i would like to put the team names (taken from column B) into the right order from the results shown.
I have to replicate this over a number of different pools in a number of different divisions. This is just one pool.
Sometimes I need to copy the values in adjacent/continuous cells and paste them into the cells which I select and which are not adjacent/continuous.
Like for example i want to copy values from A1:A4 and paste in C1,C4,C7,&C10
jane....kaser...highschool......A
jack....Anderson....college.....B
Jill....Lovin.....college.......C
if both jack+anderson in a same row adjacent cells
as per in my table it should return column 4 value (A)
can I use VLOOKUP?
is there another way ?
I want to search a block of two adjacent cells in a table and return its forth column value.
I'm trying to write a function that returns, from a different sheet, the value in column C and row 6*current row.
For example, if the function is typed into cell B5 in Sheet 1,
the resulting value would be whatever is in cell C30 in Sheet 2
I tired the following but it gave me an error
=Sheet2!&"C"&(ROW()*6)
I have the following formula: =IF(C319<=300,9,IF(AND(C319>300,C319<500,6.75),IF(C319>500,6))) It returns 9 if C319<=300 but returns FALSE for all other numbers. I want the funtion to return 9 If <=300 and 6.75 if C319 is greater than 300 but <=500 and if it greater than 500 it should return 6.
View 5 Replies View RelatedI am working on creating a spreadsheet that can be updated by those unfamiliar to excel. I have a master list on one sheet and three separate lists for business, individual, and general. Each list has three columns giving name, address and postal code. There are numerous repeats on these lists and I wanted to hide any duplicates while keeping my cells aligned. So far I have been able to highlight any duplicates, but was interested in some function that would save at least one duplicate while hiding any additional ones.
View 2 Replies View RelatedI am new to programming in excel but am looking to make a VBA that allows me to see if a value entered exists in a second spread sheet. If it does then write a comment next to it. If not then write a different comment.
This is what my spread sheet currently looks like:
Sheet1 contains all the important information:
A
B
C
D
E
F
Load
PO
Delivery
Email
Item
ShipCon
1
3456
6543
Y
5219
5129
2
2345
5432
Y
1234
4321
Sheet2 contains the items that contain a shipping condition. There are two shipping conditions Fragile and non-Fragile. The list is of Fragile items.
Item
5219
1234
I am looking for a way to have it set up that when i enter an item number in sheet in 1 it searches in sheet 2 to see if it is fragile, if it is then returns, in sheet 1 column F next to that specific item 'Fragile'. If it doesn't find it in the list returns 'non-fragile'.
Column B contains geographical Areas. Column C contains a list of business departments.
North Env
North Env
North Ops
North Sales
North Sales
North Sales
South Env
South Maint
South Ops
South Sales
South Sales
South Sales
Etc.
The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b.
We have two worksheets, sheet a and sheet b
compare sheet b, column c (email addresses) to sheet a, column x, (email addresses).
If they match, move sheet a, columns d, e, f, g, and h to sheet b.
I have the following data set with about 500 rows. It has one large value in column A, followed by a number between 1 and 24 in column B.
I am trying in Excel 2013 to develop a formula that will: divide A by the value in cell BPlace in cell C, D, E, F... the result of this division, where the number of times the value is repeated depends on the value in B. E.g.
Example of source values/data set:
A
B
18504
18
2011
2
23694
24
(about 500 rows more)
An example of the output result that I am looking for:
A
B
C
D
E
2011
2
1,005.5
1,005.5
In above example, the value in column B is 2, so the result of the division of value A with value B is repeated twice. In case value B would be 18, the value would be repeated 18 times...
I have been trying to do this in Excel for more than 2 hours, but I did not manage to complete it.
I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.
View 5 Replies View RelatedSo I have 2 sheets from a much larger worksheet where I wish for the first sheet to extract a row of values, one at a time from the second sheet using a range of numbers I enter as reference for where to look for the data. Sheet 1 can be thought of as a summary page and Sheet 2 is where data is stored.
I need to first check if any data under Sheet 2 column B fall within a specified range of numbers and if any of those numbers are found I want it to grab the largest and latest number from that range and pull all the data from certain columns in that same row and place it into Sheet 1, one column value at a time.
Sheet 1 A1 = A range of numbers as text. 10-15, 16-20 etc Only one range is entered. This also tells me what results I'm looking at.
Sheet 1 B4:B14 = Destination cells for the individual data I'm wanting to pull from Sheet 2 columns H:P. B4 wants H, B5 wants I, B6 wants J etc
Sheet 2 column A = A3:A102 are numbered 1:100.
Sheet 2 column B = A series of numbers anywhere from 10-70. Always in sequence and they can repeat. 10,10,11,12,13,14,15,15,15,16 etc. Ranges from B3:B102. Only one number per cell.
Sheet 2 columns H:P = Data in H3:P102 I want to extract to Sheet 1 B4:B14.
Now lets say I want a formula for Sheet 1 B4 which wants a value in Sheet 2 H. If Sheet 1 A1 = 10-15, I want to check whether Sheet 2 column B has any values equaling those and then tell me which row that last number appeared in.
Example: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5. (B6 is outside range and B4 isn't the last time the 14 appears.)
Then, knowing B5 is the value I want, find which row it is in (row 5 in this example) or use the number in A5 (3) and then find my way to column H (H5) where the value I want to pull is.
Example 2: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5.
Sheet 2 H4=10, Sheet 2 H5=32, Sheet 2 H6=42. Place "32" from H5 into Sheet 1 B4.
I'd like to also have some error control so I'm not trying to pull data from blank cells if it's relevant. Maybe check if Sheet 2 X3=0 and if it is, do nothing as no data appears if the cell is 0.
I have put a lot of time into trying to solve this myself but I feel way out of my depth. I've tried going step by step but I can't seem to figure out which functions are relevant and also things like how to return the range that the A1 values appear in or if using MAX, not having it return values outside of A1's range also.
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies View RelatedI want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:
=SUMIF(D2:D110,D112,E2:P110)
My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?
Let me explain, I am trying to auto generate a list. So I have one cell A1 with value "ABC"In another sheet,
I have a list of products in cells A:1 to A:100 and "ABC" is one of them, but is there 4 times. I have value in B:1 to B: 100
What I need is to create a list of all the values in Colum B that are related to "ABC"
Colum A Colum B
ABC 10
ABC 20
ABC Test
ABC TBD
Based on "ABC", I need a list to generate a list:
10
20
Test
TBD
I try Arrays and VLookup and Match and index but they are all single data return.
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!
View 6 Replies View Relatedi had data in html format.i want the data to be splitted into columns in excel.
HTML FORMAT
Employee name
Emp id
johnabraham wesley peter neiljohn and so on....... 254800 254801 254802 254803
i tried copying the data from html to excel. Then applied text to columns but i m getting incomplete names from this step.
My problem is how would i get the full name in one Column(Column B) & the emp id in the next Column (COLUMN C).Column A will have the data copied from html page. The length of the employee name differs. The only saving tip is that the employee name will suffix with sno like
Johnabraham -1
254800