Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.
I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
I have a problem similar to Ozone64's posting of 4/23 that I am trying to generate automatic time/datestamps in a cell whenever a value is entered in an adjacent cell. I am currently using the function =IF(A3"",NOW()) but the problem is when data is entered in subsequent rows the previous timestamp is overridden with the new timestamp. Thus at the conclusion of data entry every row ends up with the same date/timestamp as the final entry.
I have a worksheet that will be referencing data from a 2nd open workbook. Although the 2nd workbook will contain common worksheet names (eg stats), the file name will be volatile.
Consider the formula in wb ws1 cell A1 used to copy a value from wb2 (39961(28-May-09).xls) ws1 (stats) cell B1 if workbook 2's name was static.
In my case, the reference file will always be different and must be substituted in the forumla above (red portion). How can I adapt my formulae recognize the available workbook. I have the file name available now in cell C4 of WB1.
I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.
Can anybody provide a non-volatile alternative to this?
I have a model that does a projection for 36 months ahead on a spreadsheet. The projection depends upon a randomising "volatile" function. When I press recalculation a new projection is generated. I want to capture the set of numbers that appears at month 36 through repeated recalculations written to a separate sheet. I want around 500 of these numbers.
I have no experience with macros so doing it with preset functions would be preferable. If it has to be a macro, the easier the better. If invoking the spreadsheet and outputting to a flat file is easiest that is fine too.
I've posted this question in another site http://en.allexperts.com/q/Excel-105...xExp_72253.htm , but i haven't gotten any answer. I'm attaching an example of the file to show an idea of what i'd like to do, please check first. As you can see, i have a Due Date and Priority columns. The Due Date column has a formula, that when i choose the Priority level, it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. This is the formula i have in place for the Due Date:
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.
Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
In the sheet I am working on I have some numbers that represent todays activity. These numbers are volatile and reset when the date advances. I have a column on another tab that has all the days of the month as F4:F33
The results I want will go in the G4:G33 column. C2 is Today() to avoid too many Today()s. Active!J10 is the value I need in the G column.
Basically what I need is the value from Active!J10 to be copied to whichever cell corresponds to the date next to it in the G4:G33 and then stay there when the date advances
The formula I am using for the g range is : =IF(F24=C2,Active!J10) =IF(F25=C2,Active!J10) =IF(F26=C2,Active!J10) And so on down the column.
I dont mind the FALSES for future dates, but the current one changes to false upon the advancement of the date. Basically, I need a running log of the final numbers going down the G range and for those values to stay there when the date advances.
I'm trying to take a random sample without replacement. The sample is also partially representative, e.g. if a certain proportion of the data are Class A and a certain proportion is Class B, that proportion is maintained in the sample. After the proportions are set, the observations within each class are randomized.
Is there anyway to sample a number of rows, where the number of rows is based on the value in another cell, without having to use a lookup function or index match? Sorry if the question is a little unclear; please let me know if you need any clarification.
I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......
In the attached spreadsheet, I want to do a running average of the "To" column (column G) for each company throughout the 8 year period. The only problem is I want to do it with replacement based on column D, meaning if the same brokerage firm comes out with a new announcement, it will replace the value of the old one in the running average calculation.
Here is an illustration of what I mean:
Looking at Company A between row 2 and 12, the value for Consensus in row 2 should be the average of Column G in row 2, row 3 is the average of Column G in row 3...row 11 is the average of Column G in row 2 to row 11, but row 12 will be the average of Column G in row 2 to 12 be excluding column 5's value because both column 5 and 12 are issued by the same brokerage.
I want excel to replace new recommendations by brokerages over the old ones in the running average calculation.
I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
I am trying to search a folder that is selected by the user for all .jpg files, and then place all those pictures onto a certain spreadsheet within the excel file. It also formats the pictures. Following is the Private Sub BatchProcessThumb2x3() Msg = "Select a file containing the photos you want to insert." Directory = GetDirectory(Msg) If Directory = "" Then Exit Sub If Right(Directory, 1) "" Then Directory = Directory & ""
On Error Resume Next With Application.FileSearch .NewSearch .LookIn = Directory .Filename = "*.jpg" .SearchSubFolders = False .Execute
' Select begining range Worksheets("Thumbnail (2x3)").Select Range("B4").Select ' Loop through all files and process them
I also have the following code in my worksheet. I don't know if anything here is causing it to take so long to "calculate cells." Also my code just stopped coloring the row ranges and I not sure what cause it to stop working.
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "O:O" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target 'Begin coloring row ranges based on these requirements If .Row > 3 Then If Me.Cells(.Row, "O").Value = "" Or Me.Cells(.Row, "O").Value = "O" Or Me.Cells(.Row, "O").Value = "H" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0 End If.................
In the following thread, richphillips wonders about Application.Filesearch Application.filesearch In Office 2007
Is there a replacement for this? I have several macros that use Application.Filesearch and I would like it to work not only with Excel 2003 but also with Excel 2007. This is the function
Function CreateFileList(FileFilter As String, _ IncludeSubFolder As Boolean) As Variant ' returns the full filename for files matching ' the filter criteria in the current folder Dim FileList() As String, FileCount As Long CreateFileList = "" Erase FileList If FileFilter = "" Then FileFilter = "*.*" ' all files Debug.Print CurDir strFolder = BrowseForFolderShell(, , , 0) If strFolder = "" Then MsgBox "You Cancelled" Exit Function End If............
If InStr( Cells(i, 3).Value, "Other") > 0 Then _ Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value
This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?
Im trying to work out how to write some code to find and replace from a list.
I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.
Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.
I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...