Indirect Addressing To A Sheet
Nov 16, 2008
I want to do is copy data to my working sheet (say sheet-1) from other worksheetx (say sheet-2, sheet-3). That's easy enough, but I want to be able to indirectly address "sheet-2" or "sheet-3" from a cell in sheet-1.
Look at the attachment. The data under cost A, cost B, cost C is from other sheets in the same workbook. I want to able to type in "sheet-2" in the first column and Excel to automatically copy over the data in columns 2,3,4.
I do not want a VBA solution. I know this can be done with built-in Excel functions because I did it before. Unfortunately, I lost that spreadsheet and I can't recall how it was done. I tried using Indirect function, but it returns a ref# error.
View 5 Replies
ADVERTISEMENT
Oct 16, 2008
I have a named block, used to sort a list of data. But I names the block one row higher to include the headers, so that inserts at the top row of the data would not fall out of the named range. Then I want to sort the data, but not include the header row.
What I want to say in the sort macro is something like:
cell_range( top_row(Data)-1, Bottom_Row(Data) )
Or, to have two names, with one defined in terms of the other:
Data_sort = Data_big - Toprow(Data_big)
View 4 Replies
View Related
Feb 24, 2014
From the Active Workbook how do I run these commands?
The syntax is way wrong, but will show what I'm after...
Code:
debug.Print Workbook("1990a.xls").Worksheets("Sheet1").cells(20, "A")
Workbook("1990a.xls").Worksheets("Sheet2").cells(20, "A")= "testing"
View 3 Replies
View Related
Jun 8, 2014
Is it all possible to address controls on a user form indirectly?
Thus instead of say
kv=me.textbox10.value
Could you do something like...
i=10
kfield="me.textbox" & format(i,"99") & ".value"
kv=evaluate(kfield)
View 1 Replies
View Related
Jul 18, 2006
how does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value
ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
View 3 Replies
View Related
May 9, 2014
I have multiple sheets as Name1 ; Name2 ; Name3 and each sheet have a value on cell A1 for any number let say between 0 to 100. On another sheet call SheetName, I have A1=Name1 ; A2=Name2 ; A3=Name3.
I have formula as:
[Code] .....
The INDIRECT formula work to refer each cell A1 in each sheet, however if the sheet name include a space then it won't work anymore. How to make it work with the sheet name with space?
View 5 Replies
View Related
Dec 12, 2011
I've tried to find the solution and the options I've tried I just can't get the code or syntax correct...
I have a workbook with 11 sheets in, 10 data sheets, and a summary
On sheet call "CB", I want to select any cell, then, via VBA, on sheet "Summary", in cells b4 to b14, where a4 to a14 have the sheet names, I want to reference the cell I selected, using the Indirect function....
something like
Code:
SelCell = Activecell.Value
Range("b4").Select
Activecell.FormulaR1C1 = "=INDIRECT(""'""&R[0]C[-1]&""'!SelCell"")"
I know it's incorrect.
View 2 Replies
View Related
Dec 18, 2006
In book3.xls, worksheet 'statistics_by_class_day', how can I rewrite the formula in C7 such that the count is based on number chosen in A3 and C3?
=SUMPRODUCT((INDIRECT("'attendance"&$A$3&"'!C:C")=$A$7)*((INDIRECT("'attendance"&$A$3&"'!I:I")<>0)))
In the formula, I:I refer to Day 4 of worksheet attendance9 (ie Column I), can I use INDIRECT() by referring to C3?
View 2 Replies
View Related
Mar 2, 2009
I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!$A$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.
View 7 Replies
View Related
Jun 21, 2007
IF(ISNA(VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))
It is the part regarding til indirect that gives me problems
This form I can get it to work
IF(ISNA(VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))
But here I need the Omklist.xls to be open. And that I dont want.
The what i want is that the user can use the sheet with out oping the data sheet..
View 9 Replies
View Related
Dec 7, 2006
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()).
View 9 Replies
View Related
Jan 10, 2008
I have a formula: ='Jan 7-11'!D10. I want to replace the tab name 'Jan 7-11' to change based on the content of F6 (which is a data validation for all tabs in my workbook).
View 3 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
Jun 9, 2009
I have this formula in excel 2007:
=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").
Blad is Dutch for sheet, by the way.
In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.
W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.
I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.
View 6 Replies
View Related
Mar 16, 2009
I need to ask user to input the row ("rowin") and column ("colin") address in order to activate a cell. The mose natural inputs from the user will be an integer for row and one or two alphbats for column. Say, for example, the user input (82, AY), how do I represent the cell in code? I know I want to say something like Range("AY82"). But I just could not figure out how to concatenate "colin" & "rowin" to make it AY82 to Excel.
View 4 Replies
View Related
Jan 16, 2009
I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.
The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?
View 3 Replies
View Related
May 21, 2014
Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.
View 5 Replies
View Related
Dec 23, 2009
I have a Formula question which I can't seem to figure out. I want to set a cell so that when two other cells are above 80 then the cell is green.
If one of the two cells is bellow 80, the cell turns to yellow and if both are bellow 80 then the cell is yellow.
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
Mar 14, 2014
I 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.
View 7 Replies
View Related
Jul 13, 2006
For 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.
View 10 Replies
View Related
Nov 6, 2007
Address(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.
View 9 Replies
View Related
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
View 9 Replies
View Related
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.
View 9 Replies
View Related
