Can You Use INDIRECT In 3-D References

Jul 13, 2006

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.

View 10 Replies


ADVERTISEMENT

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

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

Data Validation INDIRECT & Relative References

Oct 6, 2006

I am setting up a spreadsheet for user data entry. I have one sheet set up as a template to enable users to copy the required data header cells to subsequent sheets and (the problem) - to different locations on the subsequent sheets. The template is using validated lists with the criteria drawn from the cell/list directly above the current list. For example, the cell in R11C2 is validated/refering to the range: =Campaign

The cell directly below this is validated/ filtered by: =Indirect(R11C2). This works great in the template, or any subsequent sheet in which the cells are all located in the same row/column. However, when the template is pasted in a higher row, the Indirect refers to R11C2 rather than referencing the cell directly above.

View 2 Replies View Related

Getting Indirect Formula To Update Cells References When Copied?

Mar 18, 2014

As you would normally use indirect formulas so the cell references don't change. Which that is what I want in the end, but I need to copy them to an indefinite number of cells first and would like to not do it by hand. I have found some solutions to similar questions/problems but cannot figure out how to make them work for me. So, what I am looking to do is this... (I have also attached the spreadsheet for reference)

I have gotten the information in columns A through F on the first sheet to update as rows are added, moved, deleted on the second sheet using Indirect range. Also, I could do this for Column I (Copmleted Proj. Avg. Terminations) but I would have to do it manually (as I began doing in I3, I4 & I5) but that would be time consuming. So I am hoping there is a way I can copy the formula down the cells are updated for the initial copy but then don't update if the referenced cells are moved or deleted.

View 1 Replies View Related

Nesting "Indirect" References Into Formulas

Oct 14, 2009

Using a combination of "Cell" and "Indirect" commands, I can get cell-references (the name, like "A1"), but I can't figure out how to actually DO anything with them.
I keep trying to nest them inside of formulas, but I just can't get it to work. I've attached a sample workbook - there are two tabs.

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

Changing Hard Coded Column References To Variable References

Oct 22, 2009

I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.

I have been working on this for a couple of days and even tried EE, but to no success.

I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")

View 9 Replies View Related

Converting Absolute References To Relative References In Formulas?

Feb 10, 2014

I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.

All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.

=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")

View 1 Replies View Related

Structured References And With Cell References I Get A Column Of Zeros

Nov 25, 2007

It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.

I have tried to do it with structured references and with cell references I get a column of zeros!

View 9 Replies View Related

Change Static References With Lookup References

Mar 2, 2009

I am using the dsum formula to sum some values...the formula in B2 is:

=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))

The Planes Entidades sheet the data is layed out like this: ....

View 9 Replies View Related

Using SUM And INDIRECT

Nov 18, 2013

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.

View 5 Replies View Related

How To Use INDIRECT

Mar 28, 2014

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.

View 6 Replies View Related

INDIRECT And IF In One

Jul 19, 2008

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.

View 9 Replies View Related

Lookup With Indirect?

Dec 5, 2013

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

Alternative For INDIRECT

Jan 26, 2009

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?

View 11 Replies View Related

Indirect Sum Through Worksheets

Sep 4, 2009

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.

View 9 Replies View Related

Sumproduct With An Indirect?

Nov 16, 2009

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?

View 3 Replies View Related

Indirect Sum Between Two Tabs

Mar 14, 2014

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.

View 7 Replies View Related

Indirect (Address ...)

Nov 6, 2007

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.

View 9 Replies View Related

Sumproduct & Indirect

Apr 20, 2007

=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

View 9 Replies View Related

INDIRECT REF TO DEFINED NAME

Aug 14, 2007

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

View 9 Replies View Related

Sum Indirect With Variables

Sep 28, 2007

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"))}

View 9 Replies View Related

Indirect Using Various Sheets

Nov 16, 2007

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.

View 9 Replies View Related

INDIRECT With MIN Function

Jan 25, 2008

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

View 9 Replies View Related

INDIRECT Across Sheets

Aug 14, 2008

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!

View 9 Replies View Related

Indirect Fields

Sep 29, 2008

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

View 10 Replies View Related

Concatenate With Indirect + VBA

Jan 31, 2009

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

View 9 Replies View Related

COUNTIF With An INDIRECT

Jun 4, 2009

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

View 9 Replies View Related

Indirect To Non-volatile

Feb 26, 2010

why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.

View 9 Replies View Related







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