# INDIRECT - Sum Contents Of Column On Another Worksheet Up To Certain Cell Reference

Jan 24, 2012

Trying to use INDIRECT to sum the contents of a column on another worksheet upto a certain cell reference which is in another cell on the worksheet.

=SUM(INDIRECT("Sheet1!A4:Sheet1!"&B1))

I have taken it back to simply having sheet1 with numbers in A4 to A23, then sheet2 with A23 in cell B1, and the formula above it C9, but I keep getting #REF!.

The formula works fine if on sheet1 without the worksheet names in it. Formula evaluation gets to =SUM(INDIRECT("Sheet1!A4:Sheet1!A23")) then gives =SUM(#REF!)

## Using Sumif And Indirect To Reference Another Worksheet

Oct 15, 2013

I am trying to use the sumif and indirect function together but I keep getting an error and I'm not sure why.

Actual formula:
=SUMIF('SWG OR'!B:B,A168,'SWG OR'!E:E)

Using the sumif/indirect function, I have the following:
> Cell B5 contains the name of the tab "SWG OR"
=sumif(indirect(B5&"!B:B",A168,B5&"!E:E"))

## Worksheet Reference In Indirect Function

Feb 22, 2010

I have a worksheet with a name that inckudes a hyphen, e.g. 1234-5678.
And I have the worksheet name in a cell, say, I!.

Using

I get a REF error because of the inclusion of the hyphen in the worksheet name.

How can I fix this without eliminating the hyphen?

## Increment Indirect Formula Column Reference

Dec 3, 2007

COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&\$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.

How do I change my formula so that the numbers increase automatically?

## Combining Text And Cell Reference With Indirect

Oct 2, 2008

I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")

But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.

## Sheet Cell Reference And INDIRECT Formula

Jun 9, 2009

I have this formula in excel 2007:

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.

## Dynamic Cell Reference In Indirect Formula

Oct 11, 2011

I need making a dynamic cell reference in an indirect formula, which looks at a different workbook.

The other workbook is called: ESF.xlsx
The sheet name in the other workbook is defined in Cell B2
The first cell i need to pull across is B115.

In cell B8, This is what i have so far:

=INDIRECT("[ESF.xlsx]"&B2&"!"&"B115")

How do i make it so that i can copy this formula quickly, but so only the row and column reference change?

Like in C8 i would want it to look at cell C115, In C9, id want it to look at C116 etc.

## Variable Cell Reference In INDIRECT Formula

Sep 28, 2006

I want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,(\$A9) and insert this in a cell BUT the \$A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?

## How To Update Cell Reference When Dragging - INDIRECT Formula

Jul 10, 2014

I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:

=SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$1:\$A\$4&"'!A:A"),\$B4,INDIRECT("'"&\$A\$1:\$A\$4&"'!G:G")))

The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.

## Too Many Instances Of "INDIRECT": Reference One Worksheet From Another

Apr 4, 2009

I only dabble with Excel occasionally, but whenever I do, boy, do things get ambitious. Now I've run into a bit of a brick wall trying to reference one worksheet from another, with the specific worksheet referenced being variable (it looks for the worksheet name in cell D3).

I formerly had this, the "hard-coded" version:

=SUM('2009'!F4:OFFSET('2009'!F4,(MATCH(B3,'2009'!A4:A15,0)-1),0,1,1))

I want to replace "2009" with the lovely and helpful INDIRECT function, and have managed to get to this point:

=SUM('2009'!F7:OFFSET(INDIRECT(D3&"!F7"),(MATCH(B3,INDIRECT(D3&"!A7:A18"),0)-1),0,1,1))...........

## Adding Number To Cell Reference Not To Contents

May 28, 2006

I want to create a formula that will refer to the cell I want by adding a number to the cell reference, not to the contents of the cell. For example, lets say that the cell C11 holds a value of 22. I want to add 4 to C11 to get my formula to reference cell C15, I don't want it to add 4 to the contents of C11 which is 22 and would give me a result of 26. I have a large spreadsheet and I can't just fill down the way I have it now and I don't want to have to retype the formula every time.

## Excel 2003 :: Offset Reference To Be Contents Of Cell

Mar 21, 2014

I have done a Scenario and they list the cell address (example R2C35) in the Report - the cell has the text "R2C35" in it.

I want to get the contents of a cell 2 columns to the left of Cell R2C35.

So what would I use for the Reference Cell in the Offset formula?

## Index Match Offset - Find Contents Of Reference Cell

Jun 21, 2012

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G\$3) is equal to 9 I want it to use 8 instead.

Code:
IF(ISERROR(INDEX(tra!\$E\$2:\$E\$1100,MATCH(G\$3,tra!\$C\$2:\$C\$1100,0))),"",
INDEX(tra!\$E\$2:\$E\$1100,MATCH(G\$3,tra!\$C\$2:\$C\$1100,0)))

I tried using G\$3-1 but this doesn't appear to work.

## Convert Cell Reference To Worksheet Reference

Jul 20, 2014

I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:

Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say

AAB
ABC
CDE

And have a formula in column B that converts this to

=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)

I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?

## Indirect Reference To Defined Name

Feb 17, 2012

Reduced to it's simplest form I have a formula

=1*(FY2012_TY), where FY2012_TY

is a Defined Name which refers to a column on another tab in the workbook, and is defined as:

=OFFSET(Table!\$AA\$3,0,0,COUNT(Table!\$AA:\$AA),1)

What I would like to do is to come up with an approach to this formula that would allow me to drag it across columns and have the year automatically increment (e.g., FY2013_TY, FY2014_TY, all of which are similarly defined Defined Names referring to adjacent columns). I have tried various approaches using INDIRECT along with CONCATENATE, but when it comes down to evaluating the formula, I consistently get a #REF! error.

## Row Reference Using No Indirect Or Offset

Feb 1, 2009

I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.

Wages
Lights
Net Profit

Boston
Lights
Wages
Sales
Net Profit

How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.

## Adding Text To INDIRECT Reference

Feb 13, 2014

Is there anyway to add text to an INDIRECT reference so that it will now reference a new range?

For instance, this is my formula...

=IF(INDEX(INDIRECT("A1+TM"),MATCH(B2,INDIRECT(A1),0),1)="","-",INDEX(INDIRECT("A1+TM"),MATCH(B2,INDIRECT(A1),0),1))

I am trying to say, if the cell 1 column to the left of the cell that matches the text in B2, in the cell range referenced in A1 (CWS) "added to the text "TM", is blank, then write "-", otherwise return the number in the cell 1 column to the left of the cell that matches the text in B2, in the cell range referenced in A1 (CWS)+"TM".

Cell A1 is a data validation list that refers to different ranges. The only difference between those named ranges and the named range I want to reference is the text "TM" comes at the end.

## Indirect Reference To Closed Workbook

Jan 17, 2008

I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.

The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.

Example: ...

## Using Named Ranges Following Indirect Reference?

Feb 7, 2012

I have the following formula which i'm using to indirectly reference a worksheet. The worksheet i'm referencing includes named ranges that i'd like to call on but i can't get the syntax right.

Code:
=SUMPRODUCT(--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")>=D5),--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")

## Indirect Reference To A Separate Workbook

Jun 26, 2014

Just wondering if there is a way to indirectly reference a workbook in a formula?

For example, suppose A1 has the path/filename of the workbook I want to reference.

So in another cell I want to do, say, a VLOOKUP(A2,A1,5), where A2 is the lookup value. This way, if I have multiple workbooks I want to look at, I can specify them all in various cells, and simply have the formula look at the right cell to perform the lookup.

I've played with INDIRECT and get something close if I do

=INDIRECT("'"&A1&"'!A1:AJ1500")

but that just returns the contents of A1 in the target sheet.

## VLOOKUP - Indirect RangeName Reference

Mar 12, 2008

I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).

Cell A1 contains a fuel cost.

Cell A2 contains a formula that returns a RangeName.

In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP(\$A\$1,\$A\$2,2,FALSE). The result is #NA!.

If I use =VLOOKUP(\$A\$1,INDIRECT(\$A\$2),2,FALSE), I get #REF!.

Among other things, I've also tried =VLOOKUP(\$A\$1,TEXT(\$A\$2,"@"),2,FALSE). No cigar.

## Changing Indirect Reference When Dragging Formula

Dec 9, 2012

I have been using the following formula to do conditional summing over multiple worksheets:

=SUMPRODUCT(SUMIF(INDIRECT("'"&\$AB\$2:\$AB\$13&"'!b3:b39"),\$B16,INDIRECT("'"&\$AB\$2:\$AB\$13&"'!c3:c39")))

The formula works fine, but when I drag it to the next column c3:c39 stays the same. How do I get that reference to change when I drag the formula over multiple columns.

## 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.

## Worksheet Name Based On Contents Of Cell

Oct 13, 2013

Is it possible to dynamically chance the name of at a worksheet that already exists based on the contents of a cell on another sheet?

I.e. If the name of a sheet is linked to the value of the Cell A1 in the "Data" sheet and I change A1 to read "Group1" it changes the name of the sheet as well?

## Using Variable Column Reference In VBA Worksheet Function

Jul 15, 2014

I have the following line in VBA which works.

[Code]....

I have a variable for the column adress of the second part of the formula so it looks like this:

[Code] ....

But it says I don't have the correct syntax.

## SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

Apr 4, 2014

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

## Copy Cell Contents To Different Worksheet On Save

Oct 28, 2008

I have an Excel invoice set up and working well. It does a bunch of things with macros - e.g. on save it increments the invoice number well as creates a jpeg screenshot for the invoice archives. I have added an additional worksheet (titled 'VAT') to the workbook. The new 'VAT' worksheet has five simple columns; Invoice no, Subtotal, VAT, M.O.T. and Total.

What I need:-
On saving the workbook I would like to add a macro function that copys the final contents of the Invoice no (H2), Subtotal (C37), VAT (C38), M.O.T. (F38) and Total (I38) cells from the 'Sales Invoice' worksheet to the newly created 'VAT' worksheet in the respective columns. I would like this to be cumulative, i.e. continue to add the contents of the afore mentioned cells to the appropriate columns in the 'VAT' worksheet every time the invoice is saved. I would also like to have the Subtotal, VAT, MOT and Total columns summed and outputted in a cell of their own - but hopefully I can handle that.

## Copy Row To A New Worksheet Based On Contents Of Cell

May 6, 2007

if colum s has a n then can i copy that entire row to a new sheet

## 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.

## VBA Code To Copy Contents Of Cell A1 To End Of All Rows In Worksheet?

Nov 17, 2011

i have a workbook with over 600 worksheets and any vba code to do the following.... (each worksheet contains different number of lines)

At the moment the data is in columns a to d

What i need is the data currently in cell a1 (in each worksheet) to appear beside every line in that worksheet

Then i need to take all this data and put it onto one single worksheet .