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