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
ADVERTISEMENT
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
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?
View 14 Replies
View Related
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
Jul 25, 2014
I have a column of values (E4:E87). I'm adding 1 value to this column manually each day...so tomorrow (July 24th) I will have E4:E88...and so forth. Each day is a new row.
In column Z (i.e. cell Z87) I have the formula =(SUM(O53:O87))/(SUM(E53:E87)). Z88 will have
=(SUM(somevalue:O88))/(SUM(somevalue:E88)). This gives me a resulting percentage for a rolling period of time.
I need an automated way to populate "O53" and "E53" ("somevalue") in the formula so that the SUM of the current row's cell (i.e. E88) through [whatever] is 50 or greater. (The sum of the values should be as small as possible, but at least 50, and should not include any older values than absolutely necessary for it to equal at least 50.)
An image of my spreadsheet can be viewed here: [URL]
View 3 Replies
View Related
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
Jan 24, 2014
I need to be able to populate a cell with text from 3 possible cells, two will have the text "none" in them, the other text cell is the one i need to populate in another cell.
View 3 Replies
View Related
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
Jul 9, 2012
So I have a table where column B contains a text entry, and column C returns a certain other text entry based on if certain keywords appear in the B entry. Say, if B8 contains "Fresh Apple Pie", column C8 could return "Tastes Delicious" by searching for "Apple" in B8. I've got it work just fine using a ridiculously long If(Isnumber(Search())) function, but the problem with this is that if I want to adjust the output, or add a new input rule I have to go into the formula and that just gets messy. Instead, I'd like to start an extra sheet, or a table to the side, where the user can write in column D what he's looking for, and then in E what the formula should return. Ie., if I wanted to change "Tastes Delicious" to "Getting Sick of It", I'd just have to change the E8 entry to that text, but leave D8 as "Apple". I've been playing with array formula's, but I can't get the formula to search past the first row of criteria.
Basically, I want something like this, where columns D and E are open ended for any user to add new search criteria or change the existing (for example, I could put "Gummy" in C4 and "Candy" in D4, and B3 would then return "Candy"):
Item
Output
Search For
Classify as
Candy Apple
Fruit
[Code] .....
I've been using this formula:
{=IF(ISNUMBER(SEARCH(C3:C4,B12)),D3:D4,"")}
(the cell references are different, but you can see the formula)
And it half works. If C3 is found in B12, it'll return D3, but if C3 isn't found I need it to look for C4, which it doesn't do. Naturally, the end formula will be a much larger range than two cells though.
View 6 Replies
View Related
Apr 9, 2012
see the example below...
ABCDE1blueblue2greengreen3redred4purplepurple
I basically want column E to check the cells from left to right and display the value that is in the first populated cell. For example, cell A2 is blank, therefore, it should display the value from B2. A3 & B3 are blank, so E3 should display the value from C3 and so on...
Is this an "IF" statement?
View 2 Replies
View Related
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
Mar 25, 2014
I have contacts list that I made and want to take it to the next level, but don't know how. See attached.
The goal:
1. Highlight an entire row of someone's contact info (i.e. row 9)
2. Click the "ORDER FORM" text box to activate a macro that populates specific cells on the 'order form' sheet with the customer's contact information from the highlighted row. The information I want copied over is in red on the order form sheet. THAT'S IT!! (Oh, and 'paste value' the current date from H5 so that the date is static on the order sheet
If possible, I would like to have an error check in case no row is selected prior to clicking on the text box that pops up the note "Please highlight a contact row before clicking the 'Order Form' button."
View 5 Replies
View Related
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
Jun 5, 2013
I have a scheduling tool that I need to do the following with -
Allow user to select a range of cellsUser types a value (non formulaic just plain text)Value gets populated to all selected cells
The reason I'm not just using fill handles is because of the amount of formatting in the cells.
View 6 Replies
View Related
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
Jan 4, 2010
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
View 4 Replies
View Related
Sep 2, 2013
I am trying to auto-populate text in cells in area A, based on data I enter into other cells in area B. I want the area A cells to be for display only, as all editing will be done in area B. The problem is: how do I do this such that the text I write does not get cut off if is longer than the column width? See the attached document for a clear example and description of what I am trying to do...
Excel Q.xlsx
View 7 Replies
View Related
Mar 22, 2014
I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
View 10 Replies
View Related
Apr 27, 2014
with the attached spreadsheet?
I want to populate "selections" sheet with data from the previous sheet (it will be the date) depending on the value of a cell in column G. You will see I have already got the data from 27th April into my selections sheet but wondered if it can be done by a formula to save me copy and paste time?
View 2 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Sep 26, 2007
My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with.
View 14 Replies
View Related
Dec 22, 2009
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
View 9 Replies
View Related
Dec 4, 2013
If I have value "H" in cell C1, how would i have "I" in cell C2 and "J" in cell C3.
FYI : next 2 alphabet after "H".
View 7 Replies
View Related
Aug 26, 2009
I have a template (attached) that needs to be populated. It's objective is to determine the QUARTER and YTD based on MONTH. For example, there's a M shown for the month Jul, Q also shown for the month Jul (since current month is Jul, current quarter month is only Jul and not Jul, Aug & Sep), and all months up to the current month shows Y for the year row. The above is currently done manually. I need a formula that will fill in the Q and Y's automatically based on the M. Is it possible?
View 3 Replies
View Related
Aug 3, 2007
My macro runs, and it inserts a cell, but in order to record data on a summary page, I need a formula to auto populate, and I've tried having the macro copy/paste and I've tried ActiveCell.Formula...and nothing seems to be working. The formula needs to go into Cell B3.
View 9 Replies
View Related
Aug 1, 2014
I have a list of data with 2 column. One is a column of unique values, the other column has a lot of blanks.
I'd like to auto generate both columns without any of the blank data from column 2. I've attached a mock spreadsheet of what I'm invisioning.
View 6 Replies
View Related
Feb 24, 2014
I need to assign van seating assignments to a client room census. I have a workbook attached. I am hoping a formula can get me there. The only variables are if the cell in column E has TO BE CLEANED or READY in it, then I want the formula to overlook that particular cell. I don't want to assign a seat to an empty room! My workbook should make what I am attempting to accomplish much more clear.
Also, there are either 12 or 15 seats available in each van, but the workbook reflects that.
View 9 Replies
View Related
May 19, 2009
Am attaching herewith one sample file. In the column G are the number of days as at 31-12-08. I want the value corresponding to that those days to populate in respective slot of days in either column H or I or J or K. E.g. stock number e (in cell A6) is having number of days 453, hence the value 110.920 (cell E6) should fall in cell J6. However i am want the single line formula which will validate in which cell the value should fall.
View 5 Replies
View Related
Oct 29, 2009
I am comparing each cell of column A with Column D to match it. I have almost 60,000 records in each columns. Now, I want to show the result of the mismatch in column B as "No Match".
Below is a sample of what I am trying to achieve.
A B D
adam No Match Bill
Wales Charlie
Charlie Denny
Charlie
Wales
I tried to record a mecro with formula (=IF(ISNA(MATCH(A2,$E$2:$E$65536,0)),"No Match","")). It works fine with fewer records but when i try it with my original file it fails.
I also tried to write a macro without formula but no success. It only worked well with fewer records.
What am I doing wrong? Is there anyway to solve this?
How to create a macro using the same formula (=IF(ISNA(MATCH(A2,$E$2:$E$65536,0)),"No Match","")) I used to run it manually.
View 9 Replies
View Related
Aug 11, 2009
I am trying to populate cells based on dropdown boxes. The data is on one worksheet and needs to be populated in cells on the main worksheet based on information in the two dropdown boxes. I have included my worksheet for reference.
View 2 Replies
View Related
