Hi, I'm using Excel version 2003. I have the following formula =IF(SUM('Section 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like to increment the cell references by 12. I was wondering if there is a way to do this using a bit of VBA code? So the result i'm looking for after code run is =IF(SUM('Section 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). I will be running the formula along the columns so will need to increment the formula by 12 for each new column. Thanks Dave

0 |

4/14/2010 1:13:01 PM

Why not just use Find & Replace, Ctrl-H? -- HTH Bob "David Marr" <David Marr@discussions.microsoft.com> wrote in message news:2FBB1722-A5B1-44D1-B59A-3EDC067D9132@microsoft.com... > Hi, > > I'm using Excel version 2003. > > I have the following formula =IF(SUM('Section > 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like > to > increment the cell references by 12. > > I was wondering if there is a way to do this using a bit of VBA code? > > So the result i'm looking for after code run is =IF(SUM('Section > 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > > I will be running the formula along the columns so will need to increment > the formula by 12 for each new column. > > Thanks > Dave

0 |

4/14/2010 1:40:06 PM

Because it takes too long as i have a vast amount of data. Dave "Bob Phillips" wrote: > Why not just use Find & Replace, Ctrl-H? > > -- > > HTH > > Bob > > "David Marr" <David Marr@discussions.microsoft.com> wrote in message > news:2FBB1722-A5B1-44D1-B59A-3EDC067D9132@microsoft.com... > > Hi, > > > > I'm using Excel version 2003. > > > > I have the following formula =IF(SUM('Section > > 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like > > to > > increment the cell references by 12. > > > > I was wondering if there is a way to do this using a bit of VBA code? > > > > So the result i'm looking for after code run is =IF(SUM('Section > > 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > > > > I will be running the formula along the columns so will need to increment > > the formula by 12 for each new column. > > > > Thanks > > Dave > > > . >

0 |

4/14/2010 2:23:01 PM

Are you interested in a non-VBA method? Let's say the formulas are to be on Sheet2. The first one in D2, the next in E2, etc. So we have (let's forget the IF part for now) D2 E2 F2 =SUM(H1675:J1686) =SUM(H1687:J1698) =SUM(H1699:J1710) In D2 enter =SUM(INDIRECT("Section3!H"&1675+12*(COLUMN(A1)-1)&":J"&1686+12*(COLUMN(A1)-1))) Format this cell with custom format such as: 0;0;;@ to hide any zero results Drag D2's fill handle to the right as far as you need to go. No matter where you start your formulas, leave the reference to COLUMN(A1) unchanged. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "David Marr" <David Marr@discussions.microsoft.com> wrote in message news:2FBB1722-A5B1-44D1-B59A-3EDC067D9132@microsoft.com... > Hi, > > I'm using Excel version 2003. > > I have the following formula =IF(SUM('Section > 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like > to > increment the cell references by 12. > > I was wondering if there is a way to do this using a bit of VBA code? > > So the result i'm looking for after code run is =IF(SUM('Section > 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > > I will be running the formula along the columns so will need to increment > the formula by 12 for each new column. > > Thanks > Dave

0 |

4/14/2010 2:27:38 PM

Looking at your before and after examples I don't see any column changes. H and J are consistent. Only row increments of 12 as if you are copying down a single column. Is that a typo or? If you are copying down a column try this macro to increment the rows. First remove the absolute reference $ signs from your first formula. Sub Increment_Formula_Steps() ''copy a formula down with steps in cell references ''select range first with formula in active cell Dim StepSize As Variant Dim NumCopies As Integer Dim Cell As Range StepSize = InputBox("Step?") 'e.g., 12 If StepSize <> "" Then NumCopies = Selection.Rows.Count Application.ScreenUpdating = False For Each Cell In Selection.Columns(1).Cells Cell.Copy Cell.Offset(StepSize) Cell.Offset(StepSize).Cut Cell.Offset(1) Next End If End Sub If you want the absolute references add them after. Sub Absolute() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Gord Dibben MS Excel MVP On Wed, 14 Apr 2010 06:13:01 -0700, David Marr <David Marr@discussions.microsoft.com> wrote: >Hi, > >I'm using Excel version 2003. > >I have the following formula =IF(SUM('Section >3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like to >increment the cell references by 12. > >I was wondering if there is a way to do this using a bit of VBA code? > >So the result i'm looking for after code run is =IF(SUM('Section >3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > >I will be running the formula along the columns so will need to increment >the formula by 12 for each new column. > >Thanks >Dave

0 |

4/14/2010 2:41:14 PM

A VBA solution with zero's blanked out; First cell used formula =Dozen(A1) to sum the first block Drag this to the right to make =Dozen(B1) to sum next block etc.... Function dozen(myblock) whatblock = (myblock.Column - 1) * 12 mystart = 1675 + whatblock firstcell = "H" & mystart mylast = 1686 + whatblock lastcell = "J" & mylast myrange = "Section3!" & firstcell & ":" & lastcell dozen = WorksheetFunction.Sum(Range(myrange)) If dozen = 0 Then dozen = "" End Function best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "David Marr" <David Marr@discussions.microsoft.com> wrote in message news:2FBB1722-A5B1-44D1-B59A-3EDC067D9132@microsoft.com... > Hi, > > I'm using Excel version 2003. > > I have the following formula =IF(SUM('Section > 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like > to > increment the cell references by 12. > > I was wondering if there is a way to do this using a bit of VBA code? > > So the result i'm looking for after code run is =IF(SUM('Section > 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > > I will be running the formula along the columns so will need to increment > the formula by 12 for each new column. > > Thanks > Dave

0 |

4/14/2010 2:59:05 PM

It takes too long to select the range to fix Edit|Replace what: $1686 with: $1698 replace all David Marr wrote: > > Because it takes too long as i have a vast amount of data. > > Dave > > "Bob Phillips" wrote: > > > Why not just use Find & Replace, Ctrl-H? > > > > -- > > > > HTH > > > > Bob > > > > "David Marr" <David Marr@discussions.microsoft.com> wrote in message > > news:2FBB1722-A5B1-44D1-B59A-3EDC067D9132@microsoft.com... > > > Hi, > > > > > > I'm using Excel version 2003. > > > > > > I have the following formula =IF(SUM('Section > > > 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like > > > to > > > increment the cell references by 12. > > > > > > I was wondering if there is a way to do this using a bit of VBA code? > > > > > > So the result i'm looking for after code run is =IF(SUM('Section > > > 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > > > > > > I will be running the formula along the columns so will need to increment > > > the formula by 12 for each new column. > > > > > > Thanks > > > Dave > > > > > > . > > -- Dave Peterson

0 |

4/14/2010 2:59:56 PM

Thanks for the reply but that doesn't seem to work. I have the following code in another Excel file I have but i need to alter it to ignore the Section 3 as it seems to increment that by 12. Sub add125() Dim rngToChange As Range Dim iChar As Integer, iCount As Integer Dim strOld As String, strNew As String, strNumb As String Dim strChar As String Set rngToChange = ActiveCell strOld = rngToChange.Formula strNew = "" strNumb = "" iCount = 0 For iChar = 1 To Len(strOld) strChar = Mid(strOld, iChar, 1) If IsNumeric(strChar) Then strNumb = strNumb & strChar Else If strNumb <> "" Then iCount = iCount + 1 strNew = strNew & (strNumb + IIf(iCount = 3, 0, 132)) strNumb = "" End If strNew = strNew & strChar End If Next iChar rngToChange.Formula = strNew End Sub Sub add12() Dim rngToChange1 As Range Dim iChar1 As Integer, iCount1 As Integer Dim strOld1 As String, strNew1 As String, strNumb1 As String Dim strChar1 As String Set rngToChange1 = ActiveCell strOld1 = rngToChange1.Formula strNew1 = "" strNumb1 = "" iCount1 = 0 For iChar1 = 1 To Len(strOld1) strChar1 = Mid(strOld1, iChar1, 1) If IsNumeric(strChar1) Then strNumb1 = strNumb1 & strChar1 Else If strNumb1 <> "" Then iCount1 = iCount1 + 1 strNew1 = strNew1 & (strNumb1 + IIf(iCount1 = 3, 0, 12)) strNumb1 = "" End If strNew1 = strNew1 & strChar1 End If Next iChar1 rngToChange1.Formula = strNew1 End Sub Where am I going wrong? cheers Dave "Bernard Liengme" wrote: > A VBA solution with zero's blanked out; > First cell used formula =Dozen(A1) to sum the first block > Drag this to the right to make =Dozen(B1) to sum next block > etc.... > > Function dozen(myblock) > whatblock = (myblock.Column - 1) * 12 > mystart = 1675 + whatblock > firstcell = "H" & mystart > mylast = 1686 + whatblock > lastcell = "J" & mylast > myrange = "Section3!" & firstcell & ":" & lastcell > dozen = WorksheetFunction.Sum(Range(myrange)) > If dozen = 0 Then dozen = "" > End Function > > best wishes > -- > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > > "David Marr" <David Marr@discussions.microsoft.com> wrote in message > news:2FBB1722-A5B1-44D1-B59A-3EDC067D9132@microsoft.com... > > Hi, > > > > I'm using Excel version 2003. > > > > I have the following formula =IF(SUM('Section > > 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like > > to > > increment the cell references by 12. > > > > I was wondering if there is a way to do this using a bit of VBA code? > > > > So the result i'm looking for after code run is =IF(SUM('Section > > 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). > > > > I will be running the formula along the columns so will need to increment > > the formula by 12 for each new column. > > > > Thanks > > Dave > > . >

0 |

4/15/2010 7:51:01 AM

I have a formula that adds data in a column. =SUM(A5:A10). The proble is that every time I copy a new row and insert it at row 5, my formul changes to =SUM(*A6*:A10). I just want it to stay the same. I'v tried using an absolute value $A$5, but it still changes when a new ro is added. I've tried using a named reference as well as locking, but m merged cells caused some problems with the lock -- shaugh ----------------------------------------------------------------------- shaught's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3080 View this thread: ...

I have a file in which the cells reference the C drive. If I open that file on another computer it then changes to be the drive for the computer I last edited it on, or the network file letter (if that is the correct way to put it). Is there a way to always keep it referencing the "C" drive no matter which computer opens it or edits it? -- David P. -- David P. ...

Suppose I have a pair of columns giving nicknames and full names, e.g. John John Q. Public Bob Robert F. Kennedy Marty Martin Luther King Abe Abraham Lincoln Dick Richard M. Nixon Is there a worksheet function which will accept the value of a nickname from one cell along with the location of the above table, and return the name from the second column corresponding to the matching nickname in the first column? E.g. I type "Abe" and the function returns "Abraham Lincoln"? Seems like there should be, but I can't find it. - Rich Have a ...

Hello, I wrote last time: I want to know what I will get paid TODAY, THIS WEEK or by the MONTH. I don't know how to get the Day (C2),Week (D2) and Month.(E2). Thanks for those who helped. Now: I must add that if today is Thursday I need to know what I will get paid Thursday - Saturday for the week and from the 29th - 30th of the month. In other words for the week and Month calculations it must be going forward from the current day not for the entire week or month if that time has past. If the weekday is Tuesday then I need from Tuesday to Saturday. What formula can I use to accomplish this...

-------------------------------------------------------------------------------- I am looking for a shortcut. I am copying the same forumula from Row 1 down the page, but where Row 1 references the next worksheet, Row 2 references the following worksheet, and so on. Is there an easy way to do this without having to go to each worksheet, find the cell and click on it? TIA -- bhigdon ------------------------------------------------------------------------ bhigdon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23686 View this thread: http://www.excelforum.com/s...

I have a list of productnames and a list of brandnames. I want to supply the productnames with an attribute "Brand". I am currently manually filtering the list of productnames based on "Contains... Brand" and adding the Brand to the product using copy paste, but this takes me too long and I can only match records one brand at a time. Can I make Excell search through the list of productnames and whenever it comes across a brandname within the string of a productname it will add it to the column next to it? One way Assume productnames running in A2 down A...

I need somone to tell me a formula to copy and paste into a cell in my excel worksheet. I need this for work today! Can't figure it out. Here's what I need: If SUM(C4:O4) is greater than 40, then SUM(C4:O4)-40, but if SUM(C4:O4)is equal or less than 40, then 0 Now how the heck do I make a formula with that? Please help! Try =IF(SUM(C4:O4)>40,SUM(C4:O4)-40,0) -- Jacob (MVP - Excel) "How do I?" wrote: > I need somone to tell me a formula to copy and paste into a cell in my excel > worksheet. I need this for work today! Can't figure i...

Hi guys, xl97 question. Is there a way to have data labels enabled** for all points plotted but with the labels automatically *excluded* for any zero values ? The exclusion is desired for aesthetic reasons. **e.g.: via Data Labels > Show value in say, Format Data Series dialog I find that these "zero" values showing on say, a column chart, have to be individually selected and removed on each series in a chart, a pretty tedious task. The values plotted are derived through formula and there would always be a couple of points here and there evaluating to zero values from time-to...

The two tck_201_ fields are text boxes on a form that are being set by to calculated text boxes on a different form!subform. As you can see by the debug statements and results, the values will just not transfer. I have set the format for both of the TCK _201_ fields to Fixed! Any Ideas? Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates Debug.Print Me.TCK_201_Done Debug.Print Me.TCK_201_Tot Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Debug.Print Forms!frmPT...

I've included a custom field on the order form. It's a bit field with the values YES and NO. The default value is NO. Our employees use this field to tell the system, if an order has been complete and is ready for invoicing. (We do not want to use the system possibility of completing an order). I've made a custom view of orders, where the view criteria is, that the value of this bit field is NO. And another view where the view criteria is, that the value is YES. However, when I select these views, I cannot see any orders. Could someone please explain, what I'm doing wr...

Hi All, How can I compare values in two columns (columns are in different tables) ? I also want to display the values that dont match in a seperate table. For eg: If values in Column1 from Table1 does not match Values in Column1 from Table2 then display those values in a seperate table Is there a way to do this? THanks in advance Under query types, there is one for unmatched data and one for matched. -- Milton Purdy ACCESS State of Arkansas "sam" wrote: > Hi All, > > > How can I compare values in two columns (columns are in differe...

I Need a Formula which can tell me eg. on seperate wotksheet a report of which product is chipset and from which suppliers.Thanks for any help I get. A B C 1 Product 1 Supplier 2 £10.00 2 Product 2 Supplier 1 £8.00 3 Product 3 Supplier 2 £8.00 4 Product 2 Supplier 2 £6.00 5 Product 1 Supplier 2 £11.00 6 Product 3 Supplier 1 £7.00 Farid, I think you mean:- cheapest - and not chipset. "Farid" wrote: > I Need a Formula which can tell me eg. on seperate wotksheet a report of > which product is chipset and from which ...

I have a two times (ex 8:30 AM and 5:30 PM) I want to figure out the number of hours from the start of one time to the end of the other time. What formula do I use? The formula is =[cell showing 5:30 PM]-[cell showing 8:30 AM]. Format the answer as 0.00 and the formula will show hours and fractions thereof (e.g. 4 1/2 hours will show as 4.50). "Joe" <anonymous@discussions.microsoft.com> wrote in message news:3db001c4a662$2dd276b0$a501280a@phx.gbl... >I have a two times (ex 8:30 AM and 5:30 PM) I want to > figure out the number of hours from the start of one time...

Hi, Suppose I have a worksheet with the following data: (column1)......... (column2) Savings..................I.D. -243...................... 01 -205...................... 02 -165.......................03 -87.........................04 57..........................05 109........................06 205........................07 303........................08 What I need to do is look up the smallest -positive- number in the "Savings" column and the look over to the "I.D." column and retrieve the corresponding I.D. So in the above example, look up the value 57 and then re...

I use auto-filter to switch a spreadsheet between a terse view and verbose view. Column B is auto-filtered, and it's either blank or non-blank. Call it a "tag column." Some column B cells have constants; others, formulas. It works fine. Can the same effect be achieved somehow to hide columns rather than rows? I'd like one row to be a "tag row." Then, I'd like to switch between terse view and verbose view by hiding columns based on what's in the tag row for each column. Can you suggest an easy way to do this without VBA? The "custom views" f...

I need help setting up a formula on a summary page. I have a ton of worsheets that are all set up the same. My summary page is set up differently but I need the total off each tab linked back to my summary page. I am working in Column Q and the info I need is in cell F28 on each sheet. An example of my formula now is ='T-4-1'!$F$28. Thanks in advance Zach f Try this one Zach f http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "zach f" <zachf@discussions.microsoft.com> wrote in message news:6EBBC383-B4C3-4B46-BE72-...

Hi all, I'm looking for an automated way to duplicate the last row in a worksheet as soon as it has been edited. I need the new row to be empty but maintain the formulas of the previous row. Is this possible please? I'm working on a continuous form for other users so there's no way of knowing how many rows will be needed in advance. I've never done anything like this in excel before so I'm not even sure if it's possible! Thanks a lot, Ciar=E1n I do something similar in a "Logbook" file I made for our shop. What I did was create the formulas and formatting ...

Hi all, i have a spreadsheet formatted as below Sheet1 Name Pin Time Fred 3325 3.25 Dave 3326 5.42 Chris 3327 5.42 Sheet 2 Name Pin Time Bob 2322 Dave 3325 Fred 3326 Chris 3327 What i want is a formula in the time column of sheet 2 that will look in sheet 2 first and say if that pin number matches a pin number in sheet 1 then in the Time Column of sheet 2 the Time Value of sheet 1 should be displayed The names in sheet 2 and sheet 1 will not be in the same row but will be in the same column so the formula will need to look at the whole ...

What function formulation is required to round up calculated dollar amounts in half dollar increments? Hi, Try this =CEILING(A1,0.5) Where a1 is your calculated dollar amount. Or you can wrap your formula with the ceiling formula =CEILING(your formula,0.5) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "DC" wrote: > What function formulation is required to round up calculated dollar amounts > in half dollar increments? &quo...

I am wanting to open a form within a form based on the value input on a field on the main form. How do I accomplish this? I am a newbie to Access 2007. Thank you!!! Don't have 2007, but in 2003 you could change which form a subform control was displaying with <subform>.SourceObject = "XXXX" "Deb" wrote: > I am wanting to open a form within a form based on the value input on a field > on the main form. How do I accomplish this? I am a newbie to Access 2007. > > Thank you!!! ...

Hi, Although I have just posted I thought this might be worth a separate posting. Over the last few months I have learnt some of the basic formulas used and at times have had the idea of combining different formulas together as I have seen in books and indeed in this forum. My question is is there a set of rules which would help me to construct them (which order and use of special characters )(,; etc) For example yesterday I had the idea of combining VLOOKUP with an OFFSET both of which I can workout separately but wouldn't have a clue where to start if I wanted to use them toget...

I do a lot of work in cleaning up mailing lists other people have generated. When I get any date column going, I usually =Value() it, to find out if any of the dates are bogus. Sometimes they are real dates, and sometimes they are "08/12/2006" and they get converted and formatted. I am confronted with a list which has the dates as "Aug 12, 2006" and I'm trying to figure how to get them as real dates. Any help would be gratefully appreciated. -- Regards, P D Sterling Dallas TX As long as they're all in the same format that you posted, you can use TTC (Text ...

On a graph I have the following trend line formula. y=6E+06e to the power of -5.1056x. If x=2 what would be the formula that I put into excel. Thanks Hugh - Looks like the Exponential type of trendline. Here's a short answer: You'll need more precision to get reliable results. So first select the textbox on the chart, and press the Increase Decimal button numerous times until 15 digits are displayed. Then, on a worksheet, enter the 15-digit values, like this: =612345.123456789*EXP(-5.10512345678901*2) Here's some longer answers: The Exponential type of trendline use...

Explanation of the question I posted yesterday. Col C Col D Col F 6 India 100 6 Germany Need a formula which averages all of col F with 6 in Col C and Germany in Col D 3 China 50 6 Germany Need a formula which averages all of col F with 6 in Col C and Germany in Col D so i was thinking if there was a way to identfy cells with formula vs. constant i could use sumifs to avoid circularity. thanks, RK see the original post -- Biff Microsoft Excel M...

I would like to export some figures that are calculations into another file, but have only the fixed values (i.e. eliminate the forumlas) when I export the data. How is this done? S. Rosenthal napsal(a): > I would like to export some figures that are calculations > into another file, but have only the fixed values (i.e. > eliminate the forumlas) when I export the data. How is > this done? Select cells, copy them and select the target. Use right mouse button and choose Paste special - Values. That's all. Marian Use Copy/Paste Special [Values]. David Hager Excel ...