Auto Insert A Row With Info Populated From The Row Above

Jan 14, 2010

to be able to do is have a stock control sheet that tells me how much of 1 item we have in stock which is easy, the hard part comes when this stock is sold as this stock can be sold to a number of different customers e.g.

We have 10,000 X Pens

Customer 1 buys 1,000
Customer 2 buys 6,000
Customer 3 buys 3,000

i need a way of saying that we have 10,000 pens but if we sold 1,000 then we can click a button (macro maybe) and that will automatically see that we have 10,000 for that line and we have only sold 1,000 so we need a line inserting saying that we have 9,000 remaing and so on.... The sheet needs to record who these goes to though, that the reason i was thinking of adding a row

If this doesnt make sense then i am happy to answer your questions?

i can upload an example excel sheet if required if someone can explain how i do this?

View 14 Replies


Vookup: Bring Back The Info In The Last Populated Cell On The Row

Mar 3, 2009

I want to lookup some data and bring back the info in the last populated cell on the row, rather than column 4. I have written the below but it is bring back info from Column Index number 4.


View 9 Replies View Related

Auto Select Populated Cells For Chart

Dec 26, 2011

Seeking a way to automatically select just the populated range of cells in Cols A, B and C starting with row 2. In this sample there are 48 however this changes.

ActiveChart.ChartType = xlBarStacked
ActiveChart.SeriesCollection(1).Name = "=Sheet1!$C$1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$C$2:$C$48"
ActiveChart.SeriesCollection(2).Name = "=Sheet1!$D$1"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$D$2:$D$48"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$A$2:$A$48"

View 1 Replies View Related

VBA To Auto Select Last Populated Print Page

Feb 19, 2014

I have a sheet that already has page breaks set at specific intervals to ensure the data is displayed in a certain way on print, I can only have whole pages. The variable is how many pages are printed on each project, which can be anything between 1 and 20 or more. I have a few columns that contain line numbers, and formulae down the entire length of the sheet, so a basic last populated cell search won't work, but the column I'd like to focus on is Col F, which only has blank/empty cells below the last used one in use. Is it possible to have a BeforePrint code find the last populated cell in Col F and simply capture which print page number it is in, and set that as the number of pages to print?

So in summary, I'd like to be able to just hit Print, and the code will automatically set the number of pages to print based on which page contains the last populated cell in Col F.

View 2 Replies View Related

Passing Value To Cell As String From Auto-Populated Combobox

Apr 10, 2013

I have been searching for hours trying to find a solution to no avail. What I need to do is make sure that when I press a button on a form, the value in a combo-box should be populated as "text format" not as a number. The reason for this is that when my numbers start with a 0, the leading zero gets dropped and I can't run the rest of my code as the items don't match...

I have tried substituting the "Value" to "text" in : Me.cboItem.value and have tried for hours trying to name dim variables as a String and pass this into the cboItem...with no luck...


Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Order")
'find first empty row in database
iRow = ws.cells.Find(What:="*", SearchOrder:=xlRows, _


The below populated the combo box cboItem:


Private Sub OptionButton1_Click()
With Me.cboItem
.List = Application.Transpose(Worksheets("Item List").Range("List1"))
End With
End Sub

View 1 Replies View Related

Insert Permanent Date And Time Stamp If And When Another Cell Is Populated

Jan 24, 2011

I want to put a permanent date & time stamp in cell A1 (date) and cell A2 (Time) when cell A3 is populated by any character which would be an inputters initials.

I'm thinking that it will be an "IF" statement but then I am getting confused about how I make it not update when the spreadsheet is opened at a later date and time.

View 6 Replies View Related

Create Auto Populated List Field Based Off Multiple Fields

Apr 21, 2014

I have two or more fields of data, where parts of them are auto populated based on inputs from elsewhere. That part i have working fine, however the problem arises in taking that information and displaying it as a Bill of Materials list on another tab in my excel file. I want to take the information, and only display it in the BOM tab if that piece of information is being used (ie item count field not being 0). I know how to do that part in a rather simple format, however i'd prefer to make it so that when i have the list, and a certain item isn't used then its not included in the BOM, and an empty space is not left behind. I would prefer this to be all be done automatically by a formula if possible instead of just having to resort to sorting the table every time the tool is being used.

Is this even possible in excel, A quick example doc is attached, shows 2 example fields. i'd like to take the information from those 2 fields. and somehow create a single list, with a single heading along the top, with all row containing a 0 "Unit Count" to be omitted entirely from the list, and not have a blank space where the item should be. Keep in mind these fields may be greater than 2, and may not be position one after the other in a strait down line.


View 4 Replies View Related

Automatically Insert New Row For New Info

Apr 4, 2008

The spreadsheet is used to keep track of shares that are purchased / sold

It will make all tax calculations as well as provide a single statement showing all transactions rather than having to sort through dozens of individual statements.

I seem to have most of it working but could use some help with the following.

I would like to be able to have a spot (maybe on a separate worksheet) where you could enter the four items from which everything else is derived, share price, date, number of shares and whether it was a purchase or a sale, and have it automatically:

1. add a new row (complete with formulas and formatting info) to display the new information

2. sort the new data to fit into the right spot in the spreadsheet by date.

View 8 Replies View Related

Search Some Info And Insert Data

Feb 6, 2014

Attached is a sample of what I need completed.

Monthly, I have to do a chart just like this except slightly more complicated.

In the Sample download, there are three charts, "Sample Chart", "Sample Input", and "Desired Result".

"Sample Chart" has a list of accounts from different companies, The first column being their number, the second being their name, and the third being the money they spent.

The "Money Spent" Column is always blank when I start for ALL companies.

I have another chart, "Sample Input", which contains the prices that I'm supposed to put in "Sample Chart, Money Spent" column.

The thing is, "Sample Input" only has the companies with prices listed.. Not all companies have prices, so this means the "Sample Input" is always a shorter list than "Sample Chart".

What I need is the prices from "Sample Input" to be put in the correct position in "Sample Chart". The "Desired Result" chart is what I want it to look like.. exactly like that!

When I do this monthly, I have to scroll through several thousand accounts doing this.

- Possibly have a macro or formula take the Account # in "Sample Input".. Sample it in "Sample Chart", then copy the price and paste it in the right location.
- Possibly make "Sample Input" have blank rows inserted in the places where it should have the account with no prices.

View 5 Replies View Related

Auto Input Info From A List

Feb 10, 2009

I am having difficulty with this formula. In the attached spreadsheet. I want to just input the item number in column A, manually. From there I want it to take the information from the hidden list and fill in the rest of the fields. Example. I input in A4-2664, I want it to search the list for 2664 and take the corrisponding information. It would take the information from M5, O5, and Q5 and place that in C4, E4, G4. I place spaces in the hidden list to make the format the same.

View 2 Replies View Related

Insert The Info From The User Form Into Certain Cells

Sep 4, 2008

I have a very simple userform that has name, address, phone, etc.

I want the command button the insert the info from the user form into certain cells in my spreadsheet then clear the info out of the userform and close it.

View 13 Replies View Related

Auto Pick Up Info From Different Sheets By Conditions

Dec 26, 2013

I'll need to pick up the rates from sheets "SYD" & "BNE" by info from column A of "Summary", which means if the info in column A is "sydney", the formula should pick up the rates from sheet "SYD", if it's "brisbane", the formula should pick up the rates from sheet "BNE".Also, the rate should be matched by the column B,C & D from "summary" page.

View 8 Replies View Related

Auto Populate Cell With Static Date As Another Cell Is Populated

Jan 25, 2008

I have used the following =IF(ISBLANK(Q6),"",(NOW())) & =IF(ISBLANK(Q6),"",(Today())),
but when the excel file is closed and opened the date changes to now or today. How can I make the date stay and not change?

View 4 Replies View Related

When One Cell Is Populated How To Auto-populate A Cell With Date

Jun 27, 2013

I am trying to make a date auto populate when an entry is made into a specific cell.


Cell H has a drop down list of staff names who will be assigned to a project, once a name is selected from the drop down box:

Cell I needs to auto-display that days date

is this possible with just a simple formula?

View 6 Replies View Related

Auto Populate Calendar Days Based On Month / Year And Auto Insert Work Based On Dates / Name

Jul 31, 2013

I am trying to auto generate a calendar based on two drop down menus - Month and Year.

Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.

August PM Schedule Demo.xlsx

View 2 Replies View Related

Auto Insert Value In A Column

Nov 1, 2008

I have an excel sheet where I gather information about companies. There are two columns involved in my problem. Column "D" and column "E". In column "D" I put the country code (example: DE for Germany, GB for England and so on..) In column "E" I put a number (for example: 45786521). What I would like Excel to do is to autoinsert the value of the column "D" before the numbers I have written in column "E". For example if in Row 12 column "D" is GB. And then I write the number 555888777 in column "E", then after the number is written and I hit Enter I would like column "E" to look this way: GB555888777. So the GB (value of the column "D") is inserted at the beginnig of the number I just wrote in column "E". How can that be done?

P.S.:I know that there is a possibility to add the values of these two columns in another column, but as there are far too many columns in the worksheet I would not like to add an extra column just for this.

View 4 Replies View Related

Auto Insert A Date

May 15, 2009

I want a bit of code so that when I insert data into column 'C', it inserts today's date into column 'H' - in the same row. (So when I insert data into C22, the code inserts the date in H22). I want to do it with VBA code rather than an in cell function.

View 2 Replies View Related

Auto Date Insert

Dec 4, 2008

if I add data into C2 then the current date will appear in A2. I also don't want the date to change every time the project is opened.


If I enter $32,500 into C2 - The current date (4-Dec) would appear in A2.

then if I close excel and I open it tomorrow, A2 would still read as 4-Dec, while if I enter $23.52 into C3, the current date (5-Dec) would appear in A3.

- would need to be able to enter multiple entries on one day as well.

View 9 Replies View Related

Auto Insert Invoice Number?

Jan 21, 2007

I've created an Invoice Template in excel. Each time I open it I'd like a certian cell "D3" to increase by one number starting at 2000. Each time this is opened a new customer information is input and then saved to their file. Please give me the very basicis on how to do this. I've already gone into other forums which have provided a code, but I have had any luck getting ti too work.

View 9 Replies View Related

Auto-insert Date In A Cell

Nov 19, 2004

I would like the current date inserted automatically into a blank cell when I click on it. It will always be in the same column, but a different date on each row for that particular entry. I used to use datepicker.xla--loved it, worked great. But now I get an erroe message when I installed and tried to run it on a new pc. Does excel come with a drop-down calendar you can pick a date?

View 9 Replies View Related

Auto Insert And Resize Picture

Jun 17, 2009

I have a sheet where I would like to have a picture inserted in a particular cell range and auto resized into that range. What I'm looking for is, if I type the name of a picture (meathead4uu.jpg) in a cell (Say, B3) then the picture should appear in range D14 to E28.

View 9 Replies View Related

Auto Insert Invoice Number

Jan 21, 2007

I've created an Invoice Template in excel. Each time I open it I'd like a certian cell "D3" to increase by one number starting at 2000. Each time this is opened a new customer information is input and then saved to their file. Please give me the very basicis on how to do this. I've already gone into other forums which have provided a code, but I have had any luck getting ti too work.

View 9 Replies View Related

Auto Insert Non Updating Static Date

Feb 11, 2006

What I am wanting to do is have excel insert a static date automatically. I know ctrl + : inserts date and ctrl shft + ; inserts time. I want excel to do this automatically. The now() and today() functions auto update the date. I dont want the date auto updated when the file is reopened after it is saved. The file is a template so I guess what I am really wanting is a automatic date/time stamp when the user saves the file so when it is emailed and reopened the date does not auto update.

View 6 Replies View Related

Auto Insert Row But Column Q Formula Not Working

May 13, 2014

I have code for auto insert row, when i m getting to new row column Q formula not working

remove the auto generate serial number in column column A, only i want to insert auto with column Q formula to work.

find enclosed a worksheet for your ref.

Please see the below auto insert code;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim O As Range
Set O = Range("O:O")
If Intersect(Target, O) Is Nothing Then Exit Sub
Application.EnableEvents = False
roow = Target.Row
Cells(roow + 1, 1).Value = Cells(roow, 1).Value + 1
Cells(roow + 1, 2).Select
Rows(Target.Row + 2).Insert
Application.EnableEvents = True
End Sub

View 1 Replies View Related

Auto Insert Comment Based On Value Entered

Dec 7, 2009

Is there a way to auto insert a cell Comment when a particular value is entered and then have the Comment copied to a cell in another worksheet in the same workbook?

The value entered into the cells i want this function to work with is from a Validation list. If the cell's value was to change, for instance the user going back to a cell to change the value for whatever reason, I would like the previous comments to remain and then give the user the choice to edit comment or not.

I've had a look in the Forums using search but nothing close seems to be forthcoming.

View 14 Replies View Related

Insert An Auto Number Into Invoice Template

Aug 6, 2006

I have a template of an invoice (in excel) but I need it to automatically
generate a different invoice number every time I open it. Such as 001 the
1st time, 002 the 2nd time etc.

I have no knowledge of macros & do not want to uses Acess.

View 13 Replies View Related

Auto Insert System Date & Time

Nov 17, 2009

I am currently using Excel 2007 and I am looking for an easier way to enter the system date and time into a cell. I am aware of the MS-Excel shortcuts but work in an environment where data capturers still often get it wrong.

Is it possible to use a Excel function/button to auto insert the system date and time by clicking on a cell/button or Alternatively use a calendar to do so.

View 10 Replies View Related

Auto Insert Row Before Monday With Formula And Formatting

Jun 21, 2014

In column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.


21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.

Is there a method with VBA (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.

I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future.

View 9 Replies View Related

Look At Two Sheets And Auto Insert Content From One Sheet Another

Apr 14, 2009

I have two sheets

One is the master and the othere sheet which has the raw data.

On each sheet you have a unique code for the product which is the same on both sheets.

From the raw data sheet i want to the weight for that product to be inserted onto the master sheet.

(the problem is that both sheets are not in order so you cannot just copy and paste the column)

the unique code on the master sheet is G9 and the unique code on sheet 2 is A27 the weight on sheet 2 is r27 and want the corresponding weight for the product to appear on the master sheet in column BI9.

View 9 Replies View Related

Retain Auto Number Sequence After Row Insert

Sep 4, 2006

I'm using a simple auto numbering command.

eg: cell A1: 1
cell A2: =(A1+1)

then shift, copy/paste cell A2 until A600,

This creates a sequential number column from 1-600. My problem lies when a new row is inserted, which is left blank. How can i maintain this auto function upon a new Row insertion?

View 6 Replies View Related

Copyrights 2005-15, All rights reserved