Expand A Cell Formula Based On The Entry Of Several Other Cell Entries

Jul 6, 2014


I am currently using the above formula and need to make an addition to it.

If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30

View 5 Replies


Deny Cell Entry If Cell Exceeds 5 Entries

Jun 19, 2007

i have managed to pull together some code that will deny people adding data into cells if they have 5 of the same entry. the entries are entered in a range and are matched against a single cell outside of the range. heres the

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim greycell As Range, i As Long
If Not Intersect(Target, Me.[grey]) Is Nothing Then
Application.EnableEvents = False
For Each greycell In Target
If WorksheetFunction. CountIf(Me.[grey], greycell.Value) > 5 Then
i = greycell.Interior.ColorIndex
greycell.Interior.ColorIndex = 3 'red
MsgBox "no cell entry past 5", vbCritical, "ERROR"
greycell.ClearContents: greycell.Interior.ColorIndex = i
End If
Application.EnableEvents = True
End If
End Sub

what i need with is adapting this code to match two ranges as i cant use the worksheet_change event twice. i need it to be as if they were seperate events but are merged together. eg:.............

View 3 Replies View Related

Insert Formula Based On Cell Entry Using Vba

Oct 5, 2009

i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3

i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.

View 9 Replies View Related

Formula To Return The Value In A Column Based On Cell Entry In Row

Jun 3, 2006

Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).

I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.

View 7 Replies View Related

Count Entries Only If Theres An Entry In The Cell To The Left

Sep 21, 2008

i'm trying to get a column to count all blanks but only if there's and entry in the cell to the left. for example i have a list of names which is picked up from my main database in column a, then in column b there's dates, non applicables and blanks. however the columns are longer than the list of names to allow for growth, so there's a lot of blanks at the bottom which i don't want to count. so is there a way to count only the blanks in column b if there's a name in column a alongside it

View 3 Replies View Related

Expand X Axis Based On Cell Values

May 15, 2014

I want to create a bar chart so that the X axis scale is based the value in the reference cells. For example, a table created for running profile as follow:

The data table # of minutes2221 pace (MPH)

The chart I need to produce. The data has 4 records, but the chat needs to show bars of total number of minutes. Picture1.png How do I do it?

View 2 Replies View Related

Expand Named Range Based On Cell Value

Jun 13, 2008

I have a workbook containing two worksheets - Sheet 1 being my "data entry sheet" and Sheet 2 is a sort of summary report containing multiple named ranges (all grouped according to similar values in a cell i.e. all cells containing 1-1 is named GRP1, all cells containig 1-2 is named GRP2). Basically, my workbook looks like this:

Sheet 1
1 Lea Female 1-1 Lea Wells
2 Leo Male 1-2 Leo Anderson

Sheet 2
1 Anne Thomas Female 1-1
2 John Doe Male 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2

Hence, when I enter the data as shown in Sheet 1, Sheet 2 should look like this:

1 Anne Thomas Female 1-1
2 John Doe Male 1-1
3 Lea Wells Female 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2
6 Leo Anderson Male 1-2

View 4 Replies View Related

Cell With Formula Won't Expand To Fit Text

Mar 31, 2009

I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.

View 5 Replies View Related

Ignore Formula Entry But Not Other Entries?

Mar 17, 2014

I have a cell with a formula in it. I want to use conditional formatting on this cell if the formula result leaves it blank. Given the formula is in the cell it's never recognised as blank. If an entry is input instead the formula then obviously I don't want the conditional formatting.

What formula can I use in the conditional formatting for it to recognise the Cell formula as blank?

View 6 Replies View Related

Getting Entry In One Cell To Cause Entry In Another Based On Entry In 3rd

Mar 20, 2014

I have a percentage in R3.

If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.

View 4 Replies View Related

Automate Data Entry Based On Existing Entries

Aug 8, 2008

Daily Iam Entrying 100`s Of Supplier Data And Material Detail,

I Want Data Should Be Poped Out While Entering In Dataentry Sheet And Data Selction Should Be From Ohther Prefilled Cells. Auto Merged Post Until 24 Hrs Passes;Here Is The File

View 4 Replies View Related

Deleting Cell Content Based On Entry In One Cell?

Mar 22, 2014

I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?

View 14 Replies View Related

Restrict Cell Entry To Numbers Based On Another Cell

Mar 13, 2008

I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:

Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.

Can I do that with data validation? I can't get any if thens to work in it.

View 3 Replies View Related

Prevent Cell Entry Based On Another Cell

Oct 22, 2013

If A1 = "Yes"

Then don't allow input into cells B1 and C1. Or delete anything in B1 and C1 perhaps?

Cell A1 would be the result of a formula

How would I go about creating something like the above?

View 2 Replies View Related

Restrict Cell Entry Based On Another Cell

Sep 18, 2007

I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.

So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.

View 3 Replies View Related

How To Expand Cell Selection From Current Cell To Bottom Of Spreadsheet

Jan 24, 2013

I would like to know if there is a way to expand the selection of cells from the current cell you are on to the very bottom cell available in the spreadsheet.

For example, I want to apply a formula from A12 - the very last A cell row in the spreadsheet.

View 1 Replies View Related

Expand/Create Single Cell Alpha Series From Cell

May 15, 2008

I have a cell with data such as: a0001-0004, a12, C8AF7-8, b17, j35-40 and i want it to output in the same cell(basically write-over the data): a0001, a0002, a0003, a0004, a12, C8AF7,C8AF8, b17, j35, j36, j37, j38, j39, j40.

View 4 Replies View Related

Countif - Based On Other Cell Entry

Aug 21, 2009

dont know if this can be done using a formula rather than VBA...but is there a formula that would change the countif range based on another cell entry. so the formula below has a column range of 2...so the other cell entry would be '2'.

=COUNTIF(B1:C1,"=Y"). If i then changed the cell entry to '4' the formula would change to. =COUNTIF(B1:E1,"=Y")

View 2 Replies View Related

Cell Sound Based On Entry

Dec 1, 2006

I will try putting the Code Tags in correct this time, I think I figured out what I have been doing wrong.

This code will allow one cell to make a sound when a condition is met. I am trying to have each cell stand on its own. Example cell A1 would sound a Ding if the number >100 and cell A2 would sound a Tada if the word Cat would be returned ....

View 9 Replies View Related

Combine Cell Entries Based On Criteria

Mar 12, 2014

I need to first merge cell entries of column 2 if cell entries of column 1 are the same; following columns always retain their corresponding first row (see cells highlighted in yellow in attachment).

View 14 Replies View Related

Restrict Time Entries Based On Another Cell

Jan 23, 2008

The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.

View 2 Replies View Related

Select Tabs Based On Cell Entry

Dec 17, 2008

i'm trying to basically use VBA, so that when a list of work tab names are entered into a column in say Sheet 1..these tabs are then selected.

View 4 Replies View Related

Fill Value In A Cell Based On User Entry

Mar 26, 2009

I have a dilemma.

1.I want to be able to a have a cell be filled with a certain value when the user enter data in Column C.

2.I know that this can be accomplished by a formula, but my spreadsheet will work my efficiently with a macro.

3.I do not know much about VBA but I believe that I will need a macro for each sheet that fills in the word.

View 6 Replies View Related

Execute Code Based On Cell Entry

Oct 28, 2009

I would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows.

View 14 Replies View Related

Selecting Between Two Worksheets Based On Cell Entry?

Jul 12, 2012

I have a workbook with 3 worksheets which contains a single spreadsheet where the user enters data and 2 worksheets containing all the named ranges and formulas(divided up by product model) used on the data entry worksheet. What I've been trying to accomplish is to choose one worksheet based on which product model I select on the data sheet.

View 1 Replies View Related

Force Cell Entry Based On A Condition

Jul 31, 2008

I need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.

View 2 Replies View Related

Formula To Transfer Over Cell Entries

May 12, 2009

Money savedMoney Earned NameTotalNametotalNameTotalMarkJanieRobCyndiPamDiana

Here's a tricky one. The chart that shows the four columns are on sheet 1. I need a formula or a VLOOK array that will fill in the 2 columns with the 3 rows shown on the left which will be on a sheet 2. Lets say that all start with A1 in both sheets. The tricky part is, i want sheet 1 to be able to populate onto sheet 2 starting with the Name column first row no matter what name i select on sheet 1. Say that Janie earned money, but Mark didn't. I enter Janies info in Money saved & Money Earned. I get her total, and the end result should be her name showing on sheet 2 in the first row under name and her total showing on the first row in the total column. Also, i would need it to bump one up or dowm to the next adjacent column if i were to go beyond the 3 rows in the 1st set on rows in sheet 2. Say Janie, Rob and Cyndi all had info, but now Mark does, i want to be able to push the others down one and over onto the next set of columns.

View 9 Replies View Related

Prevent Entry In Cell Even Without Formula?

Jan 16, 2014

how can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1

I want to prevent an entry in these cell and if you try an error message will display

View 1 Replies View Related

Using Only Specific Cell Either For Entry Or Formula

Apr 25, 2007

I have a problem to solve

I have 2 worksheets named 'Products' and 'Order'

At the 'Order' i use 3 cells


When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.

What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.

1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000

2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000

because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50

View 9 Replies View Related

Formula Protection For Cell Entry

Feb 11, 2004

I'm trying to set a cell so that it has a certain value based on other cell(s) information. I can put a formula into the cell to do that, but I also want to make the cell available for user input, preserving the formula should the user change their mind and delete their input.

Basically, it's a cell with a protected formula that can be overridden with user input, then if the user decides to take their input back out, the formula kicks back in.

View 9 Replies View Related

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