Lookup Data In One Workbook And Inserting Into Another
Mar 29, 2012
Macro (preferably) to look up some data in a workbook and paste it into another.
I have an active workbook with this format
Sheet1 Â ABCDEF1RECORDER IDDate HOUR IN KW Â Â Â
21000560805917/11/201100:15:00150Â 31000560805917/11/
201100:30:00150Â 41000560805917/11/201100:45:00150Â 51000560805917/11/
201101:00:00150Â 61000560805917/11/201101:15:00150Â 71000560805917/11/
201101:30:00150Â 81000560805917/11/201101:45:00150Â 91000560805917/11/201102:00:00150
I have another workbook opened called "Carbon intensity" with this format
CO2_Intensity_01082011_29032012 Â AB1Timeg CO201/08/2011 00:15466301/08/2011 00:30461401/08/2011
00:45460501/08/2011 01:00462601/08/2011 01:15463701/08/2011 01:30463801/08/2011 01:45466901/08/2011
02:004671001/08/2011 02:15472
I would like to insert the values in Column B in the "Carbon Intensity" workbook into Column F in the active workbook corresponding to the dates and times.
This is an example of what it would look like afterwards
Sheet1 Â ABCDEF1RECORDER IDDate HOUR IN KW Â Â Â
21000560805917/11/201100:15:0015046631000560805917/11/201100:30:0015046141000560805917/11/
201100:45:0015046051000560805917/11/201101:00:0015046261000560805917/11/201101:15:0015046371000560805917/11/
201101:30:0015046381000560805917/11/201101:45:0015046691000560805917/11/
201102:00:00150467101000560805917/11/201102:15:00150472
View 4 Replies
ADVERTISEMENT
Sep 8, 2006
I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
View 3 Replies
View Related
Sep 13, 2006
I have two excel workbooks, i.e target.xls and source.xls. Assume
target.xls looks like this
letter number sound
a 1 a
b 2 be
c 3 cee
d 4 dee
e 5 ee
f 6 eff
g 7 gee
source.xls looks like this
letter number
a I
c III
d IV
g VII
In the target file, I want to be able to change the numbers of the letters found in the source file, so that the new target file will look like this.
new target.xls
letter number sound
a I a
b 2 be
c III cee
d IV dee
e 5 ee
f 6 eff
g VII gee
I believe there must be a simple way to do this using VBA.
View 9 Replies
View Related
Mar 25, 2014
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
View 5 Replies
View Related
Jun 29, 2012
How can I insert a pivot table in excel 2007 into a new workbook so that it always references the original data file?
View 1 Replies
View Related
Oct 29, 2009
Can someone help me i have a few columns of information i need to move over to a another sheet, but the problem is the information i need to move is the end result of a formula and when i move it the answers are gone how do i fix this,,,,, and both sheets are on the same work book as well
is there a vlookup formula to use,,,im not experienced in Vlookup up formulas
View 9 Replies
View Related
Mar 26, 2013
I am trying to add the sheet name to the center footer for each worksheet in a workbook. The workbook has about 80 sheets and it is cumbersome to do this manually. I am using Excel 2010.
I have tried to record a macro capturing what I do manually, but when I run the macro on another sheet, it does not add the sheet name to the center footer.
I have tried searching for a macro online and the ones I have found just crash excel.
I know I am probably missing something obvious in my macro code.
View 4 Replies
View Related
Feb 14, 2014
I have a workbook that requires refreshed source data each day. The workbook has all of the macros and formulas that analyze the data. I have the following code to import the worksheet with the raw data (onto a fresh worksheet in the calculation workbook), but I would like to create code that also adds the date and time to the imported data worksheet -- not the date/time the raw data was created; instead, when it was imported into my calculation workbook.
Below is my code for importing the raw data worksheet:
[Code] .....
View 2 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
Jan 31, 2010
I would like to create a macro which I can use over and over again, with slight modifications.
I have included a sample worksheet with two (2) sheets. Sheet 1 is considered my 'master sheet' of which I want to add data to, if it exists. Sheet 2 is some additional data that may or may not be included. The common thread, between the two sheets is column A.
I would like to either add to sheet 1 or create a sheet 3 with the data, whatever is more effective.
In this 'sample' case, I would like to move the data on Sheet 2, column 'H' to Sheet 1, provided both column "A's" match. Again, if it makes more sense to combine the two on Sheet 3, no worries. However, the next time I do it the data may be in a different column so I hope to understand how to change the 'From' and 'To' columns. My rudimentary skills want to say If it exists, place it here. If not, move to the next entry.
The end result would be all the data on Sheet 1 (as it stands), joined with the data on Sheet 2 Column H, if Sheet 1 Col A = Sheet 2 Col A.
This would be a process that I would do over and over again as I populate Sheet 1 with the data I need.
View 6 Replies
View Related
Dec 21, 2011
i have a spread sheet that has a number of different addresses in them I need a vb code that will insert 5 rows between each row of data starting from row 42 and has the possiblity of running to the end of the rows 65536. i believe the best way to do it is via column B which will always have data in it. i have tried a number of methods but they all only insert the rows once or they do it the required ammount of times one after the other so i end up with a whole section of blank rows and all my data still together.
View 4 Replies
View Related
Sep 25, 2012
what i want to do is have an area where i can input data and then i have three options of inputing the data or resetting data that is already in the list of data so i start to create a list of data but i can reset/change values if i wish for example quantity. i would also like to have a way to subtract or add a number from the quantity assigned to a code/name of some form. i know that's quite a bit
View 3 Replies
View Related
Jul 28, 2008
I am trying to create an automated input of numbers which will only happen if text is present in a cell on the same line:
A
1Collumn 1 Column2
21 MR X
3(cell with formula: If text is present in column 2 insert (CellA2+1). Result is 3 being inserted in A3.
I have tried to combine a ISTEXT function with an IF function but to no avail.
View 9 Replies
View Related
Jul 10, 2014
I have two worksheets.
1 worksheet I have a value and I need next to it the result i take from other worksheet.
LETTERS
AMOUNT
A
Result
C
Result
G
Result
[Code]...
The list goes on.
So Pretty much I need to compare the value "LETTERS" on the first worksheet with the array of letters from second worksheet and insert in the field of the Result, the value next to the correct find in the array of letters ...
View 9 Replies
View Related
Sep 29, 2009
I'm using excel 2003 and was wondering if I could insert html around the cell data in a faster way than cutting and pasting it. Here is an example of a column and what I would like to do.
aaaa
bbbb
cccc
dddd
Now I would like every cell to have HTML around it to be easily posted on a website. I would like the cells to now look like this.
<a href="www.url.com/aaaa"><img src="www.url.com/aaaa.jpg"></a>
<a href="www.url.com/bbbb"><img src="www.url.com/bbbb.jpg"></a>
<a href="www.url.com/cccc"><img src="www.url.com/cccc.jpg"></a>
<a href="www.url.com/dddd"><img src="www.url.com/dddd.jpg"></a>
I haven't dealt much with functions so I don't even know if this is possible.
View 3 Replies
View Related
Jul 15, 2014
The code below pulls information based on what i specify in a userform from another sheet.
I am trying to total the last 3 columns but for some reason the code sticks the sum formula right in middle of all my information. However, when i run the code (the exact same way) again then the code puts the sum underneath the last row as indicated in the code. How can i get the code to run right the first time around?
View 8 Replies
View Related
Mar 3, 2014
I have a column with lots of rows. I want to locate a specific one and insert some data into this row at specific columns. This is what i have come up with so far:
[Code] .....
View 14 Replies
View Related
Apr 17, 2009
I need a bit of VBA code which will run automatically when a csv file is opened. The code needs to move anything after column F onto the next row and repeat until there is no more data. The data is generated by an external program but is put on the same row (not sure why it does this). I have included an example of the csv file (the data in it is garbage so ignore it) for you to look at. the header row which needs inserting needs to be:
A1=Status
B1=Job No.
C1=Date
D1=Name
E1=Room
F1=Description
Hope this is enough information for you. bear in mind the amount of data being generated could be quite large. it has to be run automatically in order to be used in seamless mail merge.
View 2 Replies
View Related
Jan 15, 2007
What formula would I use to populate a cell?
Example
e13 = 0 until after the 10th of the month on the 11th it = $100.00
If I13 has an amount in it on or before the 10th e13 remains $0.
Each of the 12 line of this rent roll needs to correspond to its own month.
View 10 Replies
View Related
Mar 27, 2012
I've written the following code to insert rows at certain points depending on certain conditions, looping through to the last row. However, as the last row number keeps moving, due to inserting rows, I am not sure how to alter the code to allow for this.
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
[Code]......
View 5 Replies
View Related
Feb 7, 2007
I'd like to insert the value of a cell in Excel worksheet A into the cell of another Excel workbook B. The twist is that the Worksheet A begins as a templated file and is copied into new jobs each time. The destination spreadsheet B location and name stays the same but the source file changes location. So the destination file B can't be looking to be updated. What needs to happen is that once the source spreadsheet A is copied into a new job, whenever the spreadsheet is updated and closed, it needs to "export" the new value into the source file B. Not the other way around as the destination file will not know the location of this new source file. Sorry for the confusing syntax but hope this is clear enough for an answer. Here's an example of the question: How do I automatically, on closing this source workbook A, update cell D24 into file C:MyFilesExcelFormerFile.xls Sheet1 cell B27?
View 2 Replies
View Related
Nov 23, 2007
I m trying to sort this out myself before posting but its driving me crazy! I have the following userform :-
I would like all that data entered into a worksheet called "Purchase Record" into the following cells:-
View 4 Replies
View Related
Feb 23, 2014
I need a formula to look up the value in another workbook. I have a number of workbooks that have a list o competitor's names and their scores The competitor names are not in the same order in each workbook. I have created a master workbook to extract the score from each workbook. I need the formula to match the name in another workbook and then pick up the score cells to the right.
View 4 Replies
View Related
Feb 24, 2009
I have a workbook called Book1 that was created from another macro. I am attempting to do a lookup using columns B2 and C2 and look for the same values in columns G6 and J6 from a workbook called Marine Moves..If a match is found copy the value from Column H insert that value into Book1 Column A, if possible I would like the Marine Moves workbook to remain closed during this process.
View 9 Replies
View Related
Oct 21, 2007
i have 3 files :
1-april 2007
2-may 2007
3- total
i want a macro that that 1+2 and with "vlookup" out the cells in "total". i start to record the macro but ... it's not work at all. i want to put an input box that ask the user "what month and year" and then put the relevant file in the vlookup.
View 2 Replies
View Related
Feb 7, 2014
Insert a drop down list that is attached to a bunch of data to make it easier to navigate through the data.
Therefore, when you chose one item in the list it will populate data in the worksheet for that item in the list and change for each item in the list.
For example, if you have ten people's names in the list when you pick Nancy it will show you information on her and when you pick Scott it will populate with completely different data. The data all coming from one master tab.
View 8 Replies
View Related
Dec 27, 2009
I have a userform with 4 text boxes. One of the boxes you have to enter in an amount. i.e. £25.52 This value gets put into cell E17 in my spreadsheet. When I click the button and it puts it in, it puts it in the cell as "£25.52" with left alignment and does not apply it as the accounting style, as set to that cell. The same happens if I just type "25.52" instead.
I also have a formula applied so that if E18 is "Yes", E19 will display "£0.00" otherwise, E19 should display the value of E17. And then column E has a total at the bottom of the values in Column E. Even when "£25.52" or "25.52" is entered in, it comes up in cell E19 as 25.52 (not with accounting style) but it doesn't add up in the sum at the bottom of the column.
View 2 Replies
View Related
Sep 3, 2013
I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins
name Acc # Balance Date
John 2222 200 june 2013
John 2222 344 june 2013
John 2222 700 Sept 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
View 2 Replies
View Related
