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.

ADVERTISEMENT

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

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

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

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
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH

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.

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

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

AND

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.

VB:
Sub copytest()
'by J
Dim ws As Worksheet
Workbooks.Open "survey2.xlsm"
Application.Workbooks("du_database2.xlsm").Activate

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

=if(I1>99,0.5,
if(I1>89,0.45,
if(I1>79,0.4,[code].....

View 3 Replies View Related

Nesting If Statements With Vlookups

Feb 2, 2009

I know how to do a vlookup and I know how to do an if statement. I also know how to do an IF(ISERROR or IF(ISNA ....

However, I can not get this to work:

=IF(C73<7,VLOOKUP(D73,'Package Mix'!$G$39:$H$45,2,FALSE),IF(C73<13,VLOOKUP(D73,'Package Mix'!$G$6:$H$36,2,FALSE),IF(C73>23,VLOOKUP(D73,'Package Mix'!$G$48:$H$56,2,FALSE),"")))

I can not wrap my head around how to make this work w/ IF(ISNA ....

Also, why must you use < or > .... I actually want the numbers to be 6,12, and 24... However, it seems when I use an equal sign it fails.

View 7 Replies View Related

Nesting IF/AND/Or Statements: Figure Out

Mar 22, 2009

I have two cells that each contain their own lists (listA=A1, listB=B1). I'm trying to figure out how to say:

If the selection from (listA= X AND listB=W )or the selection from listB=Y output 0, otherwise output data from C1.

View 2 Replies View Related

Nesting A Multiple IF Formula..

Sep 23, 2009

I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.

If A1 + B1 = 0 then "0"
OR

If A1 = 0 AND B1 is greater than 0 then give B1's value times .01
If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.

=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))

View 3 Replies View Related

Nesting If And Match Functions

Sep 27, 2009

I've followed several tips on how to match these formulas, but somehow cannot make them work. I'm using Open office's version of excel. I need a formula that searches on 'tension downfall' for data in the first sheet, and then return a value corresponding to the row and column that match the input on the first sheet.

'tension downfall' shows a double entry table which shows amperage against length and returns a wire size. I would like to input amperage and length on two different cells and have the wire size needed for those values. Is it clear enough? I'm attaching my current work to make it a little bit clearer.

View 5 Replies View Related

Nesting IF Functions Using EOMONTH?

Feb 17, 2014

I need to create an 'IF' function that nests four EOMONTH functions.

The following throws an error:

=IF(D12="1st month goes here",EOMONTH(D7,2),if(D12="2nd month goes here",EOMONTH(D7,3),if(D12="3rd month goes here",EOMONTH(D7,5)))

EOMONTH will increase the date in the resultant cell accordingly.

View 13 Replies View Related

VBA Nesting If Statements For Dates

Jan 27, 2010

I am having issues writing the VBA code for IF statements. I am essentially trying to nest IF statements within one another. I have it done through the regular If functions as seen in the attachment, but I cannot seem to get it to work with VBA.

I need it to work in VBA without knowing how many rows of data there are. In the attachment you will see the rules that I need the data follow, but here is an example. Essentially, depending upon how large cell C2 is, I need to add a certain amount of months to whatever is in E2.

=IF(C2="","",IF(C2<500000,DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=500000,C2<1000000),DATE(YEAR (E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=1000000,C2<=2000000),DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND( C2>2000000,D2="Critical"),DATE(YEAR(E2),MONTH(E2)+2,DAY(E2)),IF(C2>2000000,DATE(YEAR(E2),MONTH(E2)+1 ,DAY(E2)),"Reevaluate"))))))

View 9 Replies View Related

Nesting Countif: Get The Data ?

Aug 17, 2008

I have a new job a part of which is where I have to provide reports using data from excel 2003 sheets that are sent to me by someone else. I add my own formulas to get the data I need. One such bit of data is. How many rows have "yes" in col E and "w" in range M:V?

View 3 Replies View Related

LOOKUP And Nesting IF Formula

Aug 3, 2009

The enclosed spreadsheet includes a couple formulas Iím having trouble with.
The questions themselves are detailed in the document.

View 7 Replies View Related

Nesting IF Statements Which Contain And Functions?

Jul 14, 2012

I'm trying to return the value of "true" of "false" if a time in a cell falls within 2 time periods I've specified.

=IF(AND(D6>=MorningChargeStart,D6=EveningChargeStart,D6

View 5 Replies View Related

Hitting Nesting Limit

Nov 21, 2007

I'm building a model that based on the start date (cell C7) needs to display the month of the project (M1, M2, M3, M4, ... M12) under the appropriate calendar month (Dec-07, Jan-08, etc.). The formula below works great (E4 is the calendar month Dec-07 in this case, and when I drag the formula horizontally it updates (F4, G4, etc.). B33 through B44 are my month references M1, M2, M3, etc.)

Here's the problem: The below statement contains seven nested ifs and Excel won't let me add additional if statements. How else can this be accomplished?

=IF(AND(E4>=$C7,E4=$C7,E4=$C7,E4=$C7,E4=$C7,E4=$C7,E4

View 9 Replies View Related

Nesting A New Criteria In Sumif

Feb 13, 2009

I have the following Formula:

=SUMIF('Sheet2'!C:C;Sheet1!B3;'Sheet2'!E:E)

In this way I got the sum of all items located in column E wich have the same value than in B3 in Sheet 1....

How could I change the formula in the way that only returns the Values in Sheet2 E:E when (If equals to B2 and C2 in Sheet 1 then Sumif)

The value which I want to use to filter is in the same columns in both sheets

View 9 Replies View Related

ADVERTISEMENT