Lookup Across A Sheet VBA Indirect?
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
ADVERTISEMENT
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
Oct 18, 2011
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.
View 5 Replies
View Related
Jan 17, 2014
I am trying to use the INDIRECT function to look up values on a range of other worksheets. I have a column of data in col A which is essentially a lot of different worksheet names. On each worksheet I need to use VLOOKUP to find a value.
Easier to show formulas. This is an example of what I want to recreate:
=VLOOKUP(O2, test!B3:C13,2,FALSE)
I want to replace the text "test" with the text in column A. So the first INDIRECT formula looks like this:
=INDIRECT("VLOOKUP(O2, " &A1& "!B3:C13,2,FALSE)")
Which to me looks like it should work but I get a #REF! error. I've tried various permutations, e.g. having the INDIRECT part of the formula located in a different place (next to the "test" text) but run in to similar issues.
View 5 Replies
View Related
Sep 28, 2011
determining 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)
View 9 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
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
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
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
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Apr 6, 2008
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
View 10 Replies
View Related
Jan 11, 2007
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
View 2 Replies
View Related
Jun 22, 2006
I am trying to find the code that will help me look in column A in sheet 2 and match it to Col B in sheet 1. If match is found it will then Bold all the values in that row.
I have attached the excel file
View 4 Replies
View Related
Mar 26, 2014
I have a sheet of unstructured data, data that cannot be easily turned into structured data.
What I would like to do is find a way to search across the sheet for a value and return the value that is found one cell to the right.
I cannot use, as far as I am aware, VLOOKUP or HLOOKUP as I have no idea in which column or row they are to be found. INDEX and MATCH, I can't get to work.
View 14 Replies
View Related
Feb 15, 2012
I have sheet 2 with a list of numbers/text in column A and column B.
I need a code that will look on sheet 1 to see if both the numbers are in a specified range and highlight the row if they are not the example may explain better.
As you can see on sheet 1 the word Test in C has in AB the words Today, Yesterday and Friday but because Tomorrow is next to Today on sheet 2 it is coloured in because it is not in the range according to column C.
The word Test2 in C has Monday and Tuesday in AB and because they are next to each other on sheet 2 that does not need to be coloured.
Finally Test3 has July and August within the range but no September according to sheet 2 so needs colouring in.
And so on down the file which is about 20000 rows.
Sheet2
AB1TodayTomorrow2Monday Tuesday3August September
Sheet1
CAB1TESTTODAY2TESTYESTERDAY3TESTFRIDAY4TEST2MONDAY5TEST2TUESDAY6TEST3JULY7TEST3AUGUST
View 9 Replies
View Related
Feb 22, 2014
I am trying to construct a way to return a set of values from multiple sheets onto one overview sheet, based on just changing a week number in one cell. I have attached a basic form sheet.
In the "results" sheet I would like to change the week number 1, 2, etc and with that change, return the values in C9, C11, F11, J11, M11 to refer to the worksheet of that week number
View 7 Replies
View Related
Jun 23, 2009
I have a long list written twice in 2 worksheets worksheet A has a list with some of the numbers repeated. worksheet B has the same list (none repeated) and another list with new numbers beside it. What I need is to take the new numbers from worksheet B and put them next to their correlating number in worksheet A. With many of the numbers being repeated I need something to identify and repeat the new #. I'd copy and paste and drag etc. but there's about 21,000 numbers to go through.
View 3 Replies
View Related
Aug 26, 2009
Worksheet #1:
Column "A" going down (starting at A1 to A5) I have the numbers 1,2,3,4,5 entered in each cell...
Worksheet #2:
In cell A1 is the number "1"
In cell A2 is the number "7"
I want a formula in cell B1 (WS#2) that looks for the number in cell A1 (WS#2) in the range of cells A1:A5 on Worksheet #1, and if it finds the value of A1 (WS#2) in that range of cells on Worksheet #1, it returns the letter Y... if not it returns the letter N
So my result on Worksheet #2 should be...
Cell B1 shows the letter Y
Cell B2 shows the letter N
View 2 Replies
View Related
Apr 7, 2009
Formula is on Sheet1 and table array is on Sheet1
but the lookup value is on Sheet2 in Cell B15
Below does not work, either does anything I have tried.
=VLOOKUP(Sheet2!B15,B138:E161,4,FALSE)
View 9 Replies
View Related
Jul 21, 2006
I have 2 sheets in my workbook "Master Sheet" and "Weekly". The "Master Sheet" contains Parcel Numbers and the groups they belong to, this information will rarely change. The "Weekly" sheet contains data that is pulled from a report weekly. It has 4 columns, the "Parcel Number", the "Parcel Name" the "Availability" of that parcel, and the "Group" the parcel belongs to.
I want to create a macro assigned to a button then when pressed looks at the number in the "Weekly" sheet and searches for it in the "Master Sheet" and populates the corresponding "Group" cell in the "Weekly" sheet with the value it finds in the "Master Sheet". I have attached a sheet that shows an example of what I need.
View 3 Replies
View Related
Apr 23, 2007
Is there a way to get the data in the last row of a sheet and show it in another workbook? And also maybe the 2nd last row?
View 9 Replies
View Related
Jun 27, 2007
How to make the table treat like a database and gives the cost for each day alone.
depeanding on prices sheet list
( For more please see th file ..)
View 4 Replies
View Related
May 16, 2014
I am writing the code for a VLOOKUP in VBA..I was using the .Formula = "=VLOOKUP(LookupValue, LookupRange , Column No, 0 )"
But, the problem is that the LookupRange is to be done from different sheets everyday as the name of this sheet is going to be like 16th May,17th May etc.
The common thing is that this sheet is the adjacent sheet next to the one in which we are trying to get the VLOOKUP work...so what solution can i use.
View 12 Replies
View Related
