outputting values to a range from one formula

I wish to generate a table automatically by means of a single
formula that applies an iteration on a starting given value with
a given step, and the computed values are posted/entered
automtically into cells from a given cell onwards, say below
it, until the computed value reaches a certain given limt.
This is somehow the inverse of INDIRECT or of OFFSET. These
can pull values from a variable addresses of cells. What I need
is to push values into a variable addresses of cells.

Can anyone help me on that??

Thanks.  :confused

--
Shafe
-----------------------------------------------------------------------
Shafee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489
View this thread: http://www.excelforum.com/showthread.php?threadid=26529

0
9/30/2004 10:21:20 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
652 Views

Similar Articles

[PageSpeed] 54

Hi!

A bit more detail would be helpful.
First the distinction between "pull" and "push" is effectively th
difference between using a function in a cell to pull in a value an
using VBA to push a value into the cell. 


More to the point, however, can you indicate the sort of sequence yo
want to generate; is it linear ; where and how it gets its "seed" an
end value.
e.g. if you wanted (trivial case) to start in A1 and put 21,23,25,2
etc in col A, then A1 = 21; A2=A1 + 2 and copy A2 down does it. 
condition to limit the values to less than a given value (MAX) woul
maybe look like =if(A1+2>MAX,"",A1+2)

But I think you might have something more complex in mind? If so (an
bearing in mind you would have to have the above formulae in "more tha
enough" cells) VBA would be my choice.

What you describe is pretty much what Edit>Fill>Series does, if all yo
want to do is a "one-off".


Al

--
Alf
-----------------------------------------------------------------------
AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478
View this thread: http://www.excelforum.com/showthread.php?threadid=26529

0
9/30/2004 11:25:08 PM
Reply:

Similar Artilces:

If statement- formula
Well I'm stumped again. If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999 thanx, ~Julz Hi what do you mean with 'enter contents'?. Do you want to add them? If yes try =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199 99,"<>xyz",close!ae2:ae19999) -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Well I'm stumped again. > > If close!ag2:ag19999 contains "xyz" enter contents of > close!ag2:ag19999, otherwise enter cont...

Send data from one form to another
Hi All, I want to know if it,s possible to send data from 1 form to another totally unrelated form. The situation I have it one form is my MRP requirments. I want to open another form PURCHASE ORDER (PO). I was wanting to add a button to the MRP form that would send the data to the PO form. The MRP form may change to multiple items and then add these different items to the same PO. Does anyone have any suggestions on how I could do this or if it can even be done. -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.as...

Sub to check and report any formula returned error (eg: #REF!)
I need a sub to run a quick check through a list of sheets (using their codenames) filled with a ton of formulae, and pop up an all clear msg if there are no errors (eg: #REF!) returned in any formula cell. If there are errors, msg will list the affected codenames. Thanks hi, Max ! > I need a sub to run a quick check through a list of sheets (using their codenames) filled with a ton of formulae > and pop up an all clear msg if there are no errors (eg: #REF!) returned in any formula cell. > If there are errors, msg will list the affected codenames. Thanks try with som...

Copy Userform ListBox AND TextBox values
How can I copy the values from ListBox1 and TextBox2 from the same Userform into the same cell on a worksheet? I have the code to copy either one to the correct location but I need to merge the values from both, seperated by a slash (/). I have tried using the following but it will paste "FALSE" into the appropriate cell: With Target .Value = ListBox1.Value & " / " & .Value = TextBox2.Text 'copies data to column B End With Thanks. -- Traa Dy Liooar Jock Ignore last - I have managed to do it. -- Traa Dy Liooar Jock ...

How do I make two lines in one cell?
since you cant hit enter to make 2 lines in one cell how do you do it? Press alt + enter together. It's easy when you know how! :-) Alan P. "kpeters" <kpeters@discussions.microsoft.com> wrote in message news:CAEFD0EB-26F1-4AAB-9D4F-86DF4E013B9F@microsoft.com... > since you cant hit enter to make 2 lines in one cell how do you do it? if you mean that you want to add more text into the cell, then increase the row height and "wrap the text" "kpeters" <kpeters@discussions.microsoft.com> wrote in message news:CAEFD0EB-26F1-4AAB-9D4F-86DF...

Absolute Values and Column Insertions
I've created a formula in column A that adds up some cells in column D. Ex: =sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! Please don't multi-post See answer in other newsgroup -- Kind regards, Niek Otten Mic...

uninstalling microsoft office 2001 value pack
Hi, I already have office 2001 on my machine, and recently installed the value pack. i noticed word became notably slower and so for now i just wish to unistall the value pack. how can i do that cleanly. any ideas why my machine would slow down like that. i still do have 1.2 Gig of hard disk space available. thanks. ...

Copy a file with only values no formulas
Is there an easy way to copy a workbook so that it only has the values and not the formulas? Thank you. -- Lee Coleman Right click on a sheet tab and select all sheets. Then select all cells on the active sheet. Do a copy, then Edit, paste special, values. Right click on a sheet tab and select ungroup sheets. Save the workbook under a different name!!!! Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Lee Coleman" <lee@nospam.jjlgreenhouses.com> wrote in message news:OIOx4$FeHHA.4020@TK2MSFTN...

Creating one trendline for multiple series
Hi, I have a document withfour different series and these are all plotted onto one curve. I want a trendline that fits all four series of data onto one trendline. I don't want to create a new series with all of the data and then fit a trendline to that. Is there any way I can just fit one to all four sets of data? Thanks > I don't want to create a new series with all of the data and then fit a > trendline to that. Is there any way I can just fit one to all four sets of > data? Unfortunately this is how it has to be done. - Jon ------- Jon Peltier, Microsoft Excel...

Reading ranges and copying data from Excel when it is not open
Is there a way in code to copy data out of an Excel file even if Excel is not up and running? If Excel were open, I'd copy, say, the first 30 rows and paste the info to PowerPoint. Then, since rows 1-5 are for column headings, I'd hide rows 6-30 and copy a new range which would look involve 30 rows, but since rows 6-30 were hidden would be 1-5 and then 31-55 as a contiguous block. I am trying to do this in VBA without opening Excel and instatiating objects, etc. Is it possible? What VBA commands would I use? You would need to treat the excel file as a database and use ADO to g...

How to average a column, but exclude zero AND negative values?
I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero values, but what about zero AND Negative? Thanks!! =AVerage(IF(A1:A10)>0,A1:A10)) Still array entered. KMHarpe wrote: > > I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero > values, but what about zero AND Negative? > > Thanks!! -- Dave Peterson ...

IF String value the....
I am trying to calculate a formula based on the right most character in a cell. For instance if a cell had a entry like Baker T. I want to calculate a formula if the T is there and another if the T is missing. Thanks in advance Hi use =IF(RIGHT(A1,1)="T","do something","something else") -- Regards Frank Kabel Frankfurt, Germany jolene wrote: > I am trying to calculate a formula based on the right most character > in a cell. > For instance if a cell had a entry like Baker T. I want to calculate > a formula if the T is there and another if the...

Request To Shorten A Formula
Hello, don't want to be pest on my Duplicate Questions, but I was hoping someone could give me a shorter formula for the one I have here. I have 31 sheets representing 31 days (DAY1 - DAY31), of the calender in which I check for dupes in the Phone Number Column E6-E35 in all sheets, This is the Formula that works, but I want it shorter if Possible.. Thanks for any Help: =SUM(COUNTIF(INDIRECT("DAY"&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}&"!E6:E35"),"="&E6)) -- Mhz ----------------------------------...

Automatic Calculation of Table Formulas
Is there a way to set Word so that a target cell automatically re-calculates whenver any of the cells used in the formula is changes? Only if the components of the formula are provided from the results of form fields in a protected form and you have the calculate on exit check box properties of the fields checked. Otherwise you would have to force an update of the fields in the calculation. You can do that with a macro, such as that used as an example at http://www.gmayor.com/installing_macro.htm Alternatively you can insert an Excel table and have the full panoply of Excel fu...

print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

Using two MS Money accounts on one PC.
I am using MS Money 2003 Standard Edition. Is there any way I can set up accounts for two people on the same computer with the one program without running into problems? TIA. File - Open/New create two files with different names. Mr. Mrs. >-----Original Message----- >I am using MS Money 2003 Standard Edition. Is there any way I can set up >accounts for two people on the same computer with the one program >without running into problems? TIA. >. > Besides the information already posted, use shortcuts to the two different files to start the application as starting Mone...

Paste Formulas Only
How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? Pick up only the formulas select the range to copy, then do Edit=>Goto =>Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" <Brett@discussions.microsoft.com> wrote in message news:F577C7FC-D529-4BD2-8182-A14AFB82F595@microsoft.com... > How can you copy a range and then paste cell contents only if the cell > contents is a formula (cell contents begins with "=")? Isn't it so that it ...

Formula #32
I need a formula for this: If C1 is blank and B1 is less than 4/03/2008 count as 1. Answer: 2 A B C 1 Almond Joy 04/01/2008 2 Baby Ruth 04/02/2008 3 MARS 03/28/2008 04/01/2008 4 Mounds 04/10/2008 04/15/2008 5 Reeses 05/01/2008 6 Starburst 03/25/2008 03/31/2008 Thank you! Try this: =3DSUMPRODUCT((C1:C10=3D"")*(B1:B10<--"4/03/2008")) Adjust the ranges to suit. Hope this helps. Pete On Sep 2, 10:08=A0pm, Accesscrzy <Accessc...@discussions.microsoft.com> wrote: > I need a fo...

Excel XP automatic equals sign when formula bar clicked
In Excel 2000, you could click on a cell, then click on the formula bar and an equals sign would pop up in anticipation of a formula to be written. How can you make Excel 2002 duplicate this behavior? Hi Mike! This was removed from Excel 2002 onwards. The closest you'll get is: View > Toolbars > Customize Under the Insert list you'll find "=" Drag it to a toolbar near the formula bar. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. &q...

dragging formula
I have a small formula that wont drag down the column. The answer of the formula is what is dragging. How do I fix this so I have the correct answer in each cell? =left(a3,5) mary Are you getting an error? What happens when you try and do it? Are the cells blank afterwards? Is the worksheet/cell protected? The formula looks fine. If you are dragging by the little black cross in the bottom right hand corner of the cell, there should be no problems. Andy. "mary kay dougherty" <marykay.dougherty@necsa.com> wrote in message news:066501c38f49$d2d20670$a001280a@phx.gbl.....

dafault value for date not working with Date() but Now() is workin
Got a table with existing data, with simple date field on a form that I want to default to todays date. I have tried inputting the Date() into the table default value for the date, but I get an error that says "Unknown function in 'Date' in validation expression or default value on 'Data.date'. Data is the table name. If I enter Now() into the default value for date, it works just fine. But, the reports that are generated, don't show the records with the date and time in them. I can go back to the table and manually delete the time from those records...

Counting contents for Saturday dates within a range
I am trying to find all Saturdays within a range(on row 14), and if a Saturday is detected, then I would like to add the values in the corresponding row for the Saturday only dates(available in row 16). After going through the date range, and the total sum is zero, then I would like to return false. I also am trying to avoid doing this via a macro as it should be automatically re-calculated if a cell changes. Currently I have the cell formatted as follows that will provide a true or false response, if any Saturday is detected with values in the corresponding row. I created an IsSaturday fun...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

Portfolio market value report over time
The Portfolio Manager shows a Market Value at the bottom of the screen. I can change the "As of" date near the top of the screen and the Market Value will show the value as of that date. I would love to run a report that shows the Market Value for a user specified range of dates, with a selectable granularity of day/week/month/year, etc. Is there such a report? In microsoft.public.money, cgunther wrote: >The Portfolio Manager shows a Market Value at the bottom of the screen. I >can change the "As of" date near the top of the screen and the Market Value >...

Formula Syntax
Hi Excel Forum, I believe the following formulas all find the MINIMUM value that is NO = zero (<>0) and produce the same results. I am unsure of their syntax Can you explain the syntax of the following formulas and th differences between them: =MIN(IF(A1:A20,A1:A20)) =MIN(IF(A1:A20<>0,A1:A20)) =MIN(IF(A1:A20<>0,A1:A20,"")) How does the first formula work and, if there is a zero 0 avoi returning a zero 0 without mentioning NOT = zero <>0? what are the quotation marks for and what do they do? When would it be preferable to use one syntax rather than anoth...