Returning Value From Separate Spreadsheet Tab?
Feb 5, 2014
So under one tab labeled "scores" I have data that looks like this:
12/16/13 ETF-J
12/9/13 ETF-F
12/2/13 ETF-G
and in a separate tab I have data that looks like this:
ETF-J ETF-F ETF-G
1 yr Returns
12/16/13 .87 .67 .45
12/9/13 .05 .35 .04
12/2/13 .67 .23 .24
what I am looking to do is write a formula that would return the number associated with the given ETF and date so the first tab labeled "scores" would look like this:
12/16/13 ETF-J .87
12/9/13 ETF-F .35
12/2/13 ETF-G .24
View 9 Replies
ADVERTISEMENT
Feb 5, 2014
So under one tab labeled "scores" I have data that looks like this:
12/16/13 ETF-J
12/9/13 ETF-F
12/2/13 ETF-G
and in a separate tab I have data that looks like this:
ETF-J ETF-F ETF-G
1 yr Returns
12/16/13 .87 .67 .45
12/9/13 .05 .35 .04
12/2/13 .67 .23 .24
what I am looking to do is write a formula that would return the number associated with the given ETF and date so the first tab labeled "scores" would look like this:
12/16/13 ETF-J .87
12/9/13 ETF-F .35
12/2/13 ETF-G .24
View 1 Replies
View Related
Sep 18, 2013
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob
View 2 Replies
View Related
Jun 8, 2014
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
View 2 Replies
View Related
Jun 12, 2014
I created 14 spreadsheets for each of my call center agents to monitor their metrics. I password protected all then placed the originals on the network. The 14 sheets have 1 cell within them that are linked up to their relevant agent cell in a separate spreadsheet. This spreadsheet is located on the network and automatically updates every hour.
I have monitored the the spreadsheets and updates etc for a while now and everything seemed to be working perfect. So I decided to slowly roll it out by placing a shortcut on 3 agents desktops. I quickly discovered that they were not seeing any updates as I had seen prior to the roll out. I go to the folder within the network and check all spreadsheets and I see all spreadsheets working and updating successfully.
So I thought maybe it was because they were password protected. I removed the protection and the 3 agents saw their spreadsheets update successfully on their computers, the next hour as I had hoped BUT the hour following however they did not see a further update again.
View 8 Replies
View Related
Jan 31, 2014
I usually use INDIRECT to link to a separate spreadsheet. Is there a way i can use this without opening the separate spreadsheet
This is a sample of the formula =VLOOKUP($F$14,INDIRECT('Data Files'!$C$17 & "$A:$Z"),5,0)
View 1 Replies
View Related
Mar 29, 2007
I am trying to take a spreadsheet and have each row print on a seperate page - without having to do the print area thing on each row. Is there a way to automate this process?
View 2 Replies
View Related
Feb 2, 2010
Hi everyone,
I've got several Excel files that need to be edited separately.
Once they have been edited, I need to paste the content of each file in a single separate Excel file.
Is there a way to retrieve the content from all the files in a folder, and merge it in a single file?
For example:
3 separate files > File1, File2, File3
Merged file, sheet1
File1
File2
File3
View 14 Replies
View Related
Jun 16, 2009
I have a very long Excel spreadsheet (>2500 rows by approx 20 columns) that consists of >400 customer statements. At the top of each invoice, in colun B, is the text "Service Requestor Contract Number", and 5 rows down is the respective contract number. Further down (and the number of rows differ for each contract as each has its own unique level of detail) , again in column B is the text "Current Month Charges", and on the same row, in columns K and O, the current month number of items sold and $ charges respectfully. The spreadsheet is NOT paginated for each respective statement.
What I need to do on a separate spreadsheet is sum, by contract, both the number of widgets sold, as well as the amounts due.
View 9 Replies
View Related
Dec 5, 2008
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
View 4 Replies
View Related
Dec 3, 2013
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies
View Related
Nov 20, 2012
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
View 5 Replies
View Related
Oct 9, 2013
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
View 6 Replies
View Related
Feb 13, 2014
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
View 6 Replies
View Related
Feb 25, 2013
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
View 4 Replies
View Related
Nov 21, 2006
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
View 9 Replies
View Related
Apr 23, 2014
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies
View Related
Jan 23, 2012
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies
View Related
Jan 4, 2010
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
View 4 Replies
View Related
Aug 11, 2013
I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
files: twilight sheet squirrel temperature
View 6 Replies
View Related
Jun 26, 2008
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?
View 9 Replies
View Related
Feb 6, 2009
Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:
Sheet A has 10,000 records with these fields: id#, name, address, place of employment.
Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.
Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.
View 3 Replies
View Related
Jan 13, 2010
I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.
I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.
The information I need to transfer from Issues is: .....
View 13 Replies
View Related
Apr 7, 2014
I have 2 sheets in my excel spreadsheet. One tracks data for a number of projects five different employees are working on. The other sheet is where I want to total up the number of minutes each employee has worked on their individual projects. I tried writing an IF statement like below but I am only getting the total in the first field even if the employee's name is not Employee 1....
[Code] .....
How I can write this so their totals show up in the correct row?
View 3 Replies
View Related
Mar 11, 2012
I have a Main Customer Spreadsheet. I want to Auto Populate FROM the Main Customer Spreadsheet to a New Spreadsheet. I want to be able to key in a customer name on the New Spreadsheet and take the info for that customer from the MAIN Spreadsheet and fill in the blanks. I need to be able to do this several times a day.
View 3 Replies
View Related
Apr 14, 2007
It also renames the CommanBarPopop with the new filename.This allows the user to open both Projectworkbooks/files (If required) and load each CommandBarPopup for different filenames .Therefor opening the Userforms and worksheets for the CommandBarPopup clicked ...
View 9 Replies
View Related
Apr 2, 2014
Wondering if there is an easy way to compare 2 spreadsheets that should have identical data on them? The first spreadsheet (Before) has the output data from 'before' a code fix was applied. The second spreadsheet (After) has the output data from 'after' a code fix was applied. The spreadsheets have 7 columns of data and almost 500 rows.
I've already copied the data from the source datasets provided by my IT folks into Notepad (.txt) files and then used Excel to open them as fixed width spreadsheets. I have 1 workbook with 1 spreadsheet with 'before' data. And, I have 1 workbook with 1 spreadsheet 'after' data. And, I have another workbook that contains both worksheets. So, I'm ready to go whenever I get hints of what to do next. :-)
I need to be able to show my client that we did not impact the data with the code fix that was applied. I want to be able to show my client contacts (business folks) an end result via Excel that confirms that I actually compared the 2 sheets and there were no differences. In other words....I can't just show them a formula with '0' as it end result (even tho that's basically what I'm trying to prove).
View 6 Replies
View Related
Apr 24, 2006
I need to write a macro that will import data contained in another spreadsheet, but am unsure how to do this. I have several (about 15) spreadsheets that contain data. I need to import key bits of this data into one central spreadsheet that will be used for reporting purposes. I only need 2 cells worth (values) from each source spreadsheet, to be pasted into the destination spreadsheet, into designated cells.
The source spreadsheets are usually closed down and kept on a file server, which my PC has access to. Ideally I want to activate this macro with a control button - i.e. I press the button once and the macro goes off and collects/updates each field with the latest data stored in each of the source spreadsheets.
View 7 Replies
View Related
Nov 4, 2012
I wish to Automatically copy the TEXT that is written from Spreadsheet 1 cells D5 to F5 to Spreadsheet 2 cells F5 to J5 .... a similar range of cells.
Is there a formula I can use or do I need to venture into the programming side of things.
View 7 Replies
View Related
Oct 29, 2008
I am trying to use the following code to compare the password that was entered, to the one that is stored in the worksheet to make the user aware that the password they have entered is incorrect
View 2 Replies
View Related