Combining OFFSET / Indirect And IF Functions
Oct 25, 2013
I roughly know what my equation should look like, but it keeps failing. Its all part of a wider spreadsheet but I will only put down that bits that impact this part of the sheet.
In my cell I need it to input a value from a different spreadsheet. The input value is text.
I currently use this formula and it works perfectly for what I need.
=(OFFSET(INDIRECT("sheet2!D"&$A7),R$6,25,1,1))
This would a return a variety of results such as "excluded","compliant","non-compliant".
I would like to arrange an IF function so that if the answer is Excluded to keep it as Excluded. But anything else to use this formula instead.
=(OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1))
View 3 Replies
ADVERTISEMENT
Jul 15, 2006
Can someone help with this formula,
Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24
Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247
This is what I've got
=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))
View 9 Replies
View Related
Dec 9, 2013
I have an INDIRECT function set up which should, in theory, concatenate data from a date range listed in another cell to correctly reference an external spreadsheet, which is named based on the date range it is generated for. The issue I'm running into is that the program which generates the external spreadsheet uses an MMDDYYYY format when naming its output file. This works fine if the month and day are both 10 or greater, but if they are less than 10, it doesn't work - for example, my INDIRECT function would return the date 5/3/13 as 532013, whereas the file name would be 05032013. I was contemplating using a series of IF statements to fix this, but there has to be a simpler way. Here are the relevant functions.
Function which generates date range (later used in INDIRECT function):
=MONTH(MIN(TransData!A2:A9882))&"/"&DAY(MIN(TransData!A2:A9882))&"/"&YEAR(MIN(TransData!A2:A9882))&" - "&MONTH(MAX(TransData!A2:A9882))&"/"&DAY(MAX(TransData!A2:A9882))&"/"&YEAR(MAX(TransData!A2:A9882))
TransData is a different sheet within the same workbook where the various dates are stored in column A. I found that I needed to format the formula like this in order to get it to return actual dates instead of date sequence numbers.
=AVERAGE(INDIRECT("'[DCQA_"&SUBSTITUTE(B4,"/","")&".xlsm]Total'!$C$3:$C$46"))
Cell B4 is where the date range formula listed above lives. "DCQA_" is the beginning of the filename of the external sheet, followed by the date range in MMDDYYYY - MMDDYYYY format. So, the sheet for 12/2/13 to 12/7/13 would be named DCQA_12022013 - 12072013. I'm unfortunately unable to change the way the external program names its output sheets, so I have to fix this from within my workbook.
View 2 Replies
View Related
Oct 2, 2008
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")
But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.
View 2 Replies
View Related
Feb 1, 2009
I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.
New York (column header)
Wages
Lights
Net Profit
Boston
Lights
Wages
Sales
Net Profit
How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.
View 9 Replies
View Related
Jul 23, 2014
I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")
Here is a sample table with showing the data and result
4
5L
4,5L
5
6R
5,6R
5
7L
5,7L
6
10R
6,10R
6
3L
6,3L
View 1 Replies
View Related
Jul 6, 2006
The format for combining COUNTIF and AND in a
single function - I can't seem to get the format right.
What I want to do is COUNTIF range 'Issued_by' = A8 AND range
'Period_all' = 1
View 14 Replies
View Related
Nov 7, 2012
Although I have seen lots of formulae to total a dynamic column, I found a brilliant formula on the Internet, as follows
HTML Code:
=SUM(OFFSET(INDIRECT("R1C",),,,ROW() -1))
The problem is I cannot work out how to adapt it to total across rows (which will expand with new columns).
View 5 Replies
View Related
Apr 4, 2014
Basically I have an equation:
=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))
Where the user inputs the number of rows used in A8.
The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
[code]....
View 3 Replies
View Related
Oct 13, 2006
Say I have written three user defined functions: first(a,b,c); second(a,b,c);third (a,b,c), which all give value for a,b,c inputs. How do I write a VBA program that uses the functions above? Say the code is called Combine(), after the user have typed in the value of a,b,c, the function Combine=first+second+third.
View 4 Replies
View Related
Dec 17, 2012
I am having an issue when I try a chart a named range. The named range "Refers To" is
Code:
=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)
&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1))))
As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.
The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.
The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?
View 4 Replies
View Related
May 13, 2013
On Sheet 2 I am looking for a formula to find Dog1 in a cell on a different sheet and then to equal a cell 2 columns to the right of where ever it found Dog1. I cannot make it so that it simply equals a certain cell all of the time. It has to be able to move in sheet 1. For example sometimes Dog1 will be on row 5 and sometimes it will be on row 7. So the equation I am looking for needs to be able to work no matter what row it is on. I have tried a couple VLOOKUP equations but they are not working because like I said, Dog1 is not the only text in that cell.
View 3 Replies
View Related
Feb 13, 2007
i need to get an equation correct for a scheduling application
assume $f24 is a task frequency and U24 is our current cell location
=IF(OFFSET(U24,0,(-$F24+1),1,($F24-1))"","","X")
i am asking if (the range of cells to the left of current cells (range being task frequency -1 columns wide) are empty - and if so - put an x - if they are not empty - then leave it balnk.
i can get it to to work for a 2 weekly schdule but need it to work upto 52 weeks
i have a simple spreadsheet with the problem clearly defined for anyone who can help
- beer in it for anyone who can help - or can pay via paypal if required
View 9 Replies
View Related
May 31, 2007
Is it possible to nest the two functions of match & offset. I am working with two worksheets. One is a constant the other is building. I want to match a text from the first spreadsheet in the second sheet (otherwords I want to find that exact within the other spreadsheet) then I want to retreive the offset value to the right of the matched cell and place it in the constant worksheet where the formula is.
For Example
Sheet 1 Sheet2
Company: First Name: Company: Name:
Adamack's ? Adamack's Patrick
So if I put the formula where the ? mark is, I want to match the first company name on sheet 1, from the range in sheet 2, then I want to get the offset from that match 0,1 to give me the name patrick.
View 9 Replies
View Related
Aug 26, 2012
I have three columns of data in columns A,B,C - there are formulas in these columns that either produce a numeric value or return blank.
I have created Dynamic Named Ranges for each of them and have entered the following formula:
=OFFSET(Working!$A$1,1,0,MATCH(1E+305,Working!$A:$A,1))
My issue is that this formula is looking one row past the last numeric entry in the column (a blank) which is leaving a blank space when I graph this data.
View 3 Replies
View Related
Jan 7, 2014
I am in the process of setting up some graphs. The graphs will show the last 6 months of data so they move as each month is goes. I saw a slick way of doing this using the count function but this did not quite fulfill my needs. What I was looking for is to be able to input the start month in a cell. The cell would be part of the function within the formula. I created a simplified version of the spreadsheet below.
Spreadsheet.jpg
Then I created my names using the ctrl-F3
Names.jpg
I then created the graph I wanted and wanted to use the formula =SERIES(Sheet1!$B$3,!chtCat,!chtIssuesReported,1). This kept getting an error indicated nothing really. It basically says there is something wrong with my Series function. I have a working version of a similiar worksheet I have been basing my entries on. They look almost identical but mine is failing. I also created a new file and tried this and it still fails. I can run the formula evaluator within Excel 2010 against the !chtCat and !chtIssuesReported names and they both return the correct value (which equals areas on the spreadsheet). I have also tried to enter the spreadsheet name and a tab in front of the names to get them to work and still get an error. Basicall I am trying to create the graph below (this grpah is using the hard coded locations)
Graph.jpg
View 2 Replies
View Related
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
View Related
Aug 18, 2009
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.
View 3 Replies
View Related
May 31, 2014
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
View 3 Replies
View Related
Nov 1, 2006
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
View 9 Replies
View Related
Mar 14, 2008
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
View 9 Replies
View Related
Oct 26, 2008
I have attached the .xls file.
The basic setup is as follows:
(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)
(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.
For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc
As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.
(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.
NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.
OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.
I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.
HOWEVER, what i WANT is the output as shown in columns Y-AF.
So as you will notice, there are 2 problems:
(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.
How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?
(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.
Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.
View 7 Replies
View Related
Mar 22, 2013
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria)
Search range is: B1:D400
(for example)
Cell B40 = samuel
C40 = Driver
D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1()
Range("B1:D400").Select
Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 2 Replies
View Related
Nov 18, 2013
I am using these formula
=SUM('18'!N:N)
=COUNT('18'!A:A)
I need to enter the sheet name in cell A1 instead of the sheet number 18 in the formula using INDIRECT for both the formulas.
View 5 Replies
View Related
Mar 28, 2014
I have the following formula:
= SUM ('03:08'!D4, '03:08'!E4)
the 01, 02 ... 020 are the names of the sheets. How can I modify the formula so that I can use other sheet names. Name sheets whose cells I want to be myself in C4 and D4. I tried INDIRECT but I don't know for several sheets.
View 6 Replies
View Related
Jul 19, 2008
I am using the below formula to return values from a seperate worksheet.
=INDIRECT("'[Test File.xls]Test Data'!A"&A4)
These values are text, numerical and dates. Sometimes there are no dates in the source worksheet to return, and I end up with 00/01/1900.
Is it possible to leave the cell blank if there is no value to return???
I had a stab at trying to nut it out, but it was a Friday afternoon, head was mush, and the pub was calling.
View 9 Replies
View Related
Dec 5, 2013
How could I create a formula that would look up based on month, category, and most importantly an indirect? I have attached a spreadsheet, the indirect is in K13 (and could be Quarter 1, Quarter 2, Quarter 3, Quarter 4) with matching data in "Sheet 2".
View 3 Replies
View Related
Jan 26, 2009
I have used the function INDIRECT in 1 of my files.
The disadvantage is that both files (source and target) have to be open.
Is there a substitute for INDIRECT that works with a closed source file?
View 11 Replies
View Related
Sep 4, 2009
I am trying to sum through multiple worksheets but maintain flexibility using INDIRECT but it is not working!
I have a worksheet for each month of the year Jan - Dec with a financial result. In order to get a Year To Date figure I would have a formula such as:
=sum(Jan:Jul!B3) for a July YTD.
However, I want to maintain flexibility such that I can enter the worksheet name in cell A1, e.g. Sep and then have a formula such as:
=sum(INDIRECT("Jan:"&A1&"!B3"))
Thus allowing me to generate the correct YTD at any point. All I get is a #REF error.
View 9 Replies
View Related
Nov 16, 2009
The following formula sorts for specifics in the sheet named 200910 in the specified ranges in columns A and D to return a total found in column AB. This works just fine.
=SUMPRODUCT(('200910'!$A$2:$A$1777="Countrywide")*('200910'!$D$2:$D$1777="Claims-All Products"),('200910'!AB2:AB1777))
What I am looking to do, instead of telling excel what sheet to go to, is insert this: =INDIRECT(TEXT(Y10,"yyymm")&"!ab1749") to find the matching sheet name to the date that resides in cell Y10.
These both work separately on their own to return the needed value. How do I put them into one formula without telling excel what sheet to go to (1st formula) and specifically what cell to go to (2nd formula) because the cell location may change and I want to completely automate this?
View 3 Replies
View Related
