Populate A Cell Based On Conditions Of Other Cells W/out Putting A Formula In The Cell

Oct 10, 2009

Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?

ADVERTISEMENT

Macro To Populate Cells With Formula Based On Adjacent Cell?

Mar 31, 2014

I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.

View 2 Replies View Related

Putting Specific Value In Blank Cells In Same Row Based On Value In 1st Cell?

Jul 18, 2013

i am trying to Put a specific value i,e "NO Deliveries" in blank Cells in the same row based on value in 1st cell of the Row which has a fixed value . note:the columns and rows are dynamic for example: I want to put the value "no Deliveries" in cell C because its blank.

A B C D

% of Availability
0.00%

80.00%

View 9 Replies View Related

Populate Cells With Data Based On Conditions From Ranges

Oct 13, 2009

What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office

View 2 Replies View Related

Using A Formula To Populate A Cell With Certain Data Based On Content Of Another Cell?

Jul 7, 2014

I am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.

For example, if worksheet 2 has the following:

Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2

and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.

View 5 Replies View Related

Colour Cell Based On Few Conditions / Cell Values Of Other Cells In Same Row?

Jun 28, 2014

In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week

I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.

The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.

The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.

View 9 Replies View Related

Populate Cells Based On Value Of Another Cell?

Apr 6, 2013

I have a table in excel with some data to create labels, but these labels must be repeated according to the number of volumes. Example: I have a delivery for X and such delivery has 5 volumes, need to create 5 labels just changing the volume number: 1/5, 2/5, 3/5, 4​​/5 5/5.

I would enter a value in "D1" after running the code, in column "A" shall be filled as follows.

D1 = 7
A2 = 1/7
A2 = 2/7
A2 = 3/7
A2 = 4/7
A2 = 5/7
A2 = 6/7
A2 = 7/7
If "D1" equals 3 then
A2 = 1/3
A2 = 2/3
A2 = 3/3

View 4 Replies View Related

Count Cells Based On Conditions In That And Other Cell(s)?

Jan 28, 2013

I have an issue log containing (separate) columns with dates, numbers and text.

One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)

I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).

After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.

So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".

After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.

View 3 Replies View Related

Return Formula Value/Text Based On Many Cell Conditions

Oct 16, 2006

This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)

Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses

Is there a formula/function that i can use (like the IF Formula) to complete the following information;

EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"

Column H lists all departments

Column A lists deaprtments

A3 representing the 1st Department needing training

View 8 Replies View Related

Formula In 2 Cells To Populate In Only One Cell

Jun 21, 2007

In the sample worksheet, there are columns "Min", "Max" and "Average". The next 2 columns "Remark1" and "Remark2" I have formulas.

In column D "Remark1", I use this:
=IF(IF(C2="",0,C2)>B2,"Ave>Max",IF(IF(C2="",0,C2)<A2,"Ave<Min",""))

In column E "Remark2", I use this:
=IF(B2=0,IF(B2<A2,"Min>Max",""),IF(B2<A2,"Min>Max",""))

How can I include col E formula into col D formula, so that i do not need to type anything in col E and such that consolidated formula in Remark1 will populate value in Remark2? Will Offset formula do the justice or need a VB code to do the job,

View 5 Replies View Related

Set Cell Fill Color Based On Conditions In Two Cells?

Nov 22, 2012

I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.

Column A
Column B
Expected Action

Press F3
N
Its Fine

Verify this..
Y
Its Fine

this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"

Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".

View 3 Replies View Related

How To Populate Date Cell Based On The Sum Of Two Time Cells

May 12, 2014

I've attached an example workbook.

Trip Plan example.xlsx

I'm trying to work out how to have cells R11 - Rxx generate the correct date, based on the rest of the calculations.

Please note that the attached is a crude cut down version of the larger spreadsheet, for the purpose of highlighting my problem.

SO, if E7 is changed, subsequently the values in K11:Rxx will be respectively updated.

D12 and G12 are entered manually. (although I'll probably make these drop downs or date choosers at some point...)

Depending on the value in E7 and time in D12, the required break period is listed in K11: Kxx.

The time that these periods of rest are required to occur before is then calculated and listed in O11:Oxx.

I need R11:Rxx to calculated the appropriate date from these calculations, relative to the date in G12.

For example, the rest required in K11, is required on the same day, as such it need to populate with the value in G12.

However the rest required in K13:Kxx, is required by a time (O13:Oxx) on the following day - G12+1

In some case, this time variance could run over 2 or more days.

I figure it's got something to do with the value in D12 also including the date of 0/1/1900.

I'm happy to add hidden cells for formulas/results to work around it, as the end product will print as an A4 document, where many cells will be locked.
Would prefer not go with any VBA unless it's my only option as I haven't done any VBA stuff as yet.

NB (the value in P5 is static only in this example)

View 11 Replies View Related

Putting Formula In Cell With VBA

Oct 23, 2008

I tried to put the following formula into a cell using vba, but I received alot of errors. here is the formula I am trying to put into a certain cell and I will use a variable to replace the row number in the formula

=IF(AD323<>"Open","",IF(OR(AND($AI323=TODAY(),$AJ323<>"Done"),AND($AN323=TODAY(),$AO323<>"Done"),AND ($AS323=TODAY(),$AT323<>"Done")),"Yes",""))

View 5 Replies View Related

Excel 2007 :: Auto-populate Cells Based On Value In Another Cell?

Nov 29, 2010

I'm creating a configuration tool for instrumentation that has several options. Many of these options are dependent on one another. To summarize, I need to:

*Conditionally lock cells (and populate with "N/A") OR allow selection from drop down list - the list exists, it's the locking and auto-population I'm struggling with

Example: If A1 = No, then B1 = "N/A" and is locked, else allow selection from drop down list in B1

View 3 Replies View Related

If Then Formula - If Value Is Higher Than Identified Cell Then Other Cells Populate?

Jan 6, 2014

I am trying to determine how to great a formula for cells E5, E6 of the attached (in pink) that says if the value of the cell above (E4) is equal to or Greater than the value of cell E10, the cell will perform the formula that I already have in E5, E6.

But what I'm trying to do is create a 5 year investment plan whereby the worksheet automatically inputs those values for me if the balance is above our minimum reserve. That way I can play with the default assumptions and see how it changes throughout the worksheet.

View 1 Replies View Related

Putting Formula Into A Cell In Another Workbook

Jun 13, 2013

When I try to run the macro, an error comes up saying application-defined or object-defined error. The following code is the line in which I receive the error.

VB : tool.Worksheets("ALL").Cells(row1, "J").Formula = "='[TRB Database.xlsm]TRB Database'!$I$" & A

View 3 Replies View Related

Populate A Specific Range Of Cells Based On A Value Of Any Kind In A Header Cell

Sep 22, 2009

I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.

View 3 Replies View Related

Putting Data From Several Cells In One Cell Together

Oct 10, 2011

I have a sheet with 2 colomns: client number and document number, as follows:

Client number Document number
1234 100012
1234 100042
4321 102233
4321 249123
4321 533213
4567 134123
7890 123451
7890 125675
7890 357353

I need to have a unique client numnber on 1 row with all his documents in one cell, as follows:

client number: document number

1234 100012, 100042
4321 102233, 249123, 533213
4567 134123
7890 123451, 125675, 357353

View 5 Replies View Related

Putting Formula In A Cell Which References Other Sheets

Mar 7, 2009

I am writing a VBA macro in excel. I have several sheets in this one workbook........one sheet for each day in a given month. Sheets for each day are labled as 3_1, 3_2 for march 1st and march 2nd respectively. I also have 4 sheets for the 4 weeks in a given month. The weekly sheets are labeled week1, week2 etc....

What i am trying to do is this:

In the week1 sheet i am trying to "put" a formula in say cell 9,12 which sums up the same cell in the first 7 days of the month. But i am having issues. Does anyone have a good way to do this via VBA?

David

View 9 Replies View Related

Populate List Based On Two Selected Conditions

Apr 6, 2009

I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for.

This worksheet posesses amongst others; two columns titled location and sublocation.

I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions)

This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.

View 7 Replies View Related

Putting 2 Conditions In IF Statment

Jul 16, 2008

I would like to check whether 2 conditions are satisfy in an if statement. One condition is of data type text and it is stored in a variable calls search1 whereas the other condition is of data type date and i intend to store into a variable calls search2. Could someone tell how to put search 2 into the below code. Basically, i want it to be like

if Cell A is equal to search1 and CellB is equal to search2 then

return value that is associated with the search in cell c


ActiveSheet.Cells(r, 13).FormulaArray = "=MIN(IF(Sheet1!$A$1:$A$20=""" & search1& """,Sheet1!$C$1:$C$20))"

View 9 Replies View Related

Select Certain Cells Based Upon Condition And Putting In Sheet 2

Mar 25, 2009

I have attached the file with this post.

What I like a macro to do is:

Whenever a 'D' ends in column "Step 5" the last value of 4 cells to the left to be put in sheet 2 in reverse order.

View 5 Replies View Related

Formula To Get Cell Value Based On Other Cells

May 29, 2014

I have a range of data A1:G52, I need to put a formula in B60 that will find the value in the column H of the range.

To find the right cell in column H, I need to find the row where

B55 matches in column A1:A52
B56 matches in column C1:C52

There will only be on row where they both match, in the row i need the value of the cell in column H

View 7 Replies View Related

Populate One Cell Based On Two Others

Apr 9, 2014

I am trying to populate a field in a Sheet 1 that is dependent on two other drop down fields that are chosen. Some of my data has the same names but different equipment.

Name
Equipment
Serial
Model

Jim Jones
snips
123
1AB

[Code] ....

Basically, I want to choose a name from say a drop down, then an equipment pc from drop down, and have the other field populate for say the Serial Number.

View 4 Replies View Related

VBA To Set Cell Value Based On Multiple Conditions

Jan 4, 2013

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.

View 9 Replies View Related

How Do I Highlight A Cell Based On Conditions Of Another?

Aug 5, 2009

I want to highlight A1 red if C1 is greater than 55. Is this possible?

View 3 Replies View Related

Cell Value Based On Multiple Conditions

Feb 23, 2010

I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.

I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.

View 7 Replies View Related

Reset Cell Value Based On Two Conditions

Mar 10, 2009

I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.

View 9 Replies View Related

Highlight Cell Based On 2 OR Conditions

May 15, 2008

I am seeking assistance with code for column H to be highlighted when I have a value of "Pending" or "Work In Progress" is present in column E?

View 3 Replies View Related

Populate Cell Based On Worksheet Name

Mar 18, 2009

I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column B on the summary page for each row of theirs that is copied over....here is my current macro I use to copy the data: (this code is repeated for each sales persons sheet - "Blankenship", "Dew", etc...) I have attached a scaled down version of my file.

View 2 Replies View Related

Populate Cell Based On Two Criteria?

Aug 2, 2013

I have a list of teams, and beside them I need a calculation to be filled.

The criteria is based on a cell reference (a month which can be changed from a drop-down in BM2) and also the name of the team.

dummy 16.xls

View 5 Replies View Related

3 IF Conditions But Formula In One Cell

Feb 18, 2013

"IF" formula. I want this in one cell but not able to do so :

If A1180, then Over Utilized, If A1=180, then Fully Utilized.

If the formula returns 0,1,2 instead of Under Utilized,Over Utilized, Fully Utilized respectively, I have no problems.

View 1 Replies View Related

Copy And Paste Cell Based On Conditions

Jul 9, 2012

I have data in columns A and B.

If the data is in column A it will not be in column B.

My objective is to move the data in column A to column B, as long as it is not a number with the color yellow in the cell.

The data displays as follows:

**SPECIAL NOTE: Cells with 34407211756830 and 34407212013659 have yellow cells.

Column A Column B
34407211756830 000 Staples IV

RC 636

View 1 Replies View Related

Populate List Based On Single Cell?

Apr 8, 2014

When I enter a value for 'Number of Steps' from a drop down a table is generated with the x values changing accordingly.

For example:

Number of Steps 5

x x^2
------------
0 0
1 1
2 4
3 9
4 16

Number of Steps 9

x x^2
------------
0 0
0.5 0.25
1 1
1.5 2.25
2 4
2.5 6.25
3 9
3.5 12.25
4 16

You will notice that the number of steps is the number of x values that take us up to 4. The 4 is fixed. I would be able to work out the maths myself as I am a maths teacher but I was wondering how I could autopopulate the table based on a drop down box selection. If it involves going into VBA then don't worry my son needs to get to grips with what I have done.

Edit. The formatting does not seem to work above. On the left of each table is supposed to be the x value and on the right is x^2.

View 7 Replies View Related

Populate A Cell Based On Range Of Data

Mar 5, 2009

I need to modify the code below:

PHP
Sub Macro1()
 Macro1 Macro
    Dim strbody As String
    For Each cell In Range("B2:B640")  
    Sheets("MASTER").Select
    Cells.Select
    Selection.Copy
    Sheets("Final Merged").Select
    Sheets.Add
    ActiveSheet.Paste  
   Next
End Sub 

The code above is creating a new sheet for each different value in column B. I would also like to take that value and place on the newly created sheet in cell A17.

Also, how would I get the sheet to be ranamed to match the value in cell A17 of the newly created sheet?

View 3 Replies View Related

Automatically Populate Cell Based On Worksheet Name

Mar 18, 2009

I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)

View 5 Replies View Related

Populate Cell Based On Multiple Criteria

Dec 7, 2006

I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense...

That being said: what I am looking to do is change cell L3 based on new criteria in cell M3.

Right now if B3=Stationery World then L3=Stationery.

What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.

View 9 Replies View Related

Populate Cell Based On A Dropdown Selection Of Another

Jan 25, 2008

I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.

View 2 Replies View Related

Formula To Find Value Of Cell Based On Selections Made In Two Other Cells?

Feb 2, 2014

I am making a form with drop down boxes and auto fill to make things easier. I have one Box for the Company selection another box for the occupation selection and I need a formula to find a $ value based on the selections made in these two cells. If I could establish the actual cell reference of the selected data in the second drop down list. The data will be much larger than this example and will live on a separate sheet.

View 2 Replies View Related

Create Active Cell Formula Based Of Variables From Given Cells?

Apr 20, 2012

I am trying to create an active cell formula based of variables from given cells.

With ActiveCell.FormulaR1C1 = "=CONCATENATE(""RU"",RC[-12],RC[-11],RC[-10],RC[-9])-CONCATENATE(""MU"",RC[-12],RC[-11],RC[-10],RC[-9])"
ActiveCell.Formula = ActiveCell.Value

But This does nothing.

View 5 Replies View Related

Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell

Aug 3, 2009

I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:

Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.

i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow

However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).

View 4 Replies View Related

ADVERTISEMENT