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
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.
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 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.
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)
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.
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.
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.
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.
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?
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.
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?
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.
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.
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.
=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("'" & 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.
I 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"))}
I 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.
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))
I 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!
I 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
2 questions:
1. How can i put an Indirect function nested with concatenate?
=CONCATENATE(A8,B8,C8,D8,E8,F8,G8,H8,I8,J8,K8,L8,M8)
2. How can i get the formula to adjust to new data range without manually filling down. Assume the formula starts CELL Q8
I need to have a cell count the number of cells that contain a certain text found in cell $b3. (the 3 is relative the B is not).
The data will be found on multiple sheets called "Game x" where X is an integer. (Game 1, Game 2, etc...) the cells are between $b$65 and $b$73.
i tried =COUNTIF(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$b73"),$B3)
but it did not work.
also after i get that working I need to do the same thing, but i need to count the number of times the Name in $b3 appears in the List with the word "Win" in the "D" column (next to the $b65:b$73)
Again i tried =COUNTIF(AND(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$b73"),$B3),(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$D73"),"Win"))
why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.
View 9 Replies View RelatedI have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies View RelatedI want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies View Related
