Indirect To Non-volatile
Feb 26, 2010why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.
View 9 Replies
ADVERTISEMENT
Replacement For Using Indirect (Volatile)
Oct 30, 2009What can I use to replace the portion in red because it is volatile?
=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)
Non Volatile Replacement For INDIRECT In SUMIFS Formula
Dec 4, 2013Okay 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.
My original formula was:
=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")
However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:
=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))
However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.
Non Volatile Custom Function Acting Volatile
Oct 15, 2007I am using a linear interpolation UDF that recalculates every time a change is made to any cell in the workbook, whether that cell is pertinent to the calculation or not. Is this correct behavior?
View 9 Replies View RelatedDevelop An Indirect Indirect Validation Drop Down List
Aug 18, 2009I 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.
Non-volatile Timestamp
May 17, 2002I 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.
Cell With Day Of Month ONLY (and Volatile)
Jan 12, 2009I have searched around the forum but I could not find anything related. Like the title says, I need to have a cell that is the current day of the month and changes automatically each day.
View 2 Replies View RelatedVolatile References In Formula
Mar 4, 2010I 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.
='[39961(28-May-09).xls]Stats'!$B$1
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.
Non-Volatile Dynamic Named Ranges
Mar 27, 2008I 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?
=OFFSET(Query!$A$14,,,Height-13,19)
With Height being:
=MATCH(BigText,Query!$B:$B)
Creating Multiple Results From Volatile Functions
Aug 26, 2012I 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.
Volatile Formula Returning Date To Be Static
Sep 21, 2008I'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:
=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))
Is there a way to make up a vb macro to be able to make the Due Date output static, without affecting the formula?
Copy Volatile Numbers To Persistent Cell Based On Date.
Apr 21, 2009In 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.
User Defined Functions And Using Application.Volatile To Update Sheet Automatically
Jan 13, 2009I created a user defined function and using Application.Volatile to update sheet automatically.
But this slowing down my sheet execution alot.
When i remove that it doesnt take much time but no automatic update.
Using SUM And INDIRECT
Nov 18, 2013I 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.
How To Use INDIRECT
Mar 28, 2014I 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.
INDIRECT And IF In One
Jul 19, 2008I 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.
Lookup With Indirect?
Dec 5, 2013How 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 RelatedAlternative For INDIRECT
Jan 26, 2009I 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?
Indirect Sum Through Worksheets
Sep 4, 2009I 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.
Sumproduct With An Indirect?
Nov 16, 2009The 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?
Indirect Sum Between Two Tabs
Mar 14, 2014I am having some trouble with a handy formula I learned over this forum and its application between two tabs.
Referencing the attached workbook, the formulas in cell C6 & C7 are working for the end range I want, but the first section doesn't want to work. I'm not sure if it has something to do with the quotes (") or not.
Can You Use INDIRECT In 3-D References
Jul 13, 2006For example
=SUM(INDIRECT(C10))
where C10 would contain
="Sheet2:"&"Sheet3!"&"A"&ROW()
always returns #REF!.
However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
Indirect (Address ...)
Nov 6, 2007Address(5,$Z$5+60) appears to refer to the cell I want; however, I'm trying to use the Address function inside a Rank function and have tried it with and without the Indirect function (as shown below) and it doesn't work --
=Rank($BE$5,Indirect(Address(5,$Z$5+60)):Indirect(Address(1000,$Z$5+60)))
The range always comes back as 0.
Sumproduct & Indirect
Apr 20, 2007=SUMPRODUCT(--(A2:A1000=B1))
Column A has got random numbers, my formula attemts to capture the count of numbers as specified in B1, is there a way I can nest an indired t formula to mention in cell C1 ( > or < ) so it can look for numbers greater than the number in B1 or lesser as indicated
INDIRECT REF TO DEFINED NAME
Aug 14, 2007=INDIRECT("'" & A2 & "'!" & B2)
I am trying to use this formula to get to total of each month depending on A2. Cell A2 will have drop down of months names, that is Tabs names. I want B2 to have total of each month rather than cell reference, because Total may not be always in the same cell, we add rows if we add new account number or cost center.
So, Can I use name function instead of cell reference, for example April-total, May-total, June-total etc.
Sum Indirect With Variables
Sep 28, 2007I have several cells with defined names throughout a workbook which I need to be able to sum. The defined names all have the same naming convention (i.e. Assets575Total, Assets349Total, Assets286Total) where the numeric is an account #.
I have tried the follwing formulas below using Indirect, however, none seem to work:
{=SUM(INDIRECT("Assets*"&"*Total"))}
{=SUM(INDIRECT("Assets"&%&"Total"))}
{=SUM(INDIRECT("Assets"&"*"&"Total"))}
{=SUM(INDIRECT("Assets"&*&"Total"))}
{=SUM(INDIRECT("Assets"&%&"Total"))}
Indirect Using Various Sheets
Nov 16, 2007I have 40 sheets with info and 2 summary sheets. One summary sheet will summarise the data on sheets 1 to 20 and the other 21 to 40.
Im using the following:
=INDIRECT(ROW()-9&"!X10")
This works fine for my 1st summary sheet enabling me to display the vlaue of X10 in sheets 1 to 20 in D10:D29.
However in the 2nd summary sheet I wish to display X10 in D10:D29 but only using sheets 21 to 40.
Is there a way to eliminate sheets 1 to 20 and just use 21 to 40.
INDIRECT With MIN Function
Jan 25, 2008I just need to find the MIN of P6 to Px. Where x is a number that is from another sheet. (Lets say B3)
I thought this would be it, but its still not working, gives me #REF! error
=MIN(INDIRECT("P6:"& '[filename.xls]Sheet 1'!$B$3))
INDIRECT Across Sheets
Aug 14, 2008I have a sheet with tabs Jan - Dec
My goal is for a user to specify a 3 letter Month in Cell A1 (i.e. Jun) and for my sheet to calculate all cells C5 from Jan - Jun.
I have tried using the following formula =SUM(INDIRECT("Jan:"&A1&"!C5")), but the indirect function does not seem to span across workbooks!
Indirect Fields
Sep 29, 2008I am trying to figure out a solution and wondering what would work the best. Here is my situation. As an example, I have one big database with fields such as:
Item# Date Qty Price Cost ect...
1 3/4/08 3 $9.00 $7.00
2 9/5/08 5 $8.00 $6.00 ect....
This continues for up to 1000 lines from a database. I have this is a tab called "Database". From the data in the tab "Database", I want to be able to create 4 seperate reports.
The first report might only have the columns "Item #" and "Date".
The second report might only have the columns "Item #" and "Qty".
The 3rd with only "Item #" and "Price"
The 4th with only "Item #" and "Cost"
If I create a new spreadsheet called "Sales" and create the following:
ColA = Item #
ColB = Date