I have the exact same problem as was posted in the past, in this post: http://www.ozgrid.com/forum/showthread.php?p=231189
I am trying to sum across a dynamic range of worksheets: sum(sheet1:sheet3!A1). I would like to have "sheet1" and "sheetX" in a separate updateable cell and refer to these for the sum:
When I use the following equation I get a "#REF" error(posted as a solution in the earlier thread):=SUMPRODUCT(--N(INDIRECT(" Sheet"&ROW(1:3)&"!A1"))) what is the "--N" for. Is there something that I should add?
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.
I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.
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.
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.
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".
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.
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.
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 --
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
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:
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:
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:
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"))
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?
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.
I 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 a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
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.
