I am need to setup an IF Function to cover several columns. I need the function to be able to look at Column A, B, C, and D and see if any are blank. If any of these is blank I need the function to give "Pending". If all of these columns have a Y then I need the function to give me "Completed".
View 9 RepliesI have a list A2...A11
In cell B1 is the following formula: =SUM(IF(FREQUENCY(IF($A$2:$A$11"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),1))
Rvec is defined in NAME MANAGER as: =ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1
In cells B2...B11 is this formula: =IF(ROWS($B$2:B2)
What i need is someway of when ever these certain numbers:
75
85
87
95
are entered into any row on column 1, then are automatically blacked out and un readable untill the cell is changed to another number.
I have a workbook that has a cover sheet which contains the names of all the tabs in the workbook. I need to be able to change the names in the cover sheet and the names in the tabs to be change to whatever the corresponding cell in the cover sheet text is.
View 14 Replies View RelatedIs there any way that this bit of code can be combined to create a shape using dates that fall on weekends (that are listed in row2) and at the same time cover all rows up to the last Row used in a worksheet?
For instance, in the columns of Row2 we have many dates and we would have "16-Jan-10" in one column and "17-Jan-10" in another column (usually the next column) which are weekend dates. the shape should be created in the next row under the weekend date and cover the column all the way down to the last used Row (last Row contaning data).
With ActiveSheet.Shapes.AddShape( _
Type:=msoShapeRectangle, _
Top:=.Top, Left:=.Left, _
Width:=.Width, _
Height:=.Height)
.Placement = xlFreeFloating '
.Fill.ForeColor _
.RGB = RGB(Red:=192, Green:=192, Blue:=192)
End With
I wanted to work backwards from a weeknumber (as defined in Excel). In other words, given a week number (i.e. 5) what is the first date and last date of that weeknumber. These are the formulas that I came up with and they work fine.
Formula: [Code] ......
Ffor 1st of the week and
Formula: [Code] .......
For the last of the week.
Attached File : Weeknumber.xlsx
this is normally a very simple thing to calculate and I know how to calculate for speed, distance and time or SoDiT as we learnt at school..
I have a period of time where a distance in feet is covered. Lets say 1 hour 25 minutes to cover 12,600 feet, just over two miles.
What I need to solve for in this case is not speed exactly, but how long it takes to cover 10 feet in this scenario.
I start to do the math in excel and then the formatting jumps when I set to hh:mm:ss and I get confused whether I'm multiplying or dividing by 60 and it all becomes a mess.
I have tried to make cartesian coordinates (X)and (Y) using excel 2007. now I want to represent some shapes (circles, squares, triangles, and so one). My problem now is with formating those shapes. I use this path (Insert > Shapes),, but the shape I add cover the grid,
I want (for example) a circle but I need only its circumference, and without covering the grid by its area. the same for other shapes.
CK Nursery and Finish Inventories.xlsxAttachment 207447
I would like the "Finish Summary by Age" tab worksheet to look like the "Nursery Master Sheet" and "Circle K 1 - CD Farms Nursery" tab worksheets. I'd like the "Finish Summary by Age" tab worksheet to cover the majority of the paper like worksheets on the other tabs.
I have 36 pages of names, addresses & contact info ETC that I want to print. I have a printer that offers a cover sheet in the print setup.
My list of names addresses ETC takes up two full pages of paper for each name. There are 16 columns across the sheet that contain data for each entry. I would like to print this so that I can turn the pages like an address book and see all of the data for a given set of contacts on each two page spread. So what I need to do is print the first half of the contact info on the inside of the first page and the second half on the next page and so on.
How can I set this up?
I have attached a workbook with various worksheets. the aim being to create a cover worksheet called "averages".
Every few days i will manually add a new worksheet in the same format as "332" i had to delete most of the data from "333" to meet the file size limit but you should still get the idea. I would like the "averages" worksheet to work out an average rating and pull the data for all competitors from all worksheets.
I All my data is obtained from 2 columns, USERS & rating which i manually copy paste to a new worksheet every few days from that info i build my basic pivot table. maybe i can gather all the USER & rating columns on the same page and use a more complicated pivot table and thus do away with all the worksheets completely? But I do like being able to easily compare each individual rating against the other competitors for that particular day.
Attached File : competitor ratings1.xls
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work
unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
I have drawn up a simple summary to show what I am trying to achieve: ...
View 9 Replies View RelatedCalculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.
I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.
Does anyone have a suggestion as This problem is becoming quite frustrating.
Here's an example of how the spreadsheet looks
Forecast103050205040Inventory 20019016011090400Weeks Cover6.05.04.03.02.01.0
I am trying to take the tables you will see in the dummy page on the second and third sheet and have them output onto the first sheet based on the parameters of the drop down menus. Is there a way to have a button on the first page that controls a macro to do this?
View 4 Replies View RelatedI'm having a problem with the IF statement. In THIS pic you can see how I have my columns set up. I want to add the F columns to a total, but only if column G is "n".
I have watched a bunch of tutorials about it, but I just cant seem to get the dang thing to work. Any advice on the formula I would need to write to make it work?
I'm using excel 2007.
How I can apply an IF function for four or more columns. for example: if the value is the same in four or more cells, IF function can type OK, if not something else.
what I am doing is this : =IF(H2=I2=J2=K2=L2,"OK","Not Equal") but I always receive the value"not equal" even when the all cells have the same value. The cells values are found using formula so I did copy and paste value but I received the same result.
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
I have a spredsheet with multiple Alpha Numeric codes in one cell. I would like to seperate the codes but instead of placing them in the adjacent columns, like the text to columns function does, I want them to go to the preceding rows.
View 3 Replies View RelatedI have a list of items with a cell, however these are on seperate lines (using Alt+Ent) function. Example of entered text within the cell below:
_UN
_OD
_PN
_H
The beginning of each line will always start with an underscore ( _ ). The items within the list will either be 2 or 3 characters long (which includes the underscore).
The required output I'd like is (spaces used to indicate seperate cells):
_UN _OD _PN _H
I'm trying to use the 'Text to Column' function to solve my problem, however I haven't yet managed to get it to work. I've tried using the 'Fixed Width' function within this, however when I use this, it inserts an 'Enter' within the cell, which I don't want.
Does anyone else have a solution? Any help would be appreciated. Preferably I'd like this to be automatic using a formula, instead of me having to click the 'Text to Column' button each time.
(I'm using Excel 2007 if this makes any difference too)
A
B
C
Ap1
queens Carrots
Goja Apple
Ap2
fujja Apple
fujja Apple
Ca23
Goja Apple
queens Carrots
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
I'm looking to average the numbers contained within 5 different columns if the corresponding cell in a different column is blank.
Here's what I have currently (with column A being the conditional cells and D:H being what I'm looking to average)
=AVERAGEIF(A3:A26, "", D3:H26)
From what I understand, this function only allows me to average a single column. If this is in fact a limitation of excel and not just of my knowledge, is there a different way to calculate the average of D3:H26 when the corresponding cell in column A is blank?
I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.
View 5 Replies View RelatedOk this is what I need to do:
I have worksheet A with cell AC2 to enter a team id# like 51922 for example. In Cell B3 I want it search sheet11 for the team name according to the number and put the team name in B3. Sheet11 has 11 columns the first one list the 613 team names, the remaining columns have #'s like the example giving for the teams. What formula would I need to perform this function?
This is a football game the other 10 columns represents 10 worlds with identical 613 teams to control in each world, but the teams have different #'s to identify the world, team, and coach.
I'm comparing data from two columns like this:
FALSE
FALSE
FALSETRUE
FALSEFALSE
FALSE
FALSE
and my goal is to have a TRUE or FALSE in the third column for the following criteria: TRUE if left column equals TRUE or if left column equals FALSE and right column equals TRUE. False if else........
Macro for text to columns function (if this is the best way to go).
Our work report exports customer subscriptions which we call "role name". Our customers subscriptions have a valid and start date, but our customers also can purchase multiple subscriptions and we need to separate this data in order to report by subscription.
Our export has an * after each subscription, the date is in square brackets and each subscription is separated by a semi column.
So for arguments sake, let's say we have the following subscriptions:
subscription a
subscription B
subscription C
subscription D
if one customer has purchased four subscriptions on various dates, their exports may look like this:
Subscription A* [01/02/2012 12:00:00 AM]* [01/02/2013 12:00:00AM]; Subscription B* [01/03/2012 12:00:00 AM]* [01/03/2013 12:00:00AM]; Subscription C* [01/04/2012 12:00:00 AM]* [01/04/2013 12:00:00AM]; Subscription D* [01/05/2012 12:00:00 AM]* [01/05/2013 12:00:00AM]
I then use a text to columns function to separate by the * and the;
I then am left with a column for the role, one for the start date and one for the expiry date.
The roles are in alphabetical order, but sometimes some manual sorting needs to be done to delete the roles I don’t need and keep the ones I do. For example someone could have subscribed to subscription B and not in A that means that the first column may not have all the roles I need, it may be in the second or third column depending on what other subscriptions they have.
So not sure if text to column is even the best way to go?
This is the function I have now: =COUNTIFS(Sheet1!A:A,Sheet2!A2,(Sheet1!E:E),"*") and it works great, counting every cell with a value in column E.
When I make criteria range 3 another column (criteria also being"*") my numbers actually start going down. I think it might be averaging the two columns out or something but I just want it to count all the cells with values in it from multiple columns and give me a total count.
I have 4 columns total that I want to be able to get a total count from.
I have a spreadsheet that I am trying to automate, one of the tasks is to sum some columns namely columns O to T. Column B contains the exact number of entries in the columns O to T, therefore I thought that this would be a good counter. The Spreadsheet varies in number of entries every time. In this instance the rows are in total 200 inclusive a header row. ie r below is = to 200. I have tried to record a macro and from that I have tried to replace the pertinent values.
Sub Sel_Sum ()
Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim U As Integer
Range("B1", Range("B65536").End(xlUp)).Select
r = Selection.Rows.Count
s = (r - 1) * -1
t = r + 1
U = -1
Range("O" & t, "T" & t).Select
Selection.FormulaR1C1 = "=SUM(R" & [s] & "C:R" & [u] & "C)" 'fails here
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
End Sub
I have two colums of data with names in them, Column A and B. I want to see what names in column B also occur in column A and I want to put this function in column C. So for instance if I want to see if "John" is also in column A and if he is then I want column C to say yes beside Johns name in column B.
View 3 Replies View RelatedMy formula in sheet "A" in cell D7 is this
=IF(Activity_Log!C2="BH",Activity_Log!F2,"").
What I would like to do with it is fill in row D7 to AH7 with this formula without changing the row reference in the IF function, only the column reference.
On the Activity_Log worksheet, my data is listed from cell C2:C32, which is selected from a drop down list, and F2:F32, which contains the formula =(E2-D2)*24.
I've attached a sample : Sample_MonthlyReport.xlsx