#### formula for cell plus x amount of lines

```Hi everyone.  I need a bit of help with a specific formula.  I have 2
spreadsheets, one is a working list of daily data with a weekly total,
the other gives me a summary of the totals for each week.

When Im inserting the formula for each weekly total, I can copy the
line above (eg previous week) but excel automatically assigns the next
line from my source sheet eg: =SUM(SALES!U1851) will become
=SUM(SALES!U1852)  What I want is a formula that says something like
=SUM(SALES!U1851 plus 100 rows).. this giving me the formula of
=SUM(SALES!U1951).  Is there a code for this or will I have to change
the line number manually each week?

Id be grateful for any help anyone can give.  I hope my explanation is
not too confusing.

Regards
Jan

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

```
 0
11/10/2003 7:03:57 PM
excel.misc 78881 articles. 5 followers.

1 Replies
422 Views

Similar Articles

[PageSpeed] 17

```Best to use a defined range that is self adjusting
insert>names>define>name it whatever>in the refers to box type
=offset(\$a\$1,0,0,counta(\$a:\$a),1)
then =sum(whatever)

--
Don Guillett
SalesAid Software
donaldb@281.com
"cousin_jammie" <cousin_jammie.wozea@excelforum-nospam.com> wrote in message
news:cousin_jammie.wozea@excelforum-nospam.com...
>
> Hi everyone.  I need a bit of help with a specific formula.  I have 2
> spreadsheets, one is a working list of daily data with a weekly total,
> the other gives me a summary of the totals for each week.
>
> When Im inserting the formula for each weekly total, I can copy the
> line above (eg previous week) but excel automatically assigns the next
> line from my source sheet eg: =SUM(SALES!U1851) will become
> =SUM(SALES!U1852)  What I want is a formula that says something like
> =SUM(SALES!U1851 plus 100 rows).. this giving me the formula of
> =SUM(SALES!U1951).  Is there a code for this or will I have to change
> the line number manually each week?
>
> Id be grateful for any help anyone can give.  I hope my explanation is
> not too confusing.
>
> Regards
> Jan
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

```
 0
Don
11/10/2003 7:44:02 PM

Similar Artilces:

Join two formulas
I have this formula: =SUMPRODUCT(--(YEAR('2003-2004'!\$A\$4:\$A\$10000)=2004),--(MONTH('2003-2004'!\$A \$4:\$A\$10000)=2),'2003-2004'!\$E\$4:\$E\$10000) And I only want this to be true if: {=SUM((builder="m")*(servicedby="Dave")*nc)} I don't think it is as easy as joining two together, but I tried: =IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--(YEAR('2003-2004'!\$A\$4 :\$A\$10000)=2004,--(MONTH('2003-2004'!\$A\$4:\$A\$10000)=2),('2003-2004'!\$E\$4:\$E\$ 10000))),1)) I also tried: =SUMPRODUCT(--(YEAR('2003-2...

how to highlight a cell so I know which point on the chart it is?
Hi, I am curious to see if anybody could create a visual relationship between a point on the chart and the data cell. Let's say if we have 1000 points on the chart, when I point to one of them--it will show the values--but I still have no idea where the point actually comes from. Or on the other hand, when I try to hightlight some cells using formats, the best thing is to see the points on the chart also being highlighted in some way. Thanks for any hints there. Windson: I am not sure whether I can help you here. If you select a data point and you move it Excel pops up a Goal Seek...

Hi, I have a client that has inventory on a single excel file on a office network. They want to be able to hide some cells from some users and not from others. They have mentioned they have group-level security setup on their network, so this could be used. What I am trying to do is hide some columns/rows and then password protecting the sheet, and also tried to password protect the range as well, but it does not seem to work. I have to unprotect the sheet to hide/unhide the column, then I have to remember to protect it again after. This can obvious lead to human error where the user ...

How do I add a formula to a range of cells
1) You can type a formula into a cell and then drag the that cell's fill handle (solid square in lower right corner) to fill other cells with the formula. You need to know about absolute and relative referencing - see Help I the cell is part of a vertical table, double clicking the fill handle is quicker than dragging it. 2) You can select a range of cell, type the formula and finish off with CTRL+ENTER. This fills the selected range with the same formula - with appropriate cell reference changes. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme ...

macro error in cell reference in the AUTOSAVE.XLA
I have a user who is using Excel 2000 that when he starts up Excel it comes up saying that there is a macro error in cell reference in the AUTOSAVE.XLA. Will replacing this file resolve this or is it caused by something else. Cheers, Dion Pauls Lots of people are using autosave.xla in xl2k without problems. It shouldn't hurt to delete that file and reinstall it from the distribution CD. Dion Pauls wrote: > > I have a user who is using Excel 2000 that when he starts > up Excel it comes up saying that there is a macro error > in cell reference in the AUTOSAVE.XLA. >...

Tightening up range in a Line Chart
This appears pretty basic, but I can not find a solution. I am comparing two sets of similar data which are between 800 and 1600. The Line Chart I have created allows for data on the vetical axis from 0 to 1800. The differences in the data would be more efficiently demonstrated if the range of the vertical axis were tightened to match the range of the data. Is there a way of doing this? I am working in Excel 2007. Thanks, Dano Format Axis/ Scale and choose your preferred min & max values instead of the automatically chosen ones. -- David Biddulph "Dano" <db_murray@y...

WLM Junk Mail Filtering by Subject Line content
In the old web based HOTMAIL, we could filter junk mail by examining Subject Line content (all, start or portion). I like the WLM both desktop and webbased versions, but I am unable to filter my junk mail by examing subject line content. Does this option still exist, if so how do I get to it? Thank you in advance for your assistance. -- Doug WLM is only a desktop version. Windows Live Hotmail(WLHm) is the web UI for Hotmail type accounts(Hotmail.com, Live.com, Msn.com) A Junk Mail setting to filter by subject is not an option in WLM or WLHm. One does have the o...

Playing Vedio clip in excel cell
Do anybody know how to play a vedio clip in a excell cell..... thanx Jagz Hi don't think this is really possible. And as in your other thead: why do you want to do such a thing with Excel? -- Regards Frank Kabel Frankfurt, Germany "Jagz" <jagjeetgulati@gmail.com> schrieb im Newsbeitrag news:1104577313.512252.303900@c13g2000cwb.googlegroups.com... > Do anybody know how to play a vedio clip in a excell cell..... > thanx > > Jagz > ...

why have my spreadsheets stopped calculating formulas?
The sheet calculates only those cells that I retype the formula for. If I change the values again it will not recalculate until I retype the formula again. Most likely, the calculation mode has been set to manual instead of automatic. Go to the Tools menu, choose Options, then the Calculation tab. There, check the Automatic check box. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dfbaskwill" <dfbaskwill@discussions.microsoft.com> wrote in message news:D3688C6C-AC98-4142-9B58-7D79B090B683@microsoft.com... > Th...

getting a y-value from a graph knowing the corresponding x-value
if you have a graph with three points plotted [(x1,y1),(x2,y2),(x3,y3)], can excel find y4 if you know the corresponding x4? -- lsu-i-like ------------------------------------------------------------------------ lsu-i-like's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14317 View this thread: http://www.excelforum.com/showthread.php?threadid=392733 You could use FORECAST() or TREND() to predict the y-value assuming that the points can be approximated by a straight line. You could use FORECAST() to predict the y-value assuming that the points can be ap...

Need Count Formula Help
Hello, I'm trying to write a count formula. Column A has just names, many with "New Hire" as a placeholder for upcoming heads added throughout 2005. Column B indicates the hire date --- only the "New Hire" labels will be in the 2005 year. Then I have twelve columns representing the months for 2005. I can't figure out how to write the formula, to appear at the bottom of each month's column, to count the total dates that fall within January 2005, within February 2005, within March 2005, etc. Any help is much appreciated. Thanks in advance! Corey Nevermin...

How do I move data without changing an associated formula?
I have created an Excel worksheet which I update with new values each day (golf scores). I keep the last 10 scores but want an average of the last 5 scores. How can I move the data one column to the left and not change the averaging formula so that when I post the new score it still uses the original formula (sum of G5 to K5 devided by 5). If you put a "\$" before any cell reference, it will "freeze" the formula value. So, if your formula references cell G5, you can use \$G5 to freeze the column or G\$5 to freeze the row or \$G\$5 to freeze the entire cell reference. ...

pasting linked cell from Exc and maintaining formatting no matter
I am linking a data cell from Excel into Word. I am using the paste special, paste link: unformatted text I make adjustments to the font and font size in Word. I goto Excel, and I modify the text in the cell and go back to Word and it changes. How can I get the formatting to stay the same, no matter what? In the WOrd document, press Alt+F9 to display the field codes and add the \* charformat switch inside the closing }, replacing \* mergeformat if it is there. Now apply the desired formatting to the L of Link then press Alt+F9 again to toggle off the display of the field co...

COPYing the content of a cell to a graph as Title
I need to copy the content of a Sheet1 cell (just a name to a graph (in the same xls file) as its title. Ctrl+C does not retain the info as I go back to the graph sheet to PASTE it on the title. When I write a macro, the code displays an error that says the procedure can't be performed. Help, please. Thanks. Guillermo. Click on the Current title of the chart. In the formula bar type Once you have the = in the formula bar, switch to the sheet that contains the cell you want to link to, then press enter If you don't currently have a title, enter it by going to Chart|Chart Options. ...

how do I draw a dotted line
I wish to draw a dotted or dashed line in excel, how do I do it Hi, If your drawing toolbar isn't visable, click View>Toolbars and select Drawing from the list. Select the line icon and draw your line where you want it, then right click on the line and select Format Autoshape, in the Colors and Lines tab you can adjust how your line looks. Hope this helps, Gav "blank flange" wrote: > I wish to draw a dotted or dashed line in excel, how do I do it ...

Line Chart
I have the following data I want to chart: 8 12 16 36 64 100 Mtl Cost \$15,207 \$19,079 \$22,950 \$42,308 \$69,408 \$104,252 Engr Cost \$16,640 \$24,960 \$33,280 \$74,880 \$133,120 \$208,000 When I created the chart the upper bounds of the y axis is \$350,000, and the Engr Cost series, although states it \$208,000, it's above the \$300,000. What am I doing wrong. Bob Hover your mouse above each axis in turn. Right-Click and select Format Axis. Go to the Scale page, and uncheck the auto boxes. Put in the values you feel are appropriate (Max, Min etc.) and click OK. If the visual effect is not w...

Formula Too Long #2
I am not sure what you are talking about with creating a name for the link. Could you please define a little more. Thanks. try creating a name for the link >then =Joe*bill > >"Todd" <todd@yahoo.com> wrote in message >news:0e3901c38f2f\$0f8a1b90\$a101280a@phx.gbl... >> Is there anyway to expand the amount of characters that a >> formula will allow? I am trying to link 14 different >> spreadsheets to one and I keep getting the formula is too >> long error. Any help will be appreciated. Thanks in >> advance. If you are simply addi...

Format X-Axis with Base Unit = Hour(s)
I dealing with hourly data I need to graphically present the data in hourly intervals. MS Excel only allows for Base unit selections of Day(s), Months(s) and Year(s). Is there a solution available to plot hourly indexed values without the graphical scaling on hourly intervals? [This followup was posted to microsoft.public.excel.charting with an email copy to Joe. Please use the newsgroup for further discussion.] The easy out is to use a XY Scatter chart. The more complicated solution is to use a XY Scatter chart with an additional (dummy) series that represents the values you want to s...

how do I UN-concatenate cells
I have a string of numbers 00633.84010.12740.0000.10101 that I need to break out into the separate components. Is the string of numbers always 5 numbers, a dot, 5 numbers, a dot, etc? If it's *always* in that format you can use a MID() function. If the separate components are between dots but irregularly spaced it becomes trickier. If it's the easy scenario, and supposing your string is in cell A1, try =MID(A1,1,5) =MID(A1,7,5) =MID(A1,13,5) etc Hi Julia, If you try Data > Text to Columns and follow the options, this may be what you are after. Regards Graham juli...

Can a formula determine if a cell is a particular colour?
Hi, I have an Excel 97 spreadsheet with heaps of rows in it, some of which have red rather than black text. Is it possible to have a formula that can show me the rows that are red so I can then sort on that column. For example, a formula something like: =if(cellcolour(A3)=red,1,0) Thanks a lot for any help. Tino, No worksheet formula, but you could create a simple UDF. You could try this technique that evolved out of previous threads between Harlan Grove and I. Create a function to get the colorindex of cells, namely Function ColorIndex(rng As Range, Optional font As Boolean = Fals...

Shortening SUMPRODUCT formulas
Hi all. This is a re-post, in a sense, of a question I posted last week to this forum. I work at a college and use SUMPRODUCT formulas to total up all kinds of stuff. For example, to calculate the number of people majoring in a particular area (in this case, Social Sciences and Arts), I use the following: =SUMPRODUCT(((Major="African/African Am Studies")+(Major="Ancient History")+(Major="Art")+(Major="Art History")+(Major="Asian Studies")+(Major="Chinese")+(Major="Classics")+(Major="Classical Studies")+(Major=&q...

Retrieve last non zero value in a range of cells
Hi How do I extract the last non zero value in a range of 5 Cells for Example: A1=95 B1=93 C1=98 D1=0 E1=0 From the example above I would require to choose the value 98 from the Range declared. Some weeks it is possible for all the values to be >0 so therefore I would need the Value in E1 other weeks it may be B1 Would it be possible to return the value I require into Cell G1 in the example above? Thanks Peter Entered in G1 =LOOKUP(2,1/(A1:E1<>0),A1:E1) Gord Dibben MS Excel MVP On Wed, 8 Jun 2011 11:55:19 -0700 (PDT), Pete <fell-walker@hotmail.co.uk> wrote: >...

Copying matched criteria plus relevant columns to new worksheet
I want to use code in a macro to search for criteria in a worksheet which allows the matched data to be copied (along with other relevant columns) to another worksheet in the same file. Would this involve some sort of loop? Any help apreciated. Thanks in advance, Matt there are several ways to do it. if you want to copy a hundred cells for three columns every time something in row 3 is matched somehting like the following could be used. sub try() tar=1 ' target column in sheet 2 for c = 1 to 34 step 3 ' this will be the look up column in sheet 1 if sheets("Sheet1&quo...

what is the format for an input cell?
I have Office - Student and Teacher Edition 2003 I am having trouble making a table ... I try to make a Data Table and try to define \$E\$3:\$G\$16 as the range in the row input cell area so I can make this a one-input data table and then I enter C\$9\$ for the column cell input area a pop up pops up saying that the input (row) cell reference is invalid I hope this not a bug that can only be fixed if you have the full version of the software any clues? it should be \$C\$9 Pumaman <Pumaman@discussions.microsoft.com> wrote in message news:D68327C9-5237-4353-8474-890677696F9...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...