Getting Data Using Indirect Function?
Jan 18, 2014I have some data on a sheet and I want it on other sheet without using lookup function. Only "Indirect function need to be used". find attached the excel sheet.
View 1 Replies
ADVERTISEMENT
Data Validation - Indirect Function
Jun 9, 2009i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation
eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams
but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?
eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players
Dependent Data Validation - Indirect Function
Apr 10, 2014See attached. I cannot get my indirect function to work in Cell K2 based on cell J2
Data for the Validation is in cells W1 to AC14
I want to do data validation in Column K based on Column J, but cannot get the indirect function to work to past into the data validation tool
I have named all my ranges.
AM Call Log.xlsm
Data Validation List - INDIRECT Function
Mar 28, 2014I am using a data-validation-list with =INDIRECT(B7) as the source. It works as expected except for after the file has been closed and reopened. When it is reopened, the source is changed to =INDIRECT(#REF!).
I have tried =INDIRECT('Worksheetname'!B7) but same result.
INDIRECT FUNCTION - Make Data Stay Permenantly
Dec 9, 2009I have approx 500 workbooks which contain custoemr details and I need to extract certain info (postcode, name, contact, phone number etc) and place it all on 1 spreadsheet. I was originally going to source data from unopen workbooks, but have realized I only need to do this task once, and I can maintain it from then on...
I have created a spreadsheet (attached) which uses INDIRECT formulas to retrieve the data. When I type in the file name in column A, Columns B to K fill themselves in. What I want to know is:
Once I open say, 10 Workbooks, TYPE the name in manually and all the other cells have filled themselves, I want to somehow KEEP the data there, rather than it return a #REF error when I close the workbooks to open the next batch.
Also, if you look at my workbook, to the far right is a range of data which I use in my INDIRECT formulas. Once I have filled all the cells with the relevant data, I want to be able to Filter my spreadsheet by Date Ascending. BUT I cant seem to do it because it includes my INDIRECT range in the filter and mess's up the formulas then I have tried Locking & protecting workbook, with Autofilter & select cells etc TICKED for users to use, but it still tells me to unlock workbook when I try to filter.
Lookup Data From Multiple Worksheet Using Indirect Address And Match Function
Sep 28, 2011determining the correct formula to lookup various details from multiple worksheet:
Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000
Sheet2: Data
AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
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))
Using Indirect Function With Comboboxes
Apr 22, 2011I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?
I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).
Attached Files
File Type: Corp MASTER (3).xlsx‎
File Type: Quote form (2).xlsx‎
Using INDIRECT Function For VLOOKUP?
May 8, 2014I am having issues using the INDIRECT function to lookup data from a sheet with the same name as that appearing in a given cell. For example, in cell D27 i have the text "S1_358_810" (Not including quotations). I also have a sheet named "S1_358_810". My formula is as follows;
Formula:
[Code] .....
However this is returning #N/A. There is a list of numbers in sheet S1_358_810 in column N and from that I want the value in column Q (thus 17).
How To Use Indirect Function Within Sumproduct
Dec 29, 2009I have the following formula, which works; however I need to make it dynamic.
View 7 Replies View RelatedINDIRECT FUNCTION With VARIABLES
Feb 14, 2007I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....
I have this formula: but it does not work
I'm not sure how to include the VLOOKUP function in my argument to
[/quote][/code]=INDIRECT(CELL("contents",B3)&"!&Address(VLOOKUP(B16,'1'!B:AE,match(RAY_ANALY!D2,'1'!B2:AE2,0),0)")
Indirect Function Syntax
Feb 15, 2007Always have problems getting my head round the syntax of the indirect function and am unable to find anything similar that's been asked.
I want to perform an operation on two numbers where the user selects which to use (add, subtract, multiply or divide) entered into another cell like this:
******** ******************** ************************************************************************>Microsoft Excel - 200701 - LCC.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC3C5=
ABCD14+5*2****3Normal*9*4****5Indirect*#REF!*Sheet3*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Indirect Function #REF! Error
Mar 1, 2009In Excel 2007, the following cell Q14 CSE formula accurately returns the row number of the first negative value in the column P array P14:P102.
{=MAX(ROW(INDIRECT(ROW()&":"&MIN(IF(P14:P102="",ROW(P14:P102)))))*(INDIRECT("P14:P"&MIN(IF(P14:P102="",ROW(P14:P102))))
#ref Error With Indirect Function
Jun 24, 2006I have been searching through your forum but I can't seem to find the solution to my problem. I have two sheets: On one sheet in cell b2 I have a validation list whose source is =Indirect(Subgroup) which gives a #ref error. However when I evaluate Subgroup, I get a legitimate range. I have attached an example of what I am trying to do.
View 9 Replies View RelatedUsing INDIRECT Function With Double Quotes?
Aug 1, 2013how to use the indirect function to pull data from a pivot table. This is the formula I am trying to recreate: =GETPIVOTDATA("sum of BOE",$A$14,"CLASS","PROVED","Years",2013)
I can't seem to get the indirect function to work properly with the words in double quotes, such as "PROVED". How do I format that part of the formula properly?
This is as far as I can get. =GETPIVOTDATA("sum of BOE",INDIRECT($M13),"CLASS","PROVED","Years",$N13) $M13 refers to $A$14 and $N13 refers to 2013
Using Indirect Function In Vlookup Should Give Corresponding Value For The Particular Name?
May 11, 2014I have two workbook, test1 (master file) and the other one is test_10_05_2014 with 100 some of them being updated daily. The last portion of the file "test_10_05_2014" is the date and we have different file on daily basis.
using Indirect function in vlookup should give corresponding value for the particular name. Formula used is giving error,
[VLOOKUP(A2,Indirect(" ' "&E1&" ' !$A$2:$X$1000),6,FALSE)]
Using INDIRECT Function In Multiple Rows?
May 16, 2014I am building an attendance spreadsheet and trying to copy and paste all of the names from Sheet 1 to Sheet 6 for a different purpose. Thing is I need the names to match so I'm not having to copy and paste every time my data changes.
My problem was that when I insert a row into sheet 1, sheet 6 accommodates and then I'm missing data and have to insert a new line manually.
At first I tried using absolutes ($) to fix the problem, but that's a different ballgame.
I've discovered INDIRECT and so now use this formula: =INDIRECT("'DIRECTORY 2014'!B5"). It works.
But I have almost 300 entries. Is there a way to expedite the process without having to change each entry? I don't want to have to retype the function on every cell.
Can't Get Indirect Function To Work In Menu
Dec 27, 2013I have a series of menus in column C. I need column D to be able to access them and return the proper data.
I am using =INDIRECT(SUBSTITUTE(10C," ","")) and keep getting error after error.
MainstreamProductMgr2013-1.xlsx
Using Indirect Function To Sum Across Multiple Sheets
Jan 19, 2009My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:
=SUM('Jan-04-08:Jan-16-08'!A1)
But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:
=SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))
where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.
Hiding !REF# Error Using =indirect() Function
Jul 7, 2009I am setting up a summary sheet that contains =indirect() functions for workbooks that don't exist yet.
I would like some kind of function that returns the =indirect() function correctly if the workbook exists and just a 0 or blank if the workbook doesn't exist.
I would like to have the indirect function in all of cells that as soon as someone creates a workbook it will update the summary sheet.
Trailing Covariance With INDIRECT Function
Oct 21, 2008I have a sheet that has consecutive dates in column A, from newest to oldest. I column B I have some stock returns. In column C I have some other stock returns. In D1, I want the covariance of the 2 stocks over the past x days (i.e. covar(B1:Bx,C1:Cx)). In D2, I want the covariance over x days as well, but only up to the date in A2 (so, covar(B2:B(x+1),C2:C(x+1))).
I tried this, but it doesnt work:
Replace INDIRECT With INDEX Or Another Function
Jul 4, 2009I am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet.
On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines:
=IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!$C$2 & 'Detailed net pay calculations'!D1 & "'!$B$43:$Y$52"), 4, FALSE), “Do something”, “Do something else”)
The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!$C$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1).
My query
The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).
Indirect Function For Lookup Table
Oct 18, 2011I simply want to use the indirect function in vlookup formula in cell B2 (sheet11) is the sheet name I want to use for the lookup table.
=vlookup(B1,'[Alan.xls]sheet11'!$A$5:$F$19,4,0)
=vlookup(B1,'[Alan.xls]indirect("&B2&")"'!"$A$5:$F$19,4,0)
But doesn't work.
Creating A UDF That Incorporates The Indirect Function
Apr 26, 2008I would like to create a UDF that incorporates the Indirect Function. So if this is possible, I'd like to put in any cell..... =Tev(A2,D6)...... "Tev" would replace the Indirect function in the UDF script. In cell A2 would be the name of the sheet that I want to pull from. And Cell D6 would be the cell from the other sheet that I'd like returned.
View 9 Replies View RelatedWorksheet Reference In Indirect Function
Feb 22, 2010I have a worksheet with a name that inckudes a hyphen, e.g. 1234-5678.
And I have the worksheet name in a cell, say, I!.
Using
=INDIRECT(I$1&"!YADDA")
I get a REF error because of the inclusion of the hyphen in the worksheet name.
How can I fix this without eliminating the hyphen?
Indirect Function: Use Cell For Sheet Name
Dec 7, 2006Cell F4 contains a worksheet name which was “calculated” by an IF function (IF something, then ‘T2’, otherwise ‘T3’).
Cell F5 must then look at either sheet T2 or sheet T3 (depending on what it is told to do by reference to cell F4 just above) and report what it finds in cell A1 there.
Clearly I must use the INDIRECT function. But I have tried every imaginable combination of single and double quotation marks to produce the equivalent of
=T2!A1
all to no avail.
Is it something to do with letter and number combinations?
Alternatively, how else should I achieve what I want?
PS F4 could have been “calculated” by any other means, e.g. =MONTH(TODAY()).
Indirect Function In Picture Formula
Mar 31, 2007I'm in the middle of making a yahtzee game in excel. Does anyone know if it is possible to use the indirect function in the formula of a picture. I have embedded 6 bitmap images of dice on sheet 2 (worksheet name: "Dice Images"). I took a picture of one of those dice and pasted that picture on sheet1. The formula in the picture is: ='Dice Images'!$A$1
This means that it is refering to cell A1 of the "Dice Images" worksheet of which contains the pasted bitmap of the dice image. I was wondering if I could use the INDIRECT function instead of the formula above but the following returns an error:
=INDIRET("'Dice Images'!$A$2",true). It returns the following error: "The text you entered is not a valid reference or defined name". It doesn't seem to like the INDIRECT function for some reason, unless I am doing something wrong.
Indirect Function Dynamic Text
Jun 1, 2008=INDIRECT(CHAR(39)& F2 & CHAR(39) & "!BC7")
I would like to make BC7 dynamic. In other words, the text in this formula, BC7, should come from a cell A4 which today says BC7 but next month the value in A4 will say BD7.
Using INDIRECT Function To Create Custom Validation
Jun 28, 2013I have a worksheet that uses the INDIRECT function as part of cell validation to generate a custom 'name' range, this name then references a bunch of sheets that contain the actual range where the values for the validation list are stored. For example: ValidationExample.xlsx
Name = Color
Values = Blue, Red, Green
Name = Shape
Values = Square, Circle, Triangle
So what this allows me to do is in the first cell, I can define two options such as Color and Shape. Once one of these is selected, the next cell will have a formula for the validation as "=INDIRECT(A1)" then I will define two named ranges called 'Color' and 'Shape'. This will then show me the list of items in the cell based on previous selection. An example of this is attached to this posting above.
When the formula is written into the validation, an error message is generated saying that the formula will generate an error. Even with this message, the method works effectively to provide a blank list if nothing in the first cell is selected, or a list dependent on the selection of the first cell.
In this case, what I want to do is generate a macro that will populate the validation for a cell when new records are added. This won't always copy down from previous cells because the way I add records is through use of a macro and I generally find using the format painter in vba to be a fairly sloppy way of getting formats from other cells. So I go through a series of validation additions to each cell in the worksheet to get this validation created (I have no written this into the workbook attached).
VB:
Dim i As Long
Dim lastRow As Long
Dim sht As worksheet
[Code] .....
As I read in previous posts, Formula1 cannot contain an actual formula, only names and lists of items. Since each of the cells will have a changing reference, thus the INDIRECT function would need to change to reflect this, The 'Secondary' name consists of the following:
"=INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN()), 0,-1)))"
Which of course does not work due to the error when using INDIRECT in the first place (though the Address() function appropriately finds the cell with the validation in it's appropriate address). I can think of other ways to do this, such as having validation lists change in with VBA on a SelectionChange or Change event, but that's a lot of code that is subject to change when the named ranges have changes to them (which is fairly frequent). Is there any way to get the .Validation.Add method to ignore errors in the name supplied to it.
For reference, my code is this:
VB:
Private Sub cmdRefreshValidation_Click()
'Re-enters validation parameters to all cells to allow selections
Dim i As Long
Dim lastRow As Long
Dim sht As Worksheet
[Code] .....
Using Indirect Function To Skip Columns From Different Worksheet
Feb 9, 2012A post about skipping rows is very similar to my situation, but I'm stuck on how to translate that for columns. (Filling formulas but need to skip cells)
I'd like to transfer information from a worksheet called "Raw Data" to another sheet, skipping every 5th column. I'd like to go from "Raw Data" B12 to a new sheet C6, then from "Raw Data" G12 to D6 in the new sheet, and so forth.
I figured out that I'll need to use COLUMN()*5-13 as a component of the function, but I can't seem to figure out how to format the name of the worksheet correctly, with the ampersand and quotation marks.
This doesn't work at all, but I think it's in the ballpark...
=INDIRECT("Raw Data!"(COLUMN()*5)-13)&12)