Automatic Filling Cells - Formula That Expands

Jun 3, 2014

I am currently streaming financial data into excel which updates on a minute by minute basis. Every minute a new row of data is automatically inputted based on market activity. Is there any formula/function which would expand to cover the new data as the amount of cells increases? Is there any way that I can have it so that the function/formula only looks at the last 20 or so cells, so its like a moving/rolling formula/function?

View 3 Replies


ADVERTISEMENT

Automatic Filling

Feb 14, 2010

Iím not even sure I can do this- but- I want to be able to have a few columns, 3 of them I want to be Item #, Item, Cost.

Is there a way to put the Item # (Number) in, then have the other 2 automatically come up?

View 9 Replies View Related

Pivot Table Fields Expands Automatically When Updating Data (only Where Data Has Been Changed)

Jan 11, 2013

I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?

It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.

View 1 Replies View Related

Filling A Column With A Formula

Jan 25, 2010

Is there a way to fill all rows of a column with the same formula without having to copy and paste it? My table is huge (about 6000 rows) and I need to perform the same computation on each row.

View 8 Replies View Related

Filling Cells According To Time

Jul 22, 2009

I'm working on some data dealing with time fields, and I need to have them counted in 15 minute intervals.

For example, one of the entries listed in a column has an event starting at 02:25:00 and ending at 02:55:00 (30 minutes). Along the top row are times listed in 15-minute intervals, e.g. 13:00:00, 13:15:00, 13:30:00, etc. In the corresponding cells, if the entry took place within a particular interval, I need it to say "1", otherwise have it say "0" or "-" or something denoting no event.

An IF statement was used, but it didn't seem to work.

If that's a little hard to follow, check the example attachment for a better idea of what I'm trying to describe.

In the example spreadsheet, Row 2 (manually typed in) is what I'm looking for. Row 3 is the (failed) attempt to use the IF statement.

View 14 Replies View Related

Mandatory Cells And Filling In Next Row

Jul 21, 2014

I am creating an excel file for my company. This file contains 18 headers (from Columns A to R), all of which are mandatory to be completed.

This file is used by several other colleagues with the intention to update our records almost on a daily basis, and saved by them daily before being checked by me.

I would like to know if there is a macro to be used in order to prevent them from saving the file if all the fields are not completed. (ie. A5 to R5)

This function should only run right up to the next blank row.

Eventually, I would like the users to complete all cells from A5 to R5.

Should a single cell not be completed, an error message will pop-up.

When the next person accesses the file to enter their data underneath, it will again, not allow them to save until all the mandatory fields are filled out.

I have looked at another very similar link but it does not work and I do not know how to tweak it.

The example given is for when the data is in Column A.

The headers on my file are on Row 1 (Column A to R).

Mandatory Cells and knowing to fill in the next row!

View 5 Replies View Related

Filling Cells According To A Calculation

Jun 29, 2008

I have a spreadsheet that is designed to aid a production department to complete and track their orders. I need two things from this.

1) I have an order of 500 baskets. Baskets are stacked, but the heights vary from 16 to 20. I need the spreadsheet to be able to work this out for me.
Example:
500 baskets 20 high = 25 stacks.
500 baskets 16 high = 31 stacks and 4 baskets.

Excel would work the last equation out as 31.25. How can I get excel to use the remainder (in this case 0.25) to work out the required number of baskets?

2) Using the above, I need the sheet to fill out a certain area with information. So, if the order is 500 baskets at 20 high, I need 25 of the cells in the specific area to display the number 20. Likewise, if the order is 500 baskets at 16 high, I need 31 cells to display 16 and the remaining cell to show 4.

View 9 Replies View Related

Filling Up The Blank Cells

Mar 6, 2009

Got two columns one with group name and other with value. given below is the data which i have

Retail group25635012030Technology group24520520245Wholesale group24334024450
the green cells are blank , i want them to fill up by the group name i.e first four rows belong to retail group, so i want retail group to be filled in the blank cells upto technology group from thereon technology group to be filled in till whole sale group.

View 9 Replies View Related

VBA - SUMIFS Formula Not Updating Cell When Filling Down?

Jul 27, 2012

I have the below formula working correctly. However the issue is when it pastes and fills down values its using H1 reference in the formula even when it goes to the next column. So basically when it goes to cell I2 it should update the sumifs formula to lookup I1 instead of H1 and so on and so forth for all columns till the loop stops.

VB:
Sheet1.Activate
Dim frmla As String
frmla = "=SUMIFS($C:$C,$A:$A,H$1,$B:$B,$G2)"
Range("H2").Activate
Do Until ActiveCell.Offset(-1, 0) = ""
ActiveCell = frmla
ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1).Activate
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Selection.FillDown
ActiveCell.Offset(0, 1).Activate

View 1 Replies View Related

Filling To Right So Don't Have To Edit Four Parts Of Formula 350 Times

Jan 19, 2014

I have this huge formula and I want to run it in one row, across 350 columns:

=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)))

The only thing I need to change from one column to the next is the four places where it says ROW(3:3). I need that to ratchet up as it goes across to the next. The cell to the right should say:

=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)))

Then the next cell to the right should say:

=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)))

etc., etc.

How can I fill across so those go up and I don't have to fill in those four numbers 350 times?!

View 6 Replies View Related

Index Row Reference When Filling Formula Across Columns

Oct 16, 2013

In Column A, starting in row 8, I have a list of part nos. This list can be any number of rows long.

Further down the sheet, I want to put that same list into a horizontal array. I currently have this formula in the first column. It will, of course, pull the value from the 1st row.

=IF(ISBLANK(A8),"",A8)

However, when I fill across all of my columns, it changes the Cell reference to B8, C8, etc. whereas I need it to change to A9, A10, etc.

Writing a formula that will index the ROW reference as I fill across?

View 3 Replies View Related

Increment Formula Row Number Filling Across Columns

Aug 24, 2007

I am trying to increment a row when i copy it across columns. I have searched for a couple hours on how to do this, but I have not come up with a solution that I can understand that works for my situation.

I found this page, but I guess I am too novice to comprehend it. [url]

I would like the number in this formula to increment when i copy it to the adjacent column.

This formula is in B178.

=IF(B8="x",A8,"")

So in C178 I want it to be

=IF(B9="x",A9,"")

View 5 Replies View Related

Increment Part Of Formula Row Reference When Filling Across

Jan 15, 2008

I have a spreadsheet containing quite a few lookups etc. I am trying to copy a cell across about 10 columns. This has to be done for about 50 different rows on about 20 different sheets, so I am looking for an alteration to the formula to help me rather than typing in the formula over and over;

='Basic Labour'!AD6*Rates!$E$526

Say this is in AD6, then in AE6 I would require

='Basic Labour'!AE6*Rates!$E$527

and so on across the region to be autofilled...

(It is multiplying the same cell in a different sheet against the 'next row down' in a rates lookup sheet).

View 4 Replies View Related

Filling In Information In Blank Cells

Dec 18, 2008

I have a sheet that has been created with a pivott table, and as such has a lot of blank cells in the description column, and I want to do a little macro to fill all of these. Basically, what I was thinking, was that the macro finds the last cell with data in it in column A. Then it moves up one cell and checks if its empty. If it is empty, then it copies the information from the cell immediatly below then moves up one and does it all again. If it is not empty then it just moves up one cell and does it all again.

View 3 Replies View Related

Filling In Blank Cells With Value From Another Cell

Apr 9, 2009

What I would like to do is to take the name values in column B and fill that into the region of blank cells below. I know that a special cells function could probably do this paired with a looping structure. I'm new to VBA and am trying to learn it so that I can use it comfortably.

View 8 Replies View Related

Auto-Filling Cells Horizontally

Feb 14, 2012

I want to be able to fill with the formula not modified (e.g., I do not want to use INDIRECT, OFFSET, etc.)

In Cell P3, I have the function "=IF(ISBLANK(C25),"",C25)."

I need to fill the next cells (horizontally) so that this increments numerically,

i.e., in Cell Q3, the function should read "=IF(ISBLANK(C26),"",C26)."

Is this possible without manually typing, and without having to modify the actual functions?

View 7 Replies View Related

Filling Array With Specific Cells?

Mar 29, 2013

I am trying to use the below code to fill a combobox with unique values. It works great except I can't figure out a way to replace BLOCK 1 with BLOCK 2. I want to use BLOCK 2 to populate mgNames so that only specific values are filled into mgNames. Currently BLOCK 1 just fills mgNames with an entire range without any conditions. how I can get BLOCK 2 to work in the way i'm intending it to?

Code:
Sub findNames()
Dim mgNames As Variant
Dim myCollection As New Collection

[Code]....

View 4 Replies View Related

Automatically Filling Cells If A:C Match A:C

Jan 25, 2007

I'm looking for a formula that when copied down in row D would automatically fill in what is in colum D above if columns A:C match.

So,

If A25:C25=A1:C24 (for example A25:C25 = A8:C8) then D25 will automatically fill in the content of D8. Then next it would be A26:C26 match A1:A25 and so on.....

View 9 Replies View Related

Filling Blank Cells In A Column

Sep 1, 2009

I have 6,000 rows of check detail. The check number is listed in both columns B & C.

If the check paid multiple invoices the check number is not repeated in columns B & C.

I would like to fill the cells in those columns with the appropriate check number. Data looks something like this-

item ck_01 ck_02 vendor ck_date
1 10100 10100 ABC 09-01-09
2 10101 10101 ALLIED 09-01-09
3 ALLIED 09-01-09
4 ALLIED 09-01-09
5 10102 10102 BEACH 09-01-09
6 BEACH 09-01-09
7 10103 10103 CAP 09-01-09
8 10104 10104 DART 09-01-09

How can I fill the balnks with the check number (above)?

View 9 Replies View Related

Filling Cells With Data From Userforms

Sep 26, 2007

I have a userform and on it is a combo box with the weeks in each month in it. A user has to select a week to enter figures for and then enter the figures into text boxes on the same form. When the click submit (command button), based on what week they've already selected, my code will put the figures into the spreadsheet in the appropriate place.

Sub december1()
Worksheets("Dec").Activate
If Figures1.TextBox1.Value >= "1" Then Range("c16") = Figures1.TextBox1.Value
If Figures1.TextBox2.Value >= "1" Then Range("c17") = Val(Figures1.TextBox2.Value) / 100#
If Figures1.TextBox3.Value >= "1" Then Range("c18") = Figures1.TextBox3.Value
If Figures1.TextBox4.Value >= "1" Then Range("c22") = Figures1.TextBox4.Value
If Figures1.TextBox5.Value >= "1" Then Range("c23") = Figures1.TextBox5.Value
If Figures1.TextBox6.Value >= "1" Then Range("c28") = Figures1.TextBox6.Value
If Figures1.TextBox7.Value >= "1" Then Range("c29") = Val(Figures1.TextBox7.Value) / 100#...........................

View 3 Replies View Related

Certain Dropdown Items Filling Different Information Into Cells?

Oct 25, 2012

Wasn't sure what the easy way to do this is. I'm trying to have a drop down menu where choosing a certain item triggers specific information to fill multiple cells. For instance, choosing Work Contacts in a drop down menu brings up four different people's names in cells B43-46 and their phone number in C43-46 and email in D43-46. Choosing another item for instance, Personal Contacts, would reveal a different set of four names and information.

View 2 Replies View Related

Data Validation And Auto Filling Cells

Sep 26, 2009

Starting to struggle a bit here. Trying to create a user form, with help from this website I was able to create 2 dropdown lists based on data validation and information from another sheet in the same workbook. I have one cell B7 that uses data validation and sheet "Eq List" to create a drop down list of departments that the user can select from. Once B7 is filled in, it creates the 2nd dropdown list in B9 so the user can pick "Equip Desc".

Where I am trying to fill B11 which should automatically give me back the Mach ID that is related to B7 & B9. Am I going about this the right way? I have ttached a sample file to make it easier to understand.

View 4 Replies View Related

Filling Empty Cells With The Right Data From Another Sheet

Jun 26, 2009

I got my invoice and my journal. Once I finish my invoices I send all to the journal, but I am having a little problem.... Here is my code

View 2 Replies View Related

Filling In Cells Based On Date Ranges

Aug 20, 2008

I have the following:

A/B/C/D/E/F/G/H/I/J/K
April 2008 / August 2008/Jan/Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct....

How can I get excel to fill in a constant number say '3' automatically based on the date range in column A and B. For example, in this case Since the date range is from April to August how can I get excel to automatically fill in 3 for apr/may/jun/jul/aug

View 9 Replies View Related

Filling Cells Based On Previous Row Information (text)

Mar 22, 2012

I have a column of data (Column T) which has about 10 different words in it (varieties of fruit). What I want to achieve is based on the data in column T I want to fill Column U with information, let me put it in an example.

Column T has the following words in 2000 rows. Thompson, Crimson, Sable, Superior, Midnight, Ralli.

If Cell T4 for example has 'Thompson' or 'Superior' I want cell U4 to say 'white', but if cell T4 has 'Crimson' or 'Ralli' I want it to say 'Red'. If it has Midnight I want it to say 'black'.

I think this can be done using a vlookup maybe? I don't have to go down 2000 rows to fill this information in.

View 4 Replies View Related

Automatically Filling In Cells Based On Information From Another Sheet

Apr 1, 2013

I want to make things easier for myself when filling in my worksheets by having certain cells autofill based on a product number.

So I have my main worksheet and I have my product information sheet. When I type in the product number in cell 13C, I want it to auto fill cell 13B with the product name which is located in collumn A on the product info sheet, but dependent upon that product number. Collumn B in my product info sheet has all of the product numbers. From there I would need 13G on my main worksheet to fill in with Collumn D from the product sheet, but again dependent upon that product number.

There are more that need to fill yet, but I can figure out what to do once I have a starting point with the above I believe.

View 8 Replies View Related

Filling In Multiple Cells After A Choice From A Drop Down List

Feb 17, 2009

If I choose 1 thing from a list I want it to then fill in many cells with info.

For example: I have a color list to choose from, once I make my choice cells near it fill in with 1.5lbs yellow, 2lbs blue, 4lbs red,.....or what ever I have established as the "recipe" for my color choice. When I choose a different color from my list these cells each fill in with different values.

View 9 Replies View Related

Macro Code Executes Very Slowly: Filling Some Cells On Different Worksheets

Nov 9, 2006

I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.

Private Sub UserForm_Activate()
Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet
Dim rngFound As Range
Set wsCB = Sheets("Current Bonds")
Set wsLL = Sheets("Landlords")
Set wsBond = Sheets("Bond")
TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy")
RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy")
Application. ScreenUpdating = False
10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number")
strFindMe = MyBond
With wsCB
Set rngData = .Range("c13:c490").......................

View 5 Replies View Related

Automatic Formula For 1 Column

Dec 29, 2008

I have two colums as shown below:.....

The SHIFT column has the word DAY under it. The TIME IN column is empty. In the Shift column below if the value entered is "Day" I want the Time In column to say "1pm", If the value entered is "Night", I want the Time In column to say "7pm", I need a formula that will fill in the TIME IN column automatically based on whats on the SHIFT column.

View 4 Replies View Related

Keep Formula When Inserting (automatic)?

Feb 1, 2014

When inserting a row in lets say row 5, i want the formula to be kept for that row. (e5) Table doesnt seems to work. Any macro for this, and no button but automatically.?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved