I 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 RepliesI need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:
In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.
In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?
I have been trying to complete a excel spreadsheet that incorporates a VBA calendar. You will know the sort of thing that I mean, the user clicks on a cell and a calendar appears then the user clicks a date and the date is added to the active cell. The reason why I have to do this in VBA is because the finished file is going to be stored on my works server and it doesn't have the calendar activex add-in installed.
Well I have made a little progress. However, I have now become stuck. the link below will give you the file that I got so far. If you extract the file then click on cell A1 a calendar will appear. However, you will see that when you try to change the month it is stuck on January and I do not know how to cure the problem.
I should add that the code that is written within this spreadsheet was not written by me. It was originally written by another person whom I am no longer able to contact. Unfortunately my knowledge of VBA is not that of the original author.
I 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))
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.
agent rank score supervisor
A B C D
1 leo 1 99 jim
2 bob 3 68 jim
3 sam 1 73 ann
4 tim 2 84 adam
5 alf 2 55 ann
6 dan 3 42 adam
7 will 2 79 jim
8 sara 1 91 adam
9
10 Rank: 1
11 Supervisor: adam
12 Agent: " "
I 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‎
I 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 View RelatedI 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).
I have the following formula, which works; however I need to make it dynamic.
View 7 Replies View RelatedI 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)")
Always 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.
In 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))))
I 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 Relatedhow 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
I 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)]
I 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.
I 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
My 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.
i 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
I 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.
I 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:
I 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).
I 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.
I 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?
Cell 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()).
I'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(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.
I 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] .....
See 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
A 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)