Automatically Filling In Data From Another Place
Jul 12, 2006
I have previously used an Excel file - created by someone else - which had pre-entered data. It was set up in such a way that if I typed a person's ID number into a cell, their name popped into the following cell. I know that the names and IDs were pre-entered somewhere else - in another sheet? In a file linked some other way?
What is this called? How do I do it? I want to end up with a file where I can type a department code into one column and have the department NAME pop up in the following column. I am mostly doing this as a time- saving measure when completing long lists of supplies being distributed to different departments (long story.) If anyone can even tell me what this is called, so I can look it up correctly, that would be... sublime.
View 6 Replies
ADVERTISEMENT
Jul 9, 2014
I'm simply inputting zeroes and ones down Column A. Basically, I either put a "0" or a "1" in A1, then A2, then A3, etc. However, I want to create a Macro that allows me to simply type in a string of 1's and 0's and it automatically takes each 1 and 0 and fills it in down the column. This website has the basic idea (Automatically Moving from Cell to Cell when Entering Data (Microsoft Excel)) but I need only one number per cell and for it to move down the column, not across the row. Here's what I have so far:
Sub SAMPLE()
Dim str As String
Dim x As Integer
Dim y As Integer
str = InputBox("Enter string")
y = 0
For x = 1 To Len(str) Step 4
ActiveCell.Offset(y, 0) = "'" & Mid(str, x, 4)
y = y + 1
Next
End Sub
*I can't get it to only break down into one number per cell down Column A. I think the Mid function or something needs to change.
View 2 Replies
View Related
Jun 21, 2009
Here's the outline of my problem...I'm building a directory of hedge fund contacts and need help in cleaning data and automating the entry of fields that will remain the same. I have one workbook with 2 pages... One is for the Companies and the other is for the contacts that work at those companies.
ex. Company #551 is 1794 Management - on this page we have fields for address, floor, city, state, zip, phone, fax and website.
on worksheet 2 is where the contacts are kept... the identifier is #551... on the contacts page all contacts under a specific company number will correspond to that company.
Here's where it gets tricky.... for the contacts the addresses are all formatted differently - first I'd like to sort by management co and address and have excel copy the first listed address format and copy this to the other contacts for that company.... when the formula reaches the next contact with company #552 it will look for the new first address and use this one for all that companies contacts.
after the formula finishes the fomating I'd like the sheet to become intuitive( dont know if this can be done but what I'm looking for is say when I add a new row and enter #551 as a company and enter the new first name, last name, and job title - if the excel sheet noticing that all contacts under that format have the same address, phone, fax, company name etc...
View 9 Replies
View Related
Feb 22, 2007
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies
View Related
May 11, 2009
If I have a dropdown menu in A1 which has the following data:
Chair1
Chair2
Chair3
and so on..
How can I create a funtion to B1 which would work so that if there is the text "Chair2" in cell A1 it would automatically give a value 200€ to B1?
View 2 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
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
Jan 9, 2010
I have the following
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
I just want the date to appear in cell H6 autmatically and the time to appear in H7.
View 9 Replies
View Related
Jul 17, 2006
I have created a gradebook and it has several types of workbooks for the student grades. 30% of the grades are calculated in different workbooks. The main gradebook is where the teacher places the mid term, final term grades. In the same workbook the teacher must place the grades for the following areas. (Participation Grade, Following Rules, Homework Grade, and Monthly Test). Now this is my problem!!! In the main grade workbook I need to automatically place the grades from the other workbooks.
Example: ='Participation English P-6'! ( Cells - AN84 for July, AN160 for September, AN274 for January, and AN350 for MArch. I tried to use = COUNTIF(AN84:AN160,AN274,AN350) but it will not work. I can get one grade at a time by using ='Participation English P-6'!AN84 , which will give me the grade in the main workbook that I need. But I don't want to change everytime the grade period comes around. forumal that will give me grades from the other workbooks at the grade time from these cells?
View 9 Replies
View Related
Feb 12, 2009
Is is possible to automatically place a toggle button on a user form by entering data in a cell?
In otherwords, if I type a number in cell A1, can that number be automatically put on the toggle button and placed on active userform?
View 14 Replies
View Related
Feb 22, 2009
i m creating a formula that will automatically place players in the correct position based on the outcome and there rank.
I have created an example sheet and the results should be in cells E3:E15
View 6 Replies
View Related
Jan 22, 2014
I am looking for a way to take the picture name associated with an image ie: (picture 571) and place that picture name in the cell next to the image.
I can see the picture name - it registers in the top left box that usually indicates the cell the cursor is on... and I could manually type that name next to each image.
However my spreadsheet has several thousand rows- each with several columns of information and an image..
I am looking for a way "formula?" to automatically read the image in cell J4 and populate cell K4 with the image name... and so on.
The reason for my request... is I am importing products into a web based shopping cart... to do so I have to export the products to a csv and import that csv into the shopping cart. The process of exporting to a csv- strips away all the images and leaves just data.
I found that I can save the document as an html and that process creates a folder with all the images- saved as (picture 571.jpg) if the resulting csv had the image name... the shopping cart would then look for that image name in my image file and it would populate into the shopping cart appropriately.
This seemed on it's face like a really simple thing to do, I have plenty experience with excel & I thought I'd be able to make cell k4= j4 and tada it would glean the image name & I could simply copy that formula all the way down the sheet & it would be done... but I just cant find the magic button that freely gives up the image name...
View 5 Replies
View Related
Sep 11, 2013
I have a list of data in a column that has blanks along the way.However, I don't just want to fill it up with the one above, or the one below.
If the data is in column B, I want to fill it such that where the row above has the same criteria in column A, column B for the above row is returned.Otherwise, column B in the row below should be filled in instead.I have set up example data below to illustrate.
Initial Data Set:
A
B
1
Criteria
Number
2
a
1
View 7 Replies
View Related
Apr 8, 2008
I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?
Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long
i = 1
Do While i
View 9 Replies
View Related
May 21, 2014
I am working with monsterous excel sheets (named Data) and need to a column that is governed by the ZIP. I have a sheet named Source in my excel folder with all the matching information next to the excel sheet. The column my zip codes in Data starts on J6 to J290 and the zip codes in Source are from A2 to A2671. The information I am trying to pull from Source to Data are names and branches located in Source from B2:E2671.
What function will auto fill the columns with the correct information. If it can only be done by putting a function into each column, that is not a problem
View 1 Replies
View Related
Aug 6, 2014
As you can see, I have 2 worksheets. I want that all entries in columns B,D,E and F automaticly fill in in multiple tables in sheet2. Also, if possible that macro creates tables (for example: if I have 100 rows, I want to extract 100 separetly values from column B,D,E and F and to enable automatic creation for new table - for 101 entry )
here is the dropbox link of the file:
HTML Code:Â
[URL]
So, I want when I click on the button I want to automaticly import all mentioned values into sheet 2 (I have marked with RED where to put values from which column). In this example, I expect, when I clik on Print all tables button, to print 7 tables in sheet2. In sheet2 I have 2 tables as you can see.
View 2 Replies
View Related
Jan 23, 2014
I have a peptide sequence that is 4500 amino acids long. I have the numbers 1 - 4500 in column A and the corresponding letter code for the amino acid in that position in column B. I have done a bunch of other calculations and isolated small series of amino acids within the entire sequence (example: positions 25-42, 153-166, 381-297, etc).
I would like to fill column C with either a blank space or, if it is in one of the selected series, the letter code again. So that the first 24 rows of column C would be blank but rows 25-42 would mirror the letter shown in B. Then blank spaces again until we reach row 153, etc.
I've used a vlookup to paste the letters with the chosen parts of the sequence, but now I want to space them out along the entire 4500 so that I can see if there are overlapping areas between several sets of criteria for choosing series (these would go in columns D, E, etc).
View 3 Replies
View Related
Nov 21, 2006
I'm attempting to make a simple userform that inputs data onto an existing worksheet. I have the userform but would like to use a combo box to choose a "category" item of data, however I dont know what the categories are! I would like the combo box to, somehow, look at the spreadsheet and read off the already entered categories and offer those as choices.
A picture is worth a thousand words:
http://www.copestake.org/images/excel.png
Is there a simple way to fill the combo box (using the form initialize I assume) with the existing categories?
[Edited to link to image instead of displaying on board~admin]
View 9 Replies
View Related
Apr 26, 2006
My worksheet looks like this: column A - ID, B - Group #, C - data. When data has been entered for all the IDs in a group I would like a message box to appear stating end of group #.
View 4 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 19, 2006
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View 8 Replies
View Related
Dec 22, 2008
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
View 3 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
Sep 11, 2009
I'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
- Etc.;
- Etc.;
View 14 Replies
View Related
Nov 20, 2012
I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2
On the 2nd tab I have the shift schedule, which tell which shift is working on particular days
Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am
Feb
1
Shift A
Shift C
[Code] ........
Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.
I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time
View 2 Replies
View Related
Mar 31, 2014
I've set myself a project to try and automate some repetitive filling in of word documents and would like a point in the right direction. I've done some research on the MSDN and some sites on the web. The way I was thinking of doing it would be,
make a template with either Fields or Bookmarks (which would be best?) for each piece of data on the paper work.Put the repeated data into an excel sheet (with a button for the macro to be assigned to).
write some VB script that will, define and "label" the data in the XL sheetopen the word templateenter the data into each Field/BookmarkSave as a new file in a "New" folder (name of file and folder taken from the Data)open the next template and repeat.
would this be the best way of approaching this problem?Whats the best way of defining points in a word template, Fields, Bookmarks?what objects would I need to use to rename the folder?
View 5 Replies
View Related
Dec 17, 2008
The below formula is in Col F6, how do i get this formula to adjust to data range without manually filling down, my data changes everyday therefore i need the formula to auto fill according to data range.
=IF(ISBLANK(E6),"",Summary!$I$2-E6)
View 9 Replies
View Related
Feb 26, 2010
I have a worksheet that contains data on products including prices and I want to use that worksheet to fill out an invoice for every sale of those products. I know how to make the list/table dynamic and varify the data to "list", then use VLOOKUP to get the price to appear, but what I'd like to know is how to overcome the problem that will arise when a product's price has to change. Eg. if on 1/1/10 the price of product A rises from £15 to £20, how do I ensure that 2009 invoices do not change when I update/edit the price, but that the current price goes up? So far, the only thing I can think of doing is to create another product, but there are several other bits of data about each product on the worksheet which will not change (weight, height, width, supplier etc), so I don't really want to have to duplicate the unchanging bits and also leaving the productA with the old price in the list may lead to accidentally selecting the old price. What do you think? Is there a way of maybe fixing the price to date or something?
View 9 Replies
View Related
Oct 11, 2006
I can't seem to find the answer to this question. I have a sorted list of data 3 cells wide, 120 cells long. I wish to have this data put into a scroll bar so I may scroll the data, select, and it will output to a chosen cell.
I assume the ScrollBar control is the choice but since I have no experience with any controls, I am at a loss. I have thus far only managed to insert the scrollbar, click on the properties and then I'm ?.
1. How do I specify the range of cells to display?
2. How do I specify an output cell after selecting from the list?
I've attached a spreadsheet to view the data.
View 5 Replies
View Related
