Cells Cross Referancing Data From Another Sheet To Fill Themselves In?
Apr 15, 2009
I would like cells to fill themselves in automatically on one sheet, by getting the figure from the cell to the right of them, and seeing where the same figure lies on a different sheet. which ever row this figure lies in will be used to grab a different figure from colum "C" of the same row. This is the figure i would like to be put back on the first sheet.
Im aware that i havent explained this very well but i hope that my attached spread sheet with a couple of notes and highlighted bit will explain it much better.
Thank you very much in advance to anyone who can help me with this!
Jake
View 14 Replies
ADVERTISEMENT
Mar 24, 2009
I have two worksheets. One worksheet is pulling in data from an OLAP cude. I have it configured as a pivot table. The other worksheet contains my dataset. Both worksheets are in the same file.
In my datasheet worksheet I would like to pull in data from the other worksheet. I would like to be able start a few cells and then drag so that it autopopulates the values. Here is the formula I'm using
='Affiliates Pivot'!$F10
='Affiliates Pivot'!$F11
='Affiliates Pivot'!$F12
it poulated with the values
23
45
64
then I select the 3 cells and drag so that I can autopulate the remaining values. It enters the formulas
='Affiliates Pivot'!$F10
='Affiliates Pivot'!$F11
='Affiliates Pivot'!$F12
='Affiliates Pivot'!$F10
='Affiliates Pivot'!$F11
='Affiliates Pivot'!$F12
='Affiliates Pivot'!$F10
='Affiliates Pivot'!$F11
='Affiliates Pivot'!$F12
what I really want is
='Affiliates Pivot'!$F10
='Affiliates Pivot'!$F11
='Affiliates Pivot'!$F12
='Affiliates Pivot'!$F13
='Affiliates Pivot'!$F14
='Affiliates Pivot'!$F15
='Affiliates Pivot'!$F16
='Affiliates Pivot'!$F17
='Affiliates Pivot'!$F18
Is this possible? How can I do this?
View 12 Replies
View Related
May 9, 2014
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
View 2 Replies
View Related
Jul 14, 2009
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
View 14 Replies
View Related
Dec 21, 2013
i want automatically filled data in customers sheet when i enter his name only (in other sheet)
i am using this formula but this gives me empty cell in between when name comes after a few cells
=IF('all people'!A3:A6='all people'!$K$1,'all people'!B3:B6,"")
some one gave me example workbook but that formula is working in the same sheet
and i need it to work in another sheet and i could not do that
download sample work book to understand this formula
View 8 Replies
View Related
Jan 10, 2009
I need to populate a range of cells with names from a list on another sheet based on the contents of another cell.
In the attached example;
on sheet 2 cell E3 has a number in it that corresponds to a number in column D on sheet 1, I want to use that as the reference to fill in cell E4 on sheet 2 with the value in column A on sheet 1, then I need to populate the YELLOW cells on sheet 2 with the names from sheet 1 column C that also match the values in sheet 2 cell E3.
The attached file has an example. I have been trying to use LOOKUP and VLOOKUP but can't get it to work.
Can someone please take a look at this for me?
View 6 Replies
View Related
Jan 6, 2010
I have 2 sheets that are used for an inventory that tracks hours on a tool that we use where I work. One sheet allows the user to add items to the inventory along with the current hours on the items. On another sheet, I have several dropdown boxes based on the category of items that are autopopulated with the item's serial numbers through data validation. So far, I'm only able to have the serial numbers fill the dropdown boxes, but I also need to have the hours associated with the individual serial numbers transfer over to this sheet as well in the cell adjacent to the dropdown list.
I assume the pseudocode for this would be for each cell in the dropdown sheet, if it matches a cell in the inventory sheet, grab the cell's value in the hours column and paste it into the adjacent cell on the dropdown sheet, but I'm not sure how to do the actual VBA for it.
I've included my sanitized workbook so you get a better idea of what I need.
View 8 Replies
View Related
Oct 19, 2012
I am trying to compare two cells and if they match then I want to fill in a list on sheet one from sheet two.
I have the absolute working when I compare one cell to one cell, but I can't get it to compare one cell to a range of cells (two weeks of days)
Here is the absolute one I have working:
=IF(C1='Data Sorted'!A1,'Data Sorted'!A2,"")
Here is what I tried that doesn't work:
=IF(C1='Data Sorted'!A1:N1,'Data Sorted'!A2,"")
View 6 Replies
View Related
Aug 18, 2014
I need to create a formula that searches the first table and autofills the second based on the teacher name. So when Mr. M is listed in column e, all the data to the right automatically appears on the second sheet.
View 3 Replies
View Related
Aug 20, 2013
I have a macro that generates multiple sheets in a work book then formats the data on each sheet the same, I would like to highlight the same two cells in each sheet with a background color of yellow. My format code is below, I can seem to get fill to work with the sh. property
For Each sh In Worksheets
sh.Range("O1").Value = "Total Amount"
sh.Range("P1").FormulaR1C1 = "=SUM(RC[-2]:R[99]C[-2])"
[Code]...
View 2 Replies
View Related
Feb 22, 2010
Using a combobox with dropdown choice you can select today or yesterday. After this i want to fil in cell A1 the date (now) if choose today, and date (-1) if choosen yesterday. I attach a test sheet with this post.
View 2 Replies
View Related
Jul 29, 2008
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
View 9 Replies
View Related
Jan 16, 2014
Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?
If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.
View 4 Replies
View Related
Apr 25, 2007
I have a sheet where a user will enter x number of rows of data.
im trying to get it so that the macro, when run, will goto the last row of Data in Colum A, and delete all rows BELOW to the end.
then select the first empty row and do the following
in A =SUM(A30:A last row of data above this one)
in B ='HASH TOTAL'
in D ='TOTAL
in E = same as A but for colum E
in F = same as A but for Colum F
in K =same as A but for Colum k
in J =same as A but for Colum J
then select the row below that one and from cells A:M
and format
With Selection.Interior
.Pattern = xlCrissCross
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With
View 9 Replies
View Related
Jul 11, 2013
Im trying to cross match two cells from two combobox's,
Allong the top there are test names, and down the side is staff names,
On a user form there are two combobox's one with staff names the other test names
Untitled.jpg
How can i make it increase the number in a cell where the name and test match the combobox's value
I have Attached an example file,
View 2 Replies
View Related
Jul 7, 2009
I'm trying to achieve this. I've been trying vlookup, index and match but can't seem to get anywhere.
I don't mind using a macro if it's easier, but I wouldn't know where to start with that.
View 9 Replies
View Related
Jul 22, 2009
I am trying to use WINGDING FOnts on some forms. I am not having full success with them. Sometimes they seem to work, other times not at all, and then sometimes if the code is less that 127. I am programically creating the forms from a worksheet.
The code used to create is as follows:
Sub MakeUserForm()
Dim TempForm As Object
Dim NewButton As MSForms.commandbutton
Dim NewLabel As MSForms.Label
Dim NewTextBox As MSForms.TextBox
Dim NewOptionButton As MSForms.OptionButton
Dim NewCheckBox As MSForms.CheckBox
Dim x As Integer
Dim Line As Integer
Dim MyScript(4) As String
Dim BC As String
'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
'Create the User Form
With TempForm..............
View 6 Replies
View Related
Jun 1, 2013
I have some quality information that is stored in a single .txt format. All the information for several different parts are stored in the same file "merge__chr.txt I then have a single excel file for each different part. I need to be able to use a macro to extract the data from the txt file and fill in the excel file based on 3 criteria.
Part #
Serial #
Characteristic #
The part # is always in cell "B4" of the excel file, and the first column of the .txt file The Serial # is variable, but will always be in column B (starting at row 11 and down) of the excel file, and the second column of the .txt file The Characteristic # will always be in row #7 (starting at column D and right) of the excel file, and is in the 3rd column of the .txt file.
I need this to copy the information from the 6th column of the .txt file and store it in the cell where the characteristic and serial # meet. I only need to be able to do this for 1 serial # at a time. When the current serial # is selected, I would like to use a button to fill in all the information for all Characteristics of that part. I attached a sample set of data with "QA-PRINT" as the Part #.
View 6 Replies
View Related
Jul 28, 2009
I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/times into individual employee timesheets. The timesheet layout has already been provided to me and cannot change.
I’m really struggling here and would appreciate any help.
Please reference the attached workbook for the issues described below:
1) Sheet "1_attlog" is the log that is imported from the electronic time clock.
2) Sheet "Sorting & Filtering" is what I have developed so far that manipulates the data from sheet "1_attlog" into something that can be easily understood, it also matches employee #'s to a name, and allows for date ranges to be set that copies over to the individual timesheets. Please do not change any formulas on this page if possible.
3) Sheets "#2 Dan through #10 Blank" are all individual employee timesheets that I need to import the data from "Sorting & Filtering" and populate the "Time In" (column D) and "Time Out" (column G) based on matching the data from "Sorting & Filtering" sheet, "Time Clcok ID" (column D) "Date" (column E), "Time In" (column F), "Time Out" (column G), "Emp Name" (column I).
The dates on the individual timesheets have been linked to the "Date Range" (cels F2 & F3) on the "Sorting & Filtering" sheet, please keep this link intact.
4) The individual timesheets already have formulas inplace to calculate the hours worked after the "Time In" and "Time Out" data has been imported.
View 2 Replies
View Related
Oct 26, 2011
How to fill a web form automatically with excel data. I need to sign up like 300 people on a site per day. Their detail are given to me in excel. How can I automatically fill the form with the excel data without spending time doing copy and paste for each person's data?
View 1 Replies
View Related
Aug 4, 2009
I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.
View 3 Replies
View Related
Dec 14, 2007
I have a macro that creates mutiple, though varing numbers of, columns. Once the columns are created, a blank column is set up to the right of them. When the data is ideal, only one cell in each row created by the populated columns will have data in it. So lets say that in this example, the macro created 23 columns of data. A1 is blank, B1 is Widget, and C1 through the end are also blank. I need a macro that will look at the rows and find the populated column and copy that string of data to the blank 24th column (to the right) created after the poulated columns. So X1 (the 24th column) will have Widget placed in it.
Now I need it to do one other thing. In another example, let say there are 5 columns created this time. In the third row, there is data in A3 and D3. If they are the equal to each other (A3 value being 143256 and D3 value being 143256), then I need the 6th column created to have 143256 placed in it. However, if the data differs ( A3 value being 143255 and D3 value being 143288) then I need the word "Scrap" to appear in the sixth column.
View 3 Replies
View Related
Mar 5, 2013
I have a list of data as follows:
Employee Location
John Florida
John New York
Jill Maine
Jack Maryland
I would like to determine if an employee works across locations. My complete list has 550 names in it, this is just a subset of the data I am looking at. Above, John works in 2 locations, however Jill & Jack work at a single location. I am looking to differentiate cross locational versus single location employees.
View 4 Replies
View Related
Feb 19, 2014
I'm trying every conceivable angle I can think of in vba and coming up empty. What I have is a spreadsheet similar to this:
Column A Column B
1. 1234567
2. 2345678
3. 3456789
4. 4567890 A0001
5.
6. 9876543
7. 9876543
8. 9876543
9. 1234578
10. 3456789 A0002
What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.
View 5 Replies
View Related
Dec 10, 2013
I have a small challenge, where I am trying to fill blank cells with the data from the last populated cell above.
However, I need it to be able to automatically recognise when a manual entry has beed actioned down the column and recalculate for below that cell with the new data.
The data in the cell is selected from a drop down. I want it to populate all the cells below, until the next drop down is selected. And automatically repopulate them all when it is selected.
E.g. Cell C8 has the value "DATA" selected, and everything below is autofilled with "DATA", but when I select cell C12 with different drop down list value "VALUE", all the cells below stay as "DATA" because they are obviously no longer blank.
What I need if C9 - C11 to remain filled with 'DATA" and from C13 to auto fill with the newly selected value "VALUE".
Need this to happen for as many rows and data selections from the drop down as needed (endless).
I have used:
Code:
Sub FillEmpty() Application.ScreenUpdating = False Application.Calculation = xlManual Dim cell As Range For Each cell In Intersect(Selection, _ ActiveSheet.UsedRange) If Trim(cell) = "" And cell.Row > 1 Then cell.NumberFormat = cell.Offset(-1, 0).NumberFormat cell.Value = cell.Offset(-1, 0).Value End If Next cell Application.Calculation = xlAutomatic 'xlCalculationAutomatic Application.ScreenUpdating = False End Sub
which does the auto fill, but won't change the cells below once one of them has been updated.
Info:
Drop down list has 31 possible selections at the moment
Starts at Cell C8
Needs to be flexible enough to add an indeterminate number of rows, and additional items to the drop down selection.
View 1 Replies
View Related
Nov 7, 2008
how to enter data in non-adjacent cells using a fill command.
Here is what I am trying to do:
in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.
I have tried to figure out a way to do this other than manually, but am confounded.
View 9 Replies
View Related
Mar 29, 2014
creating a macro I have two sheets named customer list (I have only put in 30 rows as an example but some sheets have 400 rows)
in the sheet named list column "F" are the names it should be cross checked with column "F" in the sheet named customer if it is there entire row to be deleted
View 3 Replies
View Related
Jun 14, 2013
I have a formula in cell A1 that I need to drag downwards so that it fills cells A2 to A10. That formula is picking up the figures located in another sheet but the figures on that sheet are arranged horizontally (A1 to J1). Is there a formula to perform this action? I've tried playing around with the $ sign in my formulas but it does not work.
View 2 Replies
View Related
Dec 26, 2012
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies
View Related
May 21, 2009
I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.
View 2 Replies
View Related
