Adding Cells Based Off A Set Numeric Value From Another Cell
Dec 11, 2009
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it.
I want a formula to add cells based off a number value in another cell.
So it would be something like
IF M3= 1 add C5 to SUM ; If not then go to next step
IF M3= 2 add F5 to SUM ; If not then go to next step
IF M3= 3 add I5 to SUM ; If not then go to next step
IF N3= 1 add C5 to SUM ; If not then go to next step
IF N3= 2 add F5 to SUM ; If not then go to next step
IF N3= 3 add I5 to SUM ; If not then go to next step....................
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.
Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".
Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.
I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.
cell A1 is equals to B1 because the cell C1 contains the number 1. Now if i change the value of the C1 to 2 how can i make the A1 shows the value of the B2 ?
I am trying to do is extract the volume size of products in 'ml' from 10k plus products from a description field cell. this description field could also contain the weight of the product in grams so I cannot just do a search for a numeric string , it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
I'd like to sum up values based on certain cell attributes. Firstly, I don't know if it's possible to define cell attributes and how to do it. Let me put some example:
I have one column where I've put some values and I'd like to sum up the values only for certain cells in this column. In an "OOP approach" I'd use some grouping method based on attributes, so each cell would have an attribute called ID, for example, and I could select only the cell with some desired ID.
I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.
What I am trying to do is get the net sales of 80 in either of Dave's rows in the Net column, then I can hide the duplicate row. However, this is complicated by the fact that names without returns, (like Fred in this example,) do not have a second row. (This also applies to those names without sales, but with returns, like Andy).
I have a spreadsheet where I am adding up figures in columns.
I need to differentiate between 4 different "types" of cells to sum. Cells with figures in them are either green, red or have no fill; and some cells contain no figures at all.
At the bottom of the column, I need to total up all the figures in red cells, as well as all the figures in green cells.
The shading of these cells is not permanent - colours are changed as work progresses, so I need the totals to keep up with this.
If it cannot be done based on cell fill colour, is there any other way to do it, other than the usual long-winded way of @sum(..... etc
I have values in a number of cells. Next to them, if there was a value, regardless of what the value is, except blank or zero, i would like the cell to turn blue and if possible have a 1 in the cell.
I've used the conditional formatting, however, among the options, 'less than' is the closest to to getting the color right. The issue is that if the cell has nothing in it, it still colors the corresponding cell blue; because it is not less than the already blank cell. If it is blank, I don't want it to color the cell.
I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.
I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.
My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.
I would like one column (column A) to be an "indentifier" column. Column B is the customer name, column C is the customer city and state, column D is the customer phone number, and column E is the customer email.
By calling column A an "identifier" column, what I mean is that I would like to be able to go down the list and put an "x" next to each customer I would like to include in that particular workbook. I would then like to be able to run a macro which would take each of the customers with an "x" in the column A, create a copy of the template comfirmation call sheet, and fill in the identifying information from columns B through E into the copied template. Each worksheet would also need to be renamed the same as the customer name. It would need to perform this same procedure for each row that has an "x," making sure that all worksheets (confirmation call sheets) are grouped into one new workbook.
Sheet 1 is called "main" Sheet 1 contains 5 columns A = Blank B = Customer Name C = Customer City and State D = Customer Phone E = Customer email
Sheet 2 is called "template"
"x"'s are placed into "main" sheet, column A to identify which customer will be affected by macro...................
I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.
Sub Share_Sales3() Dim Prompt As String Dim Caption As String
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more.
Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc.
Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
Been trying to work this one out and have ended up banging my head against the wall.
Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability.
The team name is a selection from a list, the availability is 1-6 (different levels of availability)
I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up:
Name Team 1st 2nd 3rd 4th 5th 6th 7th etc. Mr X ___1st___1______2__1 Mr Y ___2nd_______2________5__5__5 Mr Z ___1st___1____________3__3__3
Total 1st: 2 2 1 3 3 3 Total 2nd: 2 5 5 5
So basically the formula in the last row goes along the lines of:
Add each cell in the column IF the team name = 1st
I have a spreadsheet which contains 2 drop down pick lists in Field1 and Field2.
Code: FIELD1 | FIELD2 | FIELD3 -------------------------------------------- In Cell A2, I have this set in Data > Data Validation:
Allow: List Source: =CatList
If the user selects a value from the CatList range, then the drop-down options in Field2 are updated accordingly.
In Cell B2, I have this set in Data > Data Validation:
Allow: List Source: =INDIRECT(A2)
The Ranges are as follows:
Code: Cat1 | Cat2 | Cat3 --------------------------------------------- Cheese | Hat | Square Trees | Cat | Circle Bees | Sat | Triangle Knees | Mat | Rectangle Apologies | Bat | Octagon
I'd really like to be able to add a message into FIELD3, which is based on the value in Cat2
e.g. using pseudocode, something like:
If Field2 value = "Apologies" then put this message in Field3: "This requires approval from A" elseif Field2 value = "Triangle" then put this message in Field3: "This requires approval from XYZ" elseif Feidl2 value = "Bat" then put this message in Field3: "This does not require approval"
Basically this is for an access form, and I need to be able to add a feedback message to field3 based on what's in Field2.
I thought about using the INDIRECT option to add messages but I can't see how I'd do it. Maybe VB would do it, but I don't know it well enough.
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
In cell D16, I would like the sum of D35 (only if D29 contains data) to D54 (only if D48 contains data)...there is more than just adding these two - there's actually 25 that follows the pattern.
I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.
1. I have a main worksheet that will have a number manually entered into a cell (lets say A1); 2. I have a 'template' worksheet; 3. I'll assign the macro to a button on the main worksheet
If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).
i have an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E" Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
I want to be able to sort the columns in a worksheet based on their cell values, eg. column of text sorts A to Z and column of numbers sorts largest to smallest. Cells are formatted as General, number or currency but the sort button only offers A to Z or Z to A.
I'm trying to add up some values based on a criteria in another column. I have numbers in column A ranging from 0 - 100 and in column B have totals for those numbers. What i am trying to do is Add up the totals in column B based on the following criteria's in Column A.
<7, >7 & <=30, >30 & <=60, >60.
I have a summary page to display the individual results in separate cells.
I have a list of numbers in which I need to return the last 5 digits; the length of the string may vary.
However, if the last two digits of the number ends in a specific value then it should skip over the last two numbers and return the preceding 5 digits.
I have tried and have used the very basic Left, Right and Mid functions. My problem is I dont know how to code the formula to identify the last two digits and skip over them, if necessary.
I have provided an attached example.
Extract numeric value based upon ending characters.xlsm
