Determine Which Cells Formula Uses

Nov 10, 2006

If I have A1:A1000 in a column, and I know that A1000 is the sum of 3 cells within A1:A999 but not sure which, what kind of functions can I write to trace out these 3 cells?

View 8 Replies


ADVERTISEMENT

Excel 2010 :: Formula To Determine If Duplicate Values Exist In Single Column (excluding Empty Cells)

Jun 29, 2012

1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2

Tab 2, Column A, has Unique ID's (6 digit numeric values)

The user manually inputs the ID's on new rows in Column A

Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's

When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).

One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.

I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.

The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.

View 9 Replies View Related

Formula To Determine If A Cell Has A Formula Or Constant

Nov 17, 2006

In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.

Examples:
=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".

View 9 Replies View Related

Formula To Determine Age

May 16, 2007

formula I can use to determine age between date of birth (in one cell) and an event date (in another cell)., i.e. birthdate 3/15/2000 with an event date of 3/2/2007.

View 9 Replies View Related

Formula To Determine If An Entry Exists

Jul 21, 2009

I have a database of 6 digit numbers in one column(let's say column A). I would like to put in a 6 digit number in a cell (b1)and have another cell (c1) give me a response as to whether that number exists in the database (Exists or Doesn't Exist).

View 2 Replies View Related

VBA Test To Determine If Cell Has A Formula Or Value

Dec 28, 2008

I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.

View 3 Replies View Related

Determine Formula To Know Period Time?

Oct 9, 2011

I am trying to determine a formula to know a period time for example i started to test a unit at 10:20 am of 10/02/2011 and i would like to add 52 hours of test and i would like to know when and at what time will finish the test?

View 3 Replies View Related

Entering A Formula To Determine A Grade

Apr 6, 2007

I would like to prepare mid-term grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:

Mid-term 1: 30%
Mid-term 2: 40%
essays: 30%

each of the three grades is a number from 1-100; the final answer will be a number which I can then convert to a letter grade.

I'm just not sure what function to use, in that last column, to get Excel to calculate the final answer/grade.

Let's just assume that Mid-term 1 is in column A; Mid-term 2 is in column B; and the essay grade is in column C. How can I create a function that will allow me to give the above percentages to the respective assignments, when calculating a grade?

View 9 Replies View Related

Determine Another Cells Value Before Calculation

Oct 19, 2007

I need to create a macro that will make a cell only run its formula when an associated cell has something in it that will trigger the function.

View 2 Replies View Related

Determine Value Of Multiple Cells

Mar 27, 2008

Below is the code I used in order to calculate the price of an item depending on the number of items ordered, where the items are in column "I" and their corresponding prices in column "J"
The "If" and all of the "ElseIf" statements work fine except for the last "ElseIf" statement. It's not any different from the ones above it though.

Private Sub cmdCalculatePrice_Click()

Dim strDish As String
strDish = Range("B21").Value

Dim intQuantity As Integer
intQuantity = Range("C21").Value

Dim intPrice As Integer

View 4 Replies View Related

Excel 2003 :: Formula To Determine If Someone Is 21 Years Old Or Not

Mar 10, 2014

I am trying to come up with a formula to determine if someone is 21 years old or not. After I enter their DOB if they are 21 or over I would like the cell to turn Green, if not I would like it to turn red. I have tried alot of different formulas that primary pertain to days and months but nothing for years.

View 3 Replies View Related

Determine #NAME- Formula Error In Macro Code

Mar 18, 2008

I am trying to reference a cell that displays "#NAME?" due to an unrecognized formula. However, I keep getting an "Type Mismatch" error. Is there a way to reference a file that displays "#NAME?"

Example:

If Sheets("sheet1").Range("B6") = "#name?" Then
Sheets("sheet1").Range("B3").ClearContents
End If

View 5 Replies View Related

Function/Formula To Determine If Workbook Open

May 16, 2008

how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.

All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.

Function IsWorkBookOpen(ByVal name As String) As Boolean
Dim wBook As Workbook
Dim isOpen As Boolean

wBook = Workbooks(name)

If wBook Is Nothing Then
isOpen = False
Else
isOpen = True
End If

'I can't reutrn the value of is open?
Return isOpen

End Function

I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?

View 3 Replies View Related

Concatenate Cells Then Determine Duplicates?

Oct 14, 2013

I need to detect 2 levels of duplication.

I have a data table that is similar to below (actual table is very large 1000+ rows) but duplicate column is empty.

I don't want to add additional data (i.e. formula to perform concatenate) to other columns as it takes a very long time even with screen updating turned off.

I want to use vba to
1) determine how many rows there are in the worksheet (this updates from an outside datasource)
2) Concatenate baseno & altno and determine if the concatenated value is duplicated
3) Within those duplicates for each baseno&altno pair determine if verno is different
4) Set "Duplicate" value for each duped baseno&altno pair where verno is different

So the end resulting table after macro runs would be

baseno
altno
verno
duplicate

[Code]....

I have been for weeks now and just can't get this to work. I can get it so that I can determine duplicates on one column but I can't seem to get it to concatenate inside the script and compare without writing back to the sheet (which is too slow and I don't want to do).

View 8 Replies View Related

Determine If A Range Of Cells Is Blank

Apr 11, 2007

I'm feeling pretty inadequate at the moment ... I'm trying to determine something that (I think) would be relatively simple ... How do I determine if a single column , for example, (B:B) is blank (or null)? Alternatively, how do I determine if a range of cells in a column or columns, for example, (B1:B30) or (B1:D30) is blank (or null)?

View 2 Replies View Related

Determine Cells Not Common To 2 Ranges

Jun 5, 2007

The compliment of a set is those elements not in the set. Excel doesn't do compliments well.

In Automatic Filter, one can easily see the rows that match criteria, but selecting the compliment of those rows (the ones that don't match the criteria) is difficult.

Similarly, there are the range functions Intersect and Union, but no Symetric Difference.

(Def: the symetric difference of two sets, AB, is the set of those elements in A that are not in B. {1,2,3,4,5}{2,3,4,10,11} = {1,5}.)

Other than by looping through cells:

Given two ranges, aRange and bRange, how would one code for those cells that are in aRange, but not in bRange.

View 9 Replies View Related

Formula For Advanced Spreadsheet To Determine Inventory Count?

Mar 23, 2014

We have a spreadsheet that pulls data from various other spreadsheets that contain information on current year data, budget year data, and last year data. We need to pull from the current year spreadsheet information each month on the inventory count on hand.

We need one formula in one cell of the main spreadsheet that will know to pull from the appropriate month column of the current year spreadsheet based on the particular month we are running the report for.

In other words, we are not wanting to need to change the formula each month, but to write it such that it will know by our indicating in the main spreadsheet which month it is (which is always indicated in Cell B14).

In the main spreadsheet, the cell containing spreadsheet information on where to find the current year data is $D$2.

View 8 Replies View Related

Determine Whether Cell Is Formula Or Direct Input (constant)

Jan 28, 2013

I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.

I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.

I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.

View 7 Replies View Related

Function/Formula To Determine If Row/Column Hidden/Showing

Apr 2, 2008

In the enclosed wb you will find 2 custom functions.

The IsRowHidden Checks weather row 3 is hidden and works fine.

However, IsColHidden does not change from False to True upon hiding col. C.

What did I do wrong !?

View 9 Replies View Related

How To Determine Oldest Date In Range Of Cells

Jan 30, 2012

I am looking for a formula to determine the oldest date in a range of cells. BUT, the part that's kicking me in the pants here, is that the range will change.

In column D, I have a person's name. The number of times that person's name will appear will change every time I run this formula. In column F, I have a date & time. I'm looking for a formula that will determine the oldest date/time associated with that person.

View 2 Replies View Related

Determine Which Values/Cells Sum To A Total Amount

Apr 25, 2008

Is there a function, or how would I write a vba to figure out the following.

I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36

View 8 Replies View Related

Formula To Determine Required Values To Achieve Target Percentage?

Aug 24, 2014

Data is;

83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.

So, my bullseye percentage = 43209/83300, or ~51.87%.

Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.

Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.

View 5 Replies View Related

Formula To Determine Number Of Standard Deviations Based On % Of Population

Sep 14, 2005

The following are a few of generally accepted values: ...

View 8 Replies View Related

Date Calculation Formula - Determine Longer Period Of Time

Aug 9, 2012

How to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.

My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)

Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2

If there is any way to include the month/year in G2 that would be ideal.

View 6 Replies View Related

Create Formula That Will Determine Number Of Orders For Specific Customer?

Aug 29, 2012

I am trying to create a formula that will determine the number of orders for a specific customer whose orders have a pallet count between a certain range. The customer code is a cell reference, as well as the beginning and ending values I am looking for. I have the data on a separate tab from where I am putting the formula.

Here is what I was trying to use, but it was coming up with "0" for everything:

=COUNTIFS(Data!$I:$I,E7,Data!$I:$I,">=A8",Data!$I:$I,"

View 1 Replies View Related

Determine Total Column Width Of Visible Cells

Dec 23, 2008

I am trying to resize a picture on a worksheet to fit a dynamic measurement(the size of the page is different depending on the contents in cells).

I am doing this all in VBA.

so what I have come up with is that I need to

1) determine the total column width of only the non-hidden(visible) cells.
2) resize my picture to those measurements, so I can get an exact fit, and it doesn't create a new page break, by going over.

I have found some code on forums, and am thinking of something like this as a solution(although I don't know how to complete it):

For summing column widths:

View 7 Replies View Related

Determine Last Row And Column With Data In Sheets With Merged Cells

Jan 28, 2009

I am using the following functions to determine the highest row and column that contain data:

View 4 Replies View Related

Determine Minimum Indent Level Of Selected Cells

Dec 28, 2009

I need to determine if any of the cells selected by the user have an indent level equal to 1 and if so then have them confirm the action. the below works well if the user only selects one cell but if the select 2 or more and any of the selected cells has an indent level greater than 1 it doesn't show the confirmation.

View 3 Replies View Related

Determine Math Signs/Operators In Cells Before Calculation

Apr 1, 2008

I have Three different combinations which are in cell A & B, These combinations are with similar signs as in A1=2- & B1 = 2- (& A1 = 2+, B1=2+). Then I have numbers with no signs which A1 =2 B1= 2. My approach is to limit to these and these combinations only and use a formula which is uniformally applied on these combination. I want A1 and B1 to take only like signs like -- or ++ or without any sign. The rest shd be ignored. IS there a way of sorting two columns which have different combinations liek -- or ++ or -+ into a area where u can apply a particular formula? Hence first i take a number and move signs on the left and then apply this

IF A7 = 2- . B7 = 3- THEN

IF(OR(RIGHT(TRIM(B7),1)="-",RIGHT(TRIM(B7),1)=" ",RIGHT(TRIM(B7),1)="+"),LEFT(TRIM(B7),LEN(TRIM(B7))-1)*3,B7*3)

View 6 Replies View Related

Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range

Jul 31, 2009

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

I'm using Excel 2007.

View 9 Replies View Related







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