Positive Cells Taking A Zero Value

Nov 4, 2008

I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero.

I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.

View 4 Replies


ADVERTISEMENT

Taking Team Names Into Two Cells?

May 21, 2014

How i can take just team names in A column as home and away to B-C columns ?

View 4 Replies View Related

Taking Percentage Of The Larger Of Two Cells

Sep 19, 2006

I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.

View 5 Replies View Related

Taking Text From 2 Cells And Inserting Into 1 Cell?

Aug 16, 2013

Is it possible to take text from 2 different cell and inserting into one cell?

For example:

Cell A1 reads 'John' and cell A2 reads 'Smith' can I make cell A3 read 'John Smith' by taking those two bits of information?

View 2 Replies View Related

Taking The Value Of A Range Of Cells From One Workbook And Place Them In Another

May 12, 2007

I've been trying to learn how to write code in VBA. I've been learning for about 2 weeks now (trying to learn it for work) and heres my problem. I have a macro that will create a pivot table from raw data that I input. From this pivot table, I want to go to the last row in the pivot table (the row that takes grand totals of each column of data). I want to write a macro that will take those values (located in a workbook called AR age sorting) and place them in cells in another workbook (called AR aging analysis). Basically cells D6-D9 in workbook "AR aging analysis" should equal the values of the last row in my pivot table in workbook "AR age sorting" (last cell with data in columns J-M). I thought this would be easy but I've run into some issues. Heres the code I use:

Sub aging_summary()
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Set Sheet1 = Sheets("AR age sorting")
Set Sheet2 = Sheets("AR aging analysis")
For n = 10 To 13
For M = 6 To 9
Sheet2.Range(Cells(M, 4), Cells(M, 4)). _
Value = Sheet1.Range(Cells(n, 1), Cells(n, 1)). _
End(x1Down).Value

Next M
Next n
End Sub

I don't want it to copy and paste the values since I just need the value, not all the extra formatting and what not that is incorporated in the pivot table. When I try to run this I get an error message, saying that there is an application-defined or user-defined error.

View 2 Replies View Related

Sum Two If Both Cells Contains Positive Value

Jul 12, 2014

If i want to sum A1+ A2 and A2 containes a negative number i only want to sum A1.

Like this:

Example 1
A1,A2
3,3

3+3 = 6 (Both cells contains positve number)

Exampel 2

A1, A2
5, -3 = 5 (A2 contains negative number only sum cell A1)

View 6 Replies View Related

List Only Cells With Positive Values

Mar 15, 2011

I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?

View 6 Replies View Related

VBA - Compare 2 Cells For Positive Match?

Nov 7, 2013

Need vba to do the following: Compare 4 Columns for a 100% match: B, F, N, K.

IF ALL 4 columns] contain matches then, it is considered 100% match and can proceed with other code.

'Below is an example of how it should perform the COMPARISON:

'1. Compare what NSN's exists in B3 and B4:
'...If = MATCH, proceed to check critical column F for matches
'...If = NO MATCH, proceed with 100% FAIL rule.

'2. Compare what NSN's exists in F3 and F4:
'...If = MATCH, proceed to check critical column N for matches
'...If = NO MATCH, proceed with 100% FAIL rule.

'3. Compare what NSN's exists in N3 and N4:
'...If = MATCH, proceed to check critical column K for matches
'...If = NO MATCH, proceed with 100% FAIL rule.

4. Compare what NSN's exists in K3 and K4:
...If = MATCH, proceed to the set of rules used to perform updated and merging to these 2 rows.
...If = NO MATCH, proceed with 100% FAIL rule.

PROVIDE MESSAGE:
IF all four match, THEN,
MsgBox ("100% Match")

IF at any point along the way, one of those columns does NOT match, THEN
MsgBox ("NOT a 100% Match")

(for example, if it looks at Col B - finds match, it then moves to F and if F does not match, stop and provide message (no need to continue comparing the last 2 columns)...

View 5 Replies View Related

Negative & Positive Results In Separate Cells

Apr 9, 2008

Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.

View 4 Replies View Related

List Positive Values From Negative Cells

May 17, 2008

VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.

View 3 Replies View Related

Macro To Find Positive Value In One Cell Then Copy Value Of Corresponding Cells

Nov 11, 2009

I have a 'rota' worksheet that includes staff names and their rostered hours on all dates through the month.

On each date of the month I need excel to find that date in column A and then look across the row to find any cells that aren't blank. When the cell has a value I need the name above it in row 1 and the value (number of hours) itself to copy across to a 'daily activities' sheet. The date is autopopulating on this sheet in cell H5.

The name needs to drop into column A and the hours worked into column B. I would like the first cell to be A9 and then down from there with no spaces in between names. The other info on the sheet will then complete itself using the name that has been dropped in using VLookup.

I'm presuming I can use a macro to do this for me but am really struggling where to start with it, and how to ignore blank cells as they will change cells on a monthly basis.

View 7 Replies View Related

Formula To Return A "1" If Two Cells Have A Positive And Negative Value

Nov 1, 2008

In Cell (W6) I have a value for example of 100,000 In Cell (AE6) will have a value of for example -100,000. In Cell (AF) I want to put a formula that says "if the number in (W6) is a positive number and the number in (AE6) is a negative number the return a value of "1". and vice versa if the number in cell (W6) is a negative number (-100,000) and number in Cell (AE6) is s positive (25,000) then also return a value of "1".

View 9 Replies View Related

Taking Number In Between For Interpolation

Jun 16, 2014

I need to take number in between cells. For example, I have numbers 1, 2, 3, 4, 5, 6, 7. When I type 5 in a column, I want to have 4 and 6, above and below 5. I am doing this to ease interpolation, so that whenever I want to do interpolation, I just need to type what number, I would like to have to be interpolated.

Attached file is the example : Matic Interpolation.xlsx‎

View 5 Replies View Related

Taking Numbers Out Of A Number

Sep 5, 2007

what I have is a spread sheet of over 3000 numbers.

Now this is what the number looks like:

0000123456000

what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?

View 10 Replies View Related

Data Entry Taking An Age!

Sep 29, 2008

However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.

View 3 Replies View Related

Taking Average Of Grades?

Sep 23, 2013

I need to calculate the average of 5 grades from A,B,C,D, and E. However I want it to the average even if any number of grades (i.e. up to 5) are entered.

e.g

grade1 grade 2 grade 3 grade 4 grade 5
A A C B E

I have assigned a vlookup table to convert to numerical values, and got it to calculate the average, but it falls apart when any grade is missing.

I have used the iferror command on the different calculations, but when i put them to gather into one large string, again it falls apart.

View 8 Replies View Related

For Loop Taking Too Long

Feb 26, 2009

I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.

The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:


Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row

For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else

Cells(Rownum, 11).EntireRow.Delete shift:=xlUp

Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Taking Data From A Cell

Aug 7, 2009

Is there a fomula that would take data from a cell.

I am looking to take everything before and includuing the word LTD
For example if a cell contained
Joe Bloggs Ltd (Mr Jones) C/o USA

I would like to be able to take "Joe Bloggs LTD"

View 9 Replies View Related

Using Loops For Taking Averages

Jul 14, 2006

I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows:

Range("A2").Select
startCell = ActiveCell.AddressLocal
ActiveCell.offset(3,0).select
stopCell = ActiveCell.AddressLocal

Now, clumsy programming aside, I just want to say something like this

averageCell = AVERAGE(startCell:stopCell)

View 2 Replies View Related

Taking The Last 5 Characters Of A String

Apr 11, 2007

I have a string, specifically a file path, and I want to take the last 5 characters of the string. How would I got about doing this?

View 3 Replies View Related

Taking Next Highest Number

May 31, 2007

I have a dilemma. What I want to do is: If I input a date in a cell I want the adjacent cell to take the next highest number from a list on another worksheet. My example attached

View 3 Replies View Related

Taking Entries From Certain Columns And Putting Them Together

Apr 4, 2014

I have a huge spreadsheet where after every 12 columns there are two "special" ones, which contain certain entries. In the first of these two, the entries from the second one are numbered in a certain way. Please see the example.

I need a formula in a separate column (yellow) which will gather all these entries from all columns, and put them in order, as I did manually in this example.

Instead of 4 "groups" of columns, in my real file I have 200 of them, with 400 rows each, which makes it more complicated..

View 2 Replies View Related

Taking Data From Website To Spreadsheet?

Jun 21, 2013

I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.

I am attaching the spreadsheet.

These are the websites I am taking my data from: [URL]

View 3 Replies View Related

Matching 2 Values To 3rd And Taking Count?

Aug 29, 2013

Currently I have 3 Columns. The first column is a list of phone numbers that have confirmed and bought our product. The second column holds the phone numbers of the initial leads that we generated. And the third column is the keyword that the lead used to find our website.

What I need to do is match the phone number from Column A with the phone number of Column B then record the frequency of the keyword that was used to produce that lead.

I've attached a screen shot with the first few rows of my table (Nearly 1000 in total) as well as an example of what I imagine the result looking like.

First Few Rows Of The Table:
Excel-Keyword-Matching.jpg

What I Am Looking To Do:
Keyword-Frequency-Complete-Example.jpg

View 2 Replies View Related

Taking Over Spreadsheet With Macro Not Working?

Jun 3, 2014

Debug error points to:

BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")

Sub is below:

Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer

[Code]....

View 2 Replies View Related

Taking Row Of Spreadsheet And Putting Into Main One

Nov 29, 2013

I have a bunch of spreadsheets in a folder and I need to take one line of each spreadsheet (which is in the same place on every one) into a master spreadsheet.

I am always adding to the spreadsheets in the folder.

Is there a way to have my master spreadsheet look out for changes in the folder and when there are changes add the row into it?

View 2 Replies View Related

Taking Matching Fields And Numbering Them

Jul 15, 2009

I have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:.......

I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column)

This is what I would want it to look like:...........

View 3 Replies View Related

Taking Sum Of Column When Some Rows Hidden?

Feb 29, 2012

I have autofiltered a column to meet a certain criteria (which hides some of the rows), then I want to put a cell with the sum at the bottom of that column. When I do this, it takes the sum of all of the rows in that column, even the rows that are hidden. Is there a way to only take the sum of the rows showing?

View 5 Replies View Related

Taking One Cell Information And Creating Two

Jul 28, 2014

I have a userform that searches a data base for an address an pulls up the corresponding information. I was wondering if there was a way to get it so that if I put 1234 5th Street when it puts the information into the data base it would separate it into two cells so 1234 would be in one section and 5th street would be in another.

View 1 Replies View Related

Taking Data From Other Spreadsheets Using A Macro

Oct 3, 2007

I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:

Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C: est"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
Workbooks(fn).Close False
fn = Dir
Loop
End Sub
I have tried using this code, altering the Dir String to the folder the documents are in.

However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.

To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.

View 9 Replies View Related







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