Formula For Locating Last Cell Above That Contains A Number And Adding 1
Feb 14, 2013
Im trying to develop a formula that sequentially numbers in column a depending on if there is a value in column B
We have documents at work that have alot of text with random spacing between that needs a sequential reference number so would like the formula to be able to look at the value in column b and if its not blank add a number . I would like this number to be the previous cell above + 1
the formula ive started looks something like =if(B10""), ???????,""
can not get excel to reference cell the last cell above with data.
View 3 Replies
ADVERTISEMENT
Jan 12, 2010
Take a look at these columns below;
.00 810.00 .00 1,729.00 .00 1,594.00 .00 1,599.00 .00 498.00 498.00 .00 .00 1,852.00 .00 454.00 .00 50.00
I want to find the cell reference number of any value of column A (which is greater than 0) in column B. I could only manage to find values with my formula i.e.
=IF(A1>0,VLOOKUP(A1,$B$1:$B$9,1,0),0)
View 9 Replies
View Related
Apr 27, 2012
Sheet1
DEPARTMENTLOCATION POSITION TITLEPOSITION NUMBERCODEKXNJCEO34500A3100KXNJCEO34501A3200DXDLMGR42001A5600DXDLMGR42002A5700NXNLCHIEF23001A1200
Data Range: A1:E6
Sheet2
DEPARTMENTPOSITION NUMBERLOCATION POSITION TITLECODEKX34500ANJCEO3100KX34501ANJCEO9100DX42001ADLMGR5600DX42002ADLMGR5700NX23001ANLCHIEF1200
Data Range: A1:E6
What I need is a formula (Not a VBA), is to reconcile both sheet2 and sheet1 ensure that the codes appearing for each position number in sheet1 gets updated based on the codes for each position number appearing in sheet2. So, for instance, the code for pos #34501A should be changed from 3200 to 9100 in sheet1.
The only issue with sheet2, though, is that the column number for position number could be different each time new data gets copied into sheet2 (thought the header information stays the same). I know that it can be done via Vlookup if the place of the column doesn't change each time, but I just
How to locate the correct column and row in a range of cells to search and then extract information based on certain conditions.
View 7 Replies
View Related
Apr 2, 2014
I have a column full of data like the below with relevant data on the same row.
I wish to locate the cells which have a specific section most importantly without the "cartridge only" so for example with the below
Word Academy (Nintendo DS)
Word Academy (Nintendo DS) (Cartridge Only)
This would show 2 given there are two with the specific text "Word Academy (Nintendo DS)" there will always be a cell without the (Cartridge Only) part so for this the formula =COUNTIF(D:D,"*" & D1 & "*") seems to do the job to simply count the cells with that in however I do not wish to do this.
What I need to do is locate the cells that have this in but match them together in a large sheet so basically I somehow need to be able to locate the two corresponding cells which both have one of the cells values of "Word Academy (Nintendo DS)"
The cells have data in the row which I need to tie together to create a total value.
For example
Title Qty
Word Academy (Nintendo DS) 5
Word Academy (Nintendo DS) (Cartridge Only)6
I need to locate the all the cells with "Word Academy (Nintendo DS)" in which would mean just the two cells "Word Academy (Nintendo DS)" + "Word Academy (Nintendo DS) (Cartridge Only)" in the above instance but I need it to then see the cell which has that in its title and add the quantity of the two together leaving me with the below
Title Qty Actual Qty
Word Academy (Nintendo DS) 5 11
Word Academy (Nintendo DS) (Cartridge Only)6 11
Which is basically just adding together the quantity shown on the spreadsheet in the rows with the containing text "Word Academy (Nintendo DS)" and as "Word Academy (Nintendo DS) (Cartridge Only)"
On the attached spreadsheet Column L is an example of the result I would be desire, I have put a basic =sum formula in to just represent the value it should be showing.
View 4 Replies
View Related
Jul 1, 2013
I am using Excel 2010.
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
View 5 Replies
View Related
Apr 1, 2009
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
View 14 Replies
View Related
Dec 4, 2008
i have a question about adding a number to a cell. This is the problem. Each day we track contacts we made in a spread sheet. how could i, lets say type 2 in a cell that already reads 42 and once a tab away from the cell it would change to 44 (add in the background 42+2=44).
View 5 Replies
View Related
Nov 2, 2012
I am trying to calculate the percentage of a number, have it subtotal, and then add to that subtotal another number all within the same cell like so:
20000 + 5% = 21000 + 4000 = 25000
View 3 Replies
View Related
Jan 7, 2014
I have an existing Cash Flow Report that has a column of abbreviated/shortened (WBS Element) title where each cell contains a unique three lettered/numbered amount of characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Element) title (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBS Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru. B11, just to the left of its abbreviated/shortened (WBS Element) title.
View 3 Replies
View Related
Jan 7, 2014
I have an existing Cash Flow Report for my work that has a column of abbreviated/shortened (WBS Element) titles where each cell contains unique three lettered/numbered characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Elements) titles (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBA Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11, just to the left of its abbreviated/shortened (WBS Element) title.
Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx
View 1 Replies
View Related
May 28, 2006
I want to create a formula that will refer to the cell I want by adding a number to the cell reference, not to the contents of the cell. For example, lets say that the cell C11 holds a value of 22. I want to add 4 to C11 to get my formula to reference cell C15, I don't want it to add 4 to the contents of C11 which is 22 and would give me a result of 26. I have a large spreadsheet and I can't just fill down the way I have it now and I don't want to have to retype the formula every time.
View 2 Replies
View Related
Mar 28, 2009
First I would like to say that I am not English nor very good to explain myself so hope the title is according to the forums rules. Now to my problem
I would like to to put in for instance the number 100 in cell A and then the number should appear in cell B. I would like to remove the number in cell A without the number in cell B to dissapear. Then add for instance 50 in cell A to get the number in cell B to add up to 150 and so on. How can I do this? I would like to add that cell B is already connected to a different cell. And I am using Vista.
View 5 Replies
View Related
Jan 13, 2007
I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.
I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.
View 9 Replies
View Related
Oct 7, 2011
I do receive invoices from my vendors. They send me an invoice in PDF formate. I am using able2extract software to conver the PDF to excel. It is working great but most of the times my vendors enters the items/Case in the description field. Like the example below:
"Bajri Rotla 11 oz.(24)"
I would like to place a "," in between the last character and begining of the first number. In the above case it will be after "a" (in Rotla) and before "1" in (11). If I can do this then I can apply texttocolumn from excel and use "," as delimiter and separate item name from size and case/Pack. Then can use another "Texttocolumn" and separate size and case/pack. If there is a VBA code that can be written for this it would make my life a whole lot easier.
Following are 3 images that I have copied from actual data.
1. Original PDF invoice data that I receive from my Vendor
2. Image of the excel file before formating. This is the format from "Able2Extract" (3rd party software that I use to convert PDF to excel. Works great!!
3. Converted Excel data sheet. This is the way I want to format the data.
View 7 Replies
View Related
Jan 6, 2009
I have a list of numbers in column A (i.e.: 1234)and I need them to show up in column B in with an "*" asterisk on each side on the number (i.e.: *1234*). So I was using "=a1" in cell B1, is there away to add the asterisk to the formula as text?
View 6 Replies
View Related
Mar 31, 2013
I want to do a basic formula for a poker account to work out my P/L.
I have two sheets. The first sheet will record my P/L and the second sheet records deposits and withdrawals.
When I input a figure into Sheet 1, Cell A1, I want it to then be able to add to this figure the total that is in Sheet 2, Cell A1. Is this possible? This way the figure that I input into Sheet1, Cell A1 will give me the profit/loss after taking into account any deposits/withdrawals.
ie If in Sheet2, Cell A1 I have a deposit of -500. When I type into Sheet1, Cell A1, a figure of 800, it will then change to 300. It will add 800 less 500 = 300.
View 5 Replies
View Related
Jun 24, 2012
I'm currently doing a new worksheet for work
Cell's A1: Commission Per Sale Cell B1 Running total of commission
I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.
E.g.
Commission $5 $7 $9
Running Total $5 $12 $21
View 2 Replies
View Related
Jan 14, 2013
I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.
Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String
[Code].....
View 3 Replies
View Related
Apr 17, 2008
The loop is putting the correct formula into the cell - but THEN... - I wish to (within the loop and before it moves on) check if the value is equal to OR greater than 1 - if it is, make it a value of 1 (this '1' is then used at the bottom of the column of data to give a total). I need to do this before it moves onto the next cell. What am I doing wrong? Is it because once it puts formula in the cell, it then moves down - do I need to select the cell again first? As I had originally posted on this site - link: url]http://www.excelforum.com/showthread.php?t=641970[/url]
View 2 Replies
View Related
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
Apr 9, 2009
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
View 2 Replies
View Related
Sep 28, 2006
I'm trying to use the formula vlookup, but running into trouble. I'd like to use =VLOOKUP(3000&A1,E:F,2,0) but this isn't working correctly because it doesn't recognize "3000&A1" as a number? Is there a way to do this? This will be used in a VBA code, so I prefer the solution in VBA, but either way is fine. See attached for more info.
View 4 Replies
View Related
May 14, 2009
I've got a table similar to the below (but with a few more columns & rows). I need 2 look at 2 cells one contains a $value (say $75,000) the other contains a code (say C). I need it to return the correct value - in this example 3.00%.
This is going to be too messy to use a vlookup, I'm sure there is something better. Is this where you use Match or Index ... both of which I don't understand how they work. Or something else ?
ABCD
$ 1 $ 50,000 1.00%1.50%2.00%2.50%
$ 50,001 $ 100,000 2.00%2.50%3.00%3.50%
$ 100,001 $ 250,000 3.00%3.50%4.00%4.50%
A B C D
$1 $50,000 1.00% 1.50% 2.00% 2.50%
$50,001 $100,000 2.00% 2.50% 3.00% 3.50%
$100,001 $250,000 3.00% 3.50% 4.00% 4.50%
View 9 Replies
View Related
Jul 6, 2008
I have a range of date values that span B1:BA1 and I need to find the next date value greater than TODAY() and return the column number.
View 14 Replies
View Related
Oct 24, 2008
How can I locate 1000 or the closest number from column 5, and all the data to the left of it? I have tried all the commands I could find, and nothing has worked for me.
This is the data I want to pull from the table.
1.463149.6271.0440.985999.87
View 11 Replies
View Related
Sep 15, 2012
I need a macro that identifies duplicates within a spreadsheet. Unfortunately, the utility provided by Microsoft is not adequate because the text strings that are potential duplicates are nestled inside other text.
Fortunately, however, there is a unique text string that will locate the duplicated data. Below are two examples:
Plus -- Zero Box ==> Zero Box Medium ID=7288 (submittedby Anna Johansson)
Basic -- Zero Box ==> Resolve Medium ID=6397 (submittedby Kazy Suzuki)
The data that needs to be identified as potentially duplicated is the four digit number (i.e. 7288 and 6397). You will notice that they are preceded by “ID=”.
The spreadsheet has over 26,000 rows of data and 1700 plus IDs. The characters ID= proceeds the four digit number in all cases.
I simply need a macro that will provide a list of the four digit numbers that are duplicates. This will allow me to easily locate and manually delete any surrounding, unwanted data from the spreadsheet.
The data that the macro needs to search is contained in and restricted to Column C.
View 2 Replies
View Related
Dec 24, 2012
I want to assign macros to buttons from VBA. I know that buttons have a TopLeftCell property and I've used this before to point at cells around the button but not the other way round.
What I'm trying to do is have excel assign a macro to a button when the workbook is created (if thats possible - if not then just whenever it's opened the macro can be reassigned).
The recorder gives me the line of code:
Selection.OnAction = "ThisWorkbook.ImportJobsButton"
so I really want to change Selection for something that points at the specific shape. I want to do this with a few shapes.
View 1 Replies
View Related
Mar 10, 2007
On the computers in my office they have a My Documents folder that is in the directory C:Data. There is also a My Documents folder in the usual windows location C:Documents and Settings etc. Is there a function that will give me the path of the folder that is on the desktop? The macro will be run on computers with the folder in different locations which is why I need to look it up.
View 5 Replies
View Related
May 6, 2009
1 35
2 37
3 39
4 22
5 25
6 27
7 28
8 23
9 25
I have the above table. What I want excel to do is to grab the largest number in each set of numbers from the second column and return the corresponding number in the first column. So basically, excel would return a 3, 7, and 9 in cells C1, C2, and C3.
View 8 Replies
View Related
Oct 31, 2006
I have a single button on a spreadsheet. When clicked, it gives me Error 91: Object Variable or With Block Variable Not Set. This is the button's
Private Sub Analyze_Click()
Dim TextToLocate As String
Dim Searching As String
Dim TechNum As String
TextToLocate = "Mobile Device : "
Fname = Application. GetOpenFilename("@Road Excel Files,*.xls", , "Open @Road Log File")
Workbooks.Open (Fname)
' Error here at cells.find:
Searching = Cells.Find(What:=TextToLocate)
TechNum = Replace(Searching, TextToLocate, "")
TechNum = Left(TechNum, 5)
MsgBox TechNum
End Sub
The purpose of the code is to:Allow the user to open a log fileIn the newly opened log file, find the string "Mobile Device : "Following "Mobile Device : " is a tech number. Strip out "Mobile Device : " and grab the first 5 characters of the tech number.Show me what the tech number is in a message box
If I insert the code into the actual log file (no button and no opening of files), it works fine. I searched this forum and others and used everything I learned to find a solution, but the error remains.
View 9 Replies
View Related
