I need some formula to sum data with adjacent row and multiple 10 number, For example :
name
cost
expected result
mike
5
45 = 5 + (4x10)
4 (x10)
deby
3
27 = 3 + 4 + (2x10)
4
2 (x10)
sisca
6
6
i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.
i am ussing excel 2007.
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 RelatedHow (in VBA) to enter the same data in a variable number of adjacents cells, given the number of cells is indicated in column P, the data to enter in column Q and the location of the first cell to enter the data is in column R?
eg
Column P = 3
Column q = 1,000
Column R = v2
I would want to enter "1,000" in V2, W2 and X2
I have been asked by my supervisor to make a performance review template a little bit more user friendly.
Basically what I have is a drop own list in cells in one column (various cells with the same drop down menu) that you select from either 0-10 in a drop down box.
I have another cell which I want to have a drop down box with text comments that you select from when. Only problem is the comments different dependent on the ranking score you give a person (the number selection in the previously mentioned cell from 0-10). So if you have a score between 9-10 in the cell then the comments box will give you certain comments to choose, 7-8 different comments and so on and so forth.
I am looking for a formula to number rows in A1:A500
I would like to skip a row when B1:B500 cells are empty
then skip that row and start renumbering from 1
This is what I tried so far
=If(b9>"", countif($B$9:$B9,">"""),"")
I am generating a chart through a macro. I am using 4 non adjacent range and the end point of the range is represented by a variable as the end point is not fixed. I am using following code.
Row = 5
StartPoint1 = "C19"
EndPoint1 = "C" & 19 + Row
StartPoint2 = "E19"
EndPoint2 = "E" & 19 + Row
StartPoint3 = "G19"
EndPoint3 = "G" & 19 + Row
StartPoint4 = "I19"
EndPoint4 = "I" & 19 + Row
ActiveChart.SetSourceData Source:= Sheets("Summary").Range( _
"StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4"), PlotBy:=xlColumns
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).
View 6 Replies View RelatedI want a message to show up if I have any of 6 selected cells empty? It basically serves as a reminder to fill in all required cells in a sheet that may be located in different cells. How can I write a formula in one cell that will look at all source cells and tel me that some are empty?
NameAgeLocation
Joe******
HeightWeightTime
******3 PM
Formula Here to verify all filled in.
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 am looking some formula to make sequence number with criteria;
1) if in cell B2 contains only single/one data ----the result is mark "-"
2) if in cell B2 contains several data (not single)----the result is auto numbering with adding mark "." (dot) and start from 1.,2.,3.,4,etc........
for the detail,
condition 1 (multiple data in col.B)
number
name
1.
john
2.
mike
3.
sisca
etc...
condition 2 (single data in col.B)
number
name
The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.
The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied
Private Sub FindAll()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)..............
I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
View 14 Replies View RelatedI have 6 columns of data laid out as follows,
columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
I have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top.
The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F),
Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That
1. puts the name of the sheet I am copying from in column A
2. the deadline date in Column B (that date is always in C10 of each worksheet)
3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet
Dim DestSh As Worksheet 'worksheet in which to paste summary
Dim Last As Long
Dim CopyRow As Long 'row to copy
Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1)
End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.
View 7 Replies View RelatedI want the user the be able to pick a number. This number corresponds to a table value. The formula needs to take the value in the adjacent cell from the picked number and run a calculation on it.
For example, if the user picks 7, it will select the adjacent value to 7 in a table for use in a calculation in another cell.
I attached an example : example_001.xlsx‎
I have data in 37 sets of 6 columns and on several rows (that increase in number depending on my needs). Rows 1 to 3 are information headers.
For each row, starting on row 4, I would like to sum the cells that are on the 4th column of every set, providing that the info in the 1st row & 2nd column of each set equals either "USD", "EUR" or "GBP". If one of the above conditions is met but the cell is empty (shows "#value!"), I do not want it to be taken into account.
I have a spreadsheet with rows of data. Presently, there are three pairs of columns. The first column in each pair has a label (E, F, R). The second column represents the number assigned to the label (e.g. 30, 40, 60, 70). The labels may appear in the first column of any of the three pairs. I would like to reorganize the data in three columns, which are headed, E, F, R, with the associated number underneath the heading. I have attached a sample spreadsheet showing the desired results.
View 2 Replies View RelatedI am trying to find a formula that calculates the longest period from data in an adjacent cell.
The attached spreadsheet might better explain this
I am trying to analyse a whole heap of data generated by an image analysis package. The data is exported out in to excel, I then need to be able ot rearange it a bit and export it out to another program.
I have attached a smaller version of the data as an example. The example data set only contains 3 image planes with ~100 entries for each. A full data set contains 96 planes and ~2000 entries each.
So what i want to do.
I want to setup a workbook i can dump this data into and have it spread everything out over 96 different sheets (1 for each plane).
I can get it to do it for the first lot of data using a IF command like =IF('Object Data'!B:B=1,'Object Data'!D3,)
But that only really works for the first plane, as the starting cell for plane 2 is always going to be different.
So what i need is a formula that will say: look at the plane column, if it equals a certain value print out the value for the other cells in that row.
Is that possible?
I want to total cells that have data in them across a row. I have a name in Column A and then in Columns C-Z I have data. In Column B I want Excel to scan across cells C-Z and if a cell has data in it (it will be text) I want it to add one to the total in cell B. Would CountIF do this with text involved?
View 7 Replies View RelatedI am looking for two formulas (col. B+C) in order to split every second value from Col. A into two adjacent columns. Empty cells, in col. A, should be ignored. The requested result is shown in green and red.
View 4 Replies View RelatedI'm using this formula (in conditional formatting) to compare a cell with the previous cell (e.g. E11 compares to D11). If it is higher/ lower/the same, E11 is coloured accordingly.
=MATCH(E11,Grades,0)<MATCH(D11,Grades,0)
=MATCH(E11,Grades,0)>MATCH(D11,Grades,0)
=(D11)
However…. sometimes the data is not in D11 but C11 or B11. How do I tell excel to first of all look in D11 and only if the data is not there, to look in C11, then B11?
I have been using a look up that should be bringing me back a numeric value when i enter a certain code that relates to that value.
When i enter the code 267760RuVw the number brought back shoud be 850
When i enter the code 532210BiMe the number brought back should be 2000
The look up that i am using is =VLOOKUP(B4,Lorry!B3:O37,14)
All my sheets are in the same workbook
The look up value is on a sheet called "Pricing" and the table array is on a sheet called "Lorry"
The cell that the look up is entered into just returns N/A
Why is this not working?
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.
How to populate data in non-adjacent cells from information that is stored on another sheet. Sheet 1 is essentially setup to be visually pleasing to the user, but the data stored behind it is on another sheet stored in a more logical manner. I would like to auto-populate the nicely formatted sheet with information from sheet 2 when an item from a drop-down list is selected. I have found quite a few ways to do this using offset, etc. but in my case the values will be pulled into cells that are spread around the design sheet in no set order.
View 3 Replies View RelatedHow to do this or if there's even a formula or even a macro i could run to run this data.
I have three rows of data (29-31) from columns D to Q.
I would like to take D29 and add each of the values in the adjacent columns exactly once and sum up the value D thru Q
For example I'd like to return not just Sum(D29:Q29) but also Sum(D29:R29)+Q30, Sum (D29:R29) +Q31.
I guess that might be considered a 1X3 matrix & and 8X3 matrix, but I'm not really sure how to set up the return values for these equations nor how to return them very quickly.
I'm revamping a key inventory for my company and would like to be able to track the history of each key (name of person who it was issued to, date issued, date returned). I figure the best way to do this for the 90 keys I currently have in circulation is to create drop down lists for each key that has been used by more than one person in its history and upon selecting a given person from the drop down list of keys used more than once, the adjacent cells would populate with the corresponding data of date issued and date returned. Some keys have not been used more than once so not every key will have a drop down. Here is what my key inventory spreadsheet looks right now.
View 3 Replies View RelatedUntil now if I needed to find a cell's data (and its adjacent data) I've been using code in the format of (for the purpose of this example, you can assume all data in coulmn 1 is unique. Functioning as aprimary key using incremeting integers):
View 3 Replies View Related