Indirect Formula For Sheet Name & Range

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


ADVERTISEMENT

Sheet Cell Reference And INDIRECT Formula

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

Relative Indirect Formula To SUM Range

Feb 13, 2008

I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.

VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"

where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.

View 9 Replies View Related

Reference Sheet Names With Indirect Function To Make Formula?

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

Indirect Formula Syntax For Referring To A Range On Two Worksheets

Jul 5, 2012

B1=Name of WorkSheet
B2=Name of 2nd Worksheet

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.

=SUM(INDIRECT("'"&B1&":"&B2&"'!F5"))

with some added spaces for clarity:

=SUM(INDIRECT(" ' " & B1 & " : " & B2 & " ' ! F5"))

View 9 Replies View Related

Use INDIRECT To Refer Sheet Name

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

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 View Related

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 View Related

INDIRECT In SUMPRODUCT For Sheet Name

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

INDIRECT And Sheet Name Starting With C01V

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

Vlookup In A Closed Sheet, By Using Indirect

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

Indirect Function: Use Cell For Sheet Name

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

Indirect 3-D Range Summation

Dec 5, 2006

I was trying to assist someone with 3D referencing and summing, but getting stuck on referencing text based sheetnames.

We are trying to sum range A1:A10 in the sheets between the range defined by A1:A2

Now, if the Sheet names were numerically sequenced, eg. Sheet1, Sheet2, etc...then we could use this formula with no problem

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(A1&":"&A2))&"'!A1:A10"),""))
where A1 and A2 housed the numbers 1 and 2, respectively,

but what if the sheets are text based? Is there a way....say the Sheets were named SheetX, SheetY, SheetZ....

I did some searching on the internet and found samples only with the numeric based sheetnames.

View 10 Replies View Related

Named Range / Use Of Indirect

Mar 6, 2009

In cell A1 I have the text Testme

Testme is a Named Range
with RefersTo set to:
=Sheet1!$D$3:$D$5

In VBA - How do I

Range("A1").copy Range("D4").PasteSpecial xlPasteValues

And D4:D6 will show
123
456
789

This thing is beating me up -- Indirect is likely involved, but,,,

View 9 Replies View Related

Develop An Indirect Indirect Validation Drop Down List

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

Indirect Using Dynamic Range Not Working

Dec 27, 2012

In the example I have attached there are two sheets, Details and DataTables. There are also a number of named ranges, both static and dynamic, that refer to data on the DataTables sheet. Most of the static ranges were added for debugging purposes. The tables have been shortened for this example.

On the Details sheet column A has a drop down that allows you to select the make of a device. Column B builds its drop down list based on what is selected in column A. The data validation in B2 uses a named dynamic range and the drop down does not work. The data validation in C2 used a named static range and the drop down works.

Column C will fill in the RU value based on what is selected in column B. Cell C3 uses named dynamic range and it does not work. Cell C4 uses named static range and it works.

I would like to used named dynamic ranges if possible so when data is added to the tables the named dynamic ranges will self adjust. Can what I want to do be done?

Dynamic Range.xlsx

View 8 Replies View Related

Indirect With Non-continuous Named Range

Oct 9, 2003

I'm trying to use Indirect in a formula where named range refers to multiple non adjacent cells; e.g. ever other cell {A1, A3, A5... etc}; however, when I use indirect, I recieve a #ref error. Replacing the indirect with the actual named range will make the formula work just fine, or using a named range that a continuous range works fine as well. It seems that indirect doesn't like non continuous ranges. ?

View 6 Replies View Related

X-Y Plot That References Indirect Range

Jun 29, 2012

$TC$2:$WX$3

is an indirect range that resides in cell B15. It is constantly changing and the expectation is that the X_Y plot would adjust accordingly. It represents the data range of the chart. The chart does not carry with it any title.

So how do I approach this without using vba? As always any input is highly valued.

View 2 Replies View Related

VBA For Filter Using Indirect To Locate Range

Jan 18, 2013

I have the simple code below to perform and filter and copy:

Code:

Sub Macro1()
Sheets("Data Dump").Select
Range("A1:F100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"K1:L73"), CopyToRange:=Range("P1:U1"), Unique:=False
End Sub

However, the CriteriaRange can change depending on which OptionButton the user chooses on a different tab. My reference the range is in Cell N1. So how can I use Indirect to refer the range in Cell N1 instead of the current CriteriaRange of K1:L73?

View 5 Replies View Related

Copying Down An Indirect Formula

Aug 20, 2009

How can I copy down an indirect formula? When I copy it the lookup reference doesn't change. My formula is: =IF(INDIRECT("Q1")="",INDIRECT("R1"),INDIRECT("Q1"))

but when I copy down the cell reference stays the same (I need to keep the indirect formula because I'm adding columns in column Q but it needs to reference column Q even when columns are added). From reading through some other posts I believe I need to add a ROW() or COLUMN() formula in there somewhere.

View 5 Replies View Related

SUMPRODUCT With Indirect Formula

Jul 15, 2013

I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.

=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH($A$12,$A:$A,0),MATCH($G$3,3:3,0)),FALSE)))

I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.

View 9 Replies View Related

Array Formula And Indirect

Aug 27, 2007

getting an array formula to work with my indirects....

View 9 Replies View Related

Copy Down An INDIRECT Formula

Sep 18, 2007

When I try to copy this formula down, it stays the same and won't reference the cells.

=INDIRECT("PO_Order_Form!B5")

View 9 Replies View Related

#VALUE Error In Indirect Formula

Sep 24, 2008

rate2008
rate2007
...

And I want to show this data in a table, where I have the years 2008, 2007, ... in cells A1:A10 and the formulas =INDIRECT("rate"&A1), =INDIRECT("rate"&A2), ... in cells B1:B10.

For some reason, I am getting nothing but #VALUE! errors in my indirect formulas. In fact, even if I take out the indirect and just have ="rate"&A1, ="rate"&A2, etc., I still get the errors. It seems like the problem is with the & operator. This only seems to be a problem in this certain workbook; I am able to get the desired results if I open a new workbook.

View 9 Replies View Related

#REF! And #VALUE! Error With INDIRECT Formula

Jul 25, 2006

when using the following forula as below; =INDIRECT(INDEX($B$37:$B$62,$B$3)&"!"&ADDRESS(ROW(D6),COLUMN(D6))). centain cells come up with #REF! and or #VALUE!

View 6 Replies View Related

Add Indirect Formula Via Macro

Oct 26, 2006

I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.
this is what i have so far...

Option Explicit

Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application. ScreenUpdating = False
target = Cell.Address
Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"
Application.ScreenUpdating = True
Next Cell
End Sub

but this is the answer I am getting in the first cell of the range...

=INDIRECT(CONCATENATE(BackEnd! 'E15',"!",target))

as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.

View 5 Replies View Related

Alternate Formula For Sum Indirect

Feb 14, 2007

I have tried to apply '= SUM(INDIRECT("A2:A10"))' formula to do the SUM at cell A11. But, if I add two more Rows, then my formula moves down to cell A13 but numbers in Cell A11 and A12 does not get added to the total. How can I avoid that? I have reserached this site extensively and could not find an archived solution.

View 6 Replies View Related

Indirect Function To Reference A Named Range

Apr 30, 2009

How would I used the indirect function to reference a named range in the workbook in formula creation.

For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:

=sum(indirect(A1))

In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.

I'm using Excel 2007.

View 9 Replies View Related

Indirect: Reflect The Data In The Active Sheet Or Give Me A #VALUE Error

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

Formula To Increment Indirect References

Mar 5, 2009

I have a sheet which needs to look up one reference and then fill a table with the rest of them.

EG:

Cell A1 contains '0091 911'!$E$2 (cell E2 contains value 100)

Cell A2 contains =indirect(A1) and displays value 100

I need a formula which will auto fill the remaining cells in the table.

eg:

Cell A3 fills to contain '0091 911'!$E$3 (row +1)

Cell B2 fills to contain '0091 911'!$F$2 (column +1)

so it needs to fill the Indirect reference and not =indirect(A1),=indirect(A2)....

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved