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.

New York (column header)
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.

I roughly know what my equation should look like, but it keeps failing. Its all part of a wider spreadsheet but I will only put down that bits that impact this part of the sheet.

In my cell I need it to input a value from a different spreadsheet. The input value is text.

I currently use this formula and it works perfectly for what I need.

=(OFFSET(INDIRECT("sheet2!D"&$A7),R$6,25,1,1))

This would a return a variety of results such as "excluded","compliant","non-compliant".

I would like to arrange an IF function so that if the answer is Excluded to keep it as Excluded. But anything else to use this formula instead.

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.

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.

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.

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.

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.

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.

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.

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 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?

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?

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:

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:

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!)

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.

'maxCell.Offset(0, 1).Formula = "=max(maxCell.Offset(-5, -22) : (0, -22))" End If Next maxCell

I have two slightly different lines of code here (highlighted in red), one is commented, but i've been changing both to no avail.

I only want this to be triggered when the cell to the left (maxCell) is not empty, hence I use the offset feature. I've tried to use absolute references for the max formula, but then each cell has the same answer.

Is there a way to use the offset function with a cell reference for the first reference?

I have many columns of named ranges, each representing a month of financial statement data. I want to pull up certain cell values based on the months being compared.

For instance, if the user selects "May", I want to pull only the sales data for May 2013, May 2012 and May 2011. That data is in the 311 row of the columns for each individual month.

=OFFSET(ytdmay2013,311,0,1,1)

In short, I am using a vlookup to get the ytdmay2013 based on the chosen month of May in the input section. If the user then chooses June, the lookup will return ytdjun2013. I want to link the ytdjun2013 in the offset function.

i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker

i would like to make a dynamic chart that updates the data ranges based on something like this: ....

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.

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