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


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.


View 1 Replies View Related

Can You Use INDIRECT In 3-D References

Jul 13, 2006

For example
where C10 would contain
always returns #REF!.

However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.

View 10 Replies View Related

Nesting Isnumber Search Formulas

Jun 8, 2007

I am using the ISNUMBER formula to verify if specific text is found in a string of text.

This formula worked when I only needed to find 1 particular brand name:


Now I need to have 2 different brand names ("BrandB" and "J & B") that need to return a "B" and all others should return a "C".

I found a thread regarding this same topic, but I could not get their solution to work for me.
They recommended using:

=IF(ISNUMBER(SEARCH("BrandB",RC[6])),"B","C")&", "&IF(ISNUMBER(SEARCH("J & B",RC[6])),"B","C")

The fact that my second brand name is written with spaces ("J & B") could be adding to the problem, I don't know. I thought replacing the "C" with another "IF(ISNUMBER" function should work, but I have been unsuccessful at writing a formula that doesn't cause run-time errors.

View 9 Replies View Related

Using Cell References In File Paths For Formulas To Create Dynamic Formulas

Dec 3, 2013

I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:

=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292

What I want to do is extract the file path from the above formula and make it a composite of several cell references.

So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:

_MONTH =11 November
_YEAR =2013

I tried several versions, I am hoping for something like this:

=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292

View 4 Replies View Related

Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

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


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.


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


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

Copy Formulas With Cell References

Jan 12, 2009

I have a formula that references data on another spreadsheet with multiple worksheets. I have a new worksheet for each month (Jan, Feb, Mar, ...).

How can I copy formulas in series that keep the cell references the same, but change to the appropriate month. So for the forumula below, I want to fill a series down in a column to represent each month.

Right now I have to go in and edit it to change the month to the next month (i.e. change Jan to Feb.)

An example of the formula is below. In this case, I would want to fill the series down a column and have the months update in series but keep the cell reference the same.

=SUM('[New 2008 sales report linked.xls]Jan'!$M$19,'[New 2008 sales report linked.xls]Jan'!$N$19,'[New 2008 sales report linked.xls]Jan'!$Q$19,'[New 2008 sales report linked.xls]Jan'!$L$47,'[New 2008 sales report linked.xls]Jan'!$K$47,'[New 2008 sales report linked.xls]Jan'!$S$47)

View 4 Replies View Related

Copy Formulas With References Changing

Aug 28, 2007

I am trying to do a simple copy and paste of number of cells A42:H76 to another sheet (in the same workbook) and have all the references stay when pasting to the new sheet. Instead when I paste it I get #Ref and the cell formulas no longer point to the right cell (they are all off by the same number because I did not paste the cells on the same row/column on the new sheet). Also, they don't refer to the older sheet where I want them to refer to.

View 2 Replies View Related

Increase All Formulas Row/Column References

Jun 5, 2008

I need a script that will look for all the formulas in a sheet and increase them by a digit.

Lets say one that particular formula is =(A1-A2)/A2*100

I need the macro to change this to =(B1-B2)/B2*100

I have already programmed in a Find/Replace command, but realised that this would only work one time only. I need generic code that will bump them up each time rather than having to hard code the Find/Replace command for every single cell in the book.

View 5 Replies View Related

Circular References - Formulas Relating To Other Sheets

Jun 21, 2013

I am trying to make a formula use an IF statement to identify different cells on a different sheet to pull raw data from.

However I've never tried formulas involving multiple sheets before.

This is the formula i get a circular reference for =IF('Store Input'!I6:J6=Lewis,(('Store Input'!C6:D6/1.2)/100*0.75))

I am not entirely sure what im doing thats causing confusion but ill explain the desired effect.

If a Cell has a value of "Name" then take data from cell "X" and do calculation /1.2 /100 *0.75 on a separate sheet.

Further more i want it to apply to multiple cell possibilities. so like the one above except repeated on different cells.

So as well as the above, i want it to perform the same operation for a set of cells further to its right and so and so forth.

View 1 Replies View Related

Copy Formulas While Keeping Cell References

Sep 1, 2009

I need to copy a bunch of cells that contain formulas without altering the cell references. I know I could change each formula to contain absolute cell references, then copy and paste special with formulas, but this is alot of work, and following that I'd need to change the references back again from absolute to relative in both locations. So, is there a way to quickly copy and paste formulas in multiple cells without altering the cell references?

View 2 Replies View Related

Compare Formulas Ignoring Range References

Dec 20, 2006

I would like to rotate a Line Chart 90 degrees. I tried using an XY scatter chart but my Y-axis would be time values which are somewhat random but increasing. I would like to keep the spacing between plot points consistent (Y-axis spacing). I could simply use a number list for my Y-values to get consistent spacing but then I lose the time information. I donít necessarily need the time value on the Y-axis if I could get the information to display when mousing over the plot point. Any ideas on how I could accomplish this?

View 6 Replies View Related

Copy Formulas Without Changing Range References

Feb 5, 2008

I have a large workbook with LOTS of large formulas. However they dont contain $ characters before the ranges as I haven't needed to manipulate them til now. Now I am changing the sheet and I cant seem to move or copy these cells without the ranges whithin the formulas changing! Is there a way to do this? If not is there a way in VBA that I can check through selected cellls and enter a $ before all the ranges?

View 2 Replies View Related

Using Cell Values As Partial Spreadsheet References In Formulas

Feb 21, 2014

I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.

View 1 Replies View Related

How To Unfix Multiple Cell References From Formulas In A Range Of Cells At Once

Jan 3, 2014

Is there a way to unfix multiple cell references from formulas in a range of cells at once?

View 2 Replies View Related

Replace Range Names In Formulas With Range References

Jul 7, 2007

I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?

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

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

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

Nesting IF(AND

Feb 14, 2008

Im having some isses nesting two arguments together.

basically(ha, ha), id like to do the following operation in cell J9:

If the Actual Date (F9) is blank leave the cell blank, and if the actual date is blank is the target date is less than NOW(), leave it blank.

If the Actual date is not blank, do Actual minus Target, and if NOW() is greater then the target date, do now() minus target.

if(F9="", ""
if(E9 < NOW(), ""


if(F9=<>, F9 -E9
if(NOW()>E9, NOW()-E9

I have attached a spreadsheet to illustrate.

View 10 Replies View Related

Nesting Sum If....

Nov 30, 2008

I tried using this array formula but it has an error in it and I can't figure out the error. =SUM(IF(Tracking!$C$2:$C$5000=$B$4,IF((Tracking!$D$2:$D$5000="15-M Eval")+(Tracking!$D$2:$D$5000="15-M TIC")+(Tracking!$D$2:$D$5000="Misd CRT"),IF(Tracking!$E$2:$E$5000=$A6,IF(Tracking!$J$2:$J$5000>=VALUE($B$1),IF(Tracking!$J$2:$J$5000<=V ALUE($B$2),1,0)))))) (I attached the workbook. In the CSTE worksheet, I didn't use control-shift-enter so you could see the formula.)

What I'm trying to do is come up with a formula that will sum if either of these three Legal Status types (15-M Eval, 15-M TIC, and Misd CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. The formula works if I just want to find one Legal Status type. I also will want to come up with a formula that will sum if either of these five Legal Status types (15/30-F Eval, 15-F TIC, 1/90 CRT, 2/90 CRT, and 180 CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. I tried researching if I could define a name that would sum these 3 Legal Status types but that didn't work either.

View 2 Replies View Related

Nesting For Each Statements

Nov 19, 2013

I got a 2 columns (A:B) with values in workbook1 (survey2.xlsm)

I want to open a sheet named according to values from column A wb1 in workbook2 (du_database2)

And I want to fill in the offset values (column B) in the aproppriate sheet

I wrote this sub for it. I am having troubles with the for each statements (how do I set them up properly?). The script itself hasn't been checked might also been wrong there.

Sub copytest()
'by J
Dim ws As Worksheet
Workbooks.Open "survey2.xlsm"

[Code] .....

View 2 Replies View Related

Nesting AND / IF And SUM Functions

Jul 11, 2014

I am trying to add up a specfic column in Excel based on the parameters or two different ones.

I.e If Col B is blank and Col D is 03 then add up the value in Col P

But I can't seem to work out how to do it, I have tried conditional sums and everything else I can think of, but it is still not working.

View 4 Replies View Related

Nesting If, And & Between Functions

Mar 14, 2007

I am trying to create a formula with mutiple criterias to come back with results of whether that person is Eligible or Not Eligible for stock. The criteria to be Eligible is as follows:

1) Hire Date before 1st March 2007

2) Band 1 and the performance review = Exceeded

3) If Band 2-5 then performance review must be either Achieved or Exceeded

See attached file for the example. I have got as far as the first 2 points but i am struggling with incorporating the last point.

View 2 Replies View Related

Too Many Levels Of Nesting For Formula?

Apr 3, 2014

Im recieving an error saying that i have too many levels of nesting for my formula

Is there any way to rewrite the formula i have here?


View 3 Replies View Related

Copyrights 2005-15, All rights reserved