Formula For Obtaining Values On Separate Sheet From Values Declared On Current Sheet?
i have a workbook with two sheets. lets just call them sheet 1 and sheet 2. on sheet 1 i have data for employees and their current wages and other info. on sheet 2 the data is for compensation scale on three separate columns.......
in order for me to automatically get the data from (sheet 2 B3) the formula for sheet 1 E1 would be: ='Sheet 1'!B3. how do i formulate the equation so that i can do ='Sheet 2'!(C1)(D1)?. in other words i want to specify the column and row from the values declared in sheet 1 column c and column d respectively.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Search Contents Of Cells In Row From List Of Text Values On A Separate Sheet
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this: I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
View Replies!
View Related
Formula To Capture Values From One Sheet To Another
I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10. I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO). I tried using a SUMPRODUCT but my formula didn't work. Is there another formula that will give me this calculation?
View Replies!
View Related
Retrieving Values Based On Matching Values On Separate Worksheets
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same. Example: On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2. I have attached a sample spreadsheet for clarification.
View Replies!
View Related
Vlookup Formula Not Working With Data On Separate Sheet
I have a sheet set up with names. I have 2 other sheets with spouses and children that belong to the first sheet. I want to insert a formula that will look up the correct spouse (based on a member number) and put in it the column. Same with the children. I tried the vlookup function, but it did not return the correct result.
View Replies!
View Related
Pulling Information In Seperate Sheet Based On Data In Current Sheet
I've got a bit of a quandary here that my novice programming skills can't seem to solve. Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc). so a typical row would look as follows: Filename | Keywords | Photographer | Metadata | Location | all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row. Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on. so for example, in my second sheet lets say I have the following A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.
View Replies!
View Related
Values On Sheet 2 To Populate On Sheet 1
I would like the values on sheet 2 to populate on sheet 1, my guess would be a hlookup. I would like to use the acct# as the lookup value and then return the appropriate values .. Example for acct #1002217, I want to lookup on sheet 2 the value associated with LI03 (cell D6) to come back in sheet 1 and insert in the appropriate cell (cell F4) See this link ... http://spreadsheets.google.com/pub?k...vdd_zO8g&gid=0
View Replies!
View Related
Compute Values Sheet To Sheet.....
how do you compute values from sheet to sheet. in other words, let's say you have a master sheet with all end values on it. how do you write a function that copies the value on another sheet. for example: a function located in a cell on Sheet 1 that always is the same value as a cell in Sheet 2, that sums up two columns on Sheet 2.
View Replies!
View Related
Look Up Value From Master Sheet Based On Criteria In Current Sheet
I'm trying to create a function in a template invioce that will look up a value in another spread sheet (fees).The data will be based off three values entered in the invioce from drop down boxes. I've attached the workbook with the master sheet and the invioce (sheet 5) The three criteria that can be selected is the project, month and name and I want the amount of hours to be returned based on that information in column e. The projects are seperated and billed by month.
View Replies!
View Related
Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3. ID 1ID 2 12342122 45671112 89101718 11122678 13144544 15162324 17189987 19201215 21221928 1976 2576 2345 4678 In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
View Replies!
View Related
Name Sheet With Two Cell Values
Working in one workbook, I am copying a sheet (sheet 1 = IR Master) to the back of the workbook and I need to rename the copied sheet / tab with values from two cells (L1 & M1) on the copied sheet and save the workbook. The sheet / tab names will change each time another sheet is saved to the back. The names will look like this 0566-100, 0566-101, 0566-102, etc. with L1 = 0566 and M1 being the sequential # that changes, making each sheet copy unique. The other copying and etc. are working fine but I cannot crack the dynamic sheet / tab renaming and save at the end.
View Replies!
View Related
Vlookup On 2 Values In Another Sheet
I've done some research and came up with this formula for Vlookup (see attached excel spreadsheet). What I'm trying to accomplish is to look up the values under 'Pipe Size' & 'Pipe Material' to give me a Heatloss number. The formula works with the Sched 40 Steel & Type K copper, but with the other 2 materials, I get #N/A. Can anyone tell me why thats happening?
View Replies!
View Related
Loading Values From Another Sheet
I've created a sheet to estimate a job project and now I am working on a new sheet to keep track of our estimated totals compared with actual expenses. What I need is to make a macro that allows you to browse the PC and let the user select the excel sheet where all the Estimate Values are stored. These stored values are pulled up and posted onto the new workbook. Please pull A1 from the estimate book and put it in B4 of the new workbook
View Replies!
View Related
Choosing Values From One Sheet
I have attached a sample of what I am trying to do. What I want to do is to be able to go through each day sheet and choose the name and times from the list box's I've created and have it show up on the summary page to match. So if I'm scheduled to work on Wednesday at 9am... I want it to show up on the summary page under Wednesday at 9am that I am working, but If I am not working I want it to be just an X to show up.
View Replies!
View Related
Matching Values And Copying To A New Sheet
I have been searching through the forums for examples and haven't been able to find exactly what I am looking for. Ok here is my issue. I need to compare the Conf # and line number on both sheet 1 and 2. So basically if the Conf # and the Line number match between page 1 and 2 then it copies the Conf #, Item Number(Sheet 1), and Detail(Sheet 2) to sheet 3 in a row. This needs to continue till the end of the Sheet 1. I have a pseudo report created with no code because I don't have a clue as to where to start.
View Replies!
View Related
How To Copy Certain Values For A Cell To Another Sheet
I would like to be able to copy the values of certain cells to another worksheet if a certain condition is met. In return - I would also like to delete the information that was copy to the new worksheet if the condition has changed. I realize I can just filter out the data to get the information that I need but, I am creating this spreadsheet for several sales people to use and some are not to diverse on excel.
View Replies!
View Related
Searching Another Sheet To Plug In Values
I am trying to create a sheet for invoicing. I have a sheet I want to use as a database. I want to search the database from the "invoice" sheet and fill the fields once i have found what I am looking for. attached is a sample database I will use. I dont want to use the whole row, only selected colums. for example short_item description, item_number, Brand, SRP, ect. I want to search on the "invoice" sheet, for example, item description,and fill out the rest of the necessary fields in that row with data from my "database".
View Replies!
View Related
Convert Sheet In Another Workbook To Values Only
I am trying to be a good programmer and not do a whole bunch of activate workbooks and worksheets. So I am 99% complete with my subroutine and stuck on 3 lines. I need to copy my entire pivot table from PvtDest (which is Superdatabase.xls sheets f2 pivot) Set PvtDest = Workbooks(SSRname).Worksheets(CarrPivot) PvtDest. Cells.Copy PvtDest.Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False PvtDest.cells.copy works fine PvtDest.Cells.select Fails due to (select method.range class failed finally Selection.Paste special is supposed to paste on PvtDest but I have not been able to accomplish this. I think I have seen copy paste special in the same command line is that the solution
View Replies!
View Related
Insert Values From One Sheet To Another With A Loop
I need to insert values from a column in one sheet to another sheet with a Loop mentioned here. (I have attached a sample workbook for your kind reference.) IN STATEMENT SHEET, I NEED TO INSERT (IN col F) THE VALUES from Col A of NOS sheet.THE INSERTION SHOULD BE LOOPED AS MENTIONED HERE i.e. First time, it should be 1 to 10 Second time it should be 2 to 10 and 1 Third time it should be 3 to 10 and 1, 2 Fourth time it should be 4 to 10 and 1,2,3 Fifth time it should be 5 to 10 and 1,2,3,4…. And so on, till the last row with a value in ColA. THE VALUES IN Col A of NOS Sheet MAY BE CHANGED WHENEVER REQUIRED. There it is 1 to 10, but it may be Alphabets or any other words also. Hence, whatever values in Col A of NOS sheet should be taken for looping.
View Replies!
View Related
Searching Through A List Of Values Across A Whole Sheet
I have a list of values in column A of sheet 2. What I would like Excel to do is search for each of these values on the whole of Sheet 1. Sheet 1 has around 20 columns of information and a dynamic number of rows. In column B of Sheet 2, I’d like TRUE or FALSE displayed depending if the value is found on Sheet 1. For example AB 1 DOGTRUE 2 CAT TRUE 3 MOUSE FALSE 4 HORSE TRUE If this can only be achieved via macro, please post the code rather than a file as I can’t download the file here.
View Replies!
View Related
Adjacently Copy Values To A New Sheet
a code that will allow me to copy values from one sheet and adjancently paste them into another sheet? I would like to have theses values be pasted right under the next available blank line. Specifically, I would like a code that will select a particular range and pasteonly the cells with values to the Sheet1 on the next available blank line. And select another range and adjacently paste only the cells with values to the Sheet1 on the next available line, and so on.
View Replies!
View Related
Concatenation-copy Values To Another Sheet
I have a huge list that i need to copy over to another workbook but have a problem. the data i have is stored on 2 separate columns, A & B. I need both values to be combined into one cell and separated by a '/'. The CONCATENATE functi0n works perfectly, but when i copy these values across to the other sheet, of course i loose these becasue the original look up values for concatenation are elsewhere. How can i copy these concatenated values across to my other sheet?
View Replies!
View Related
Rewrite Sheet With Some Saved Values
I previously had a query resolved in this forum when I was trying to write data to multiple sheets but the requirements have now changed and I need to update the script. I have a multi-sheet workbook that calls a routine to rewrite sheet4 based on values in sheet1 each time sheet4 becomes the active sheet. This script makes single line entries or double line entries in sheet4 depending on whether data appears in one or both of a two specific data cells (col:J,K) in sheet1. However I now need to manually input extra data items into blank cells (col:B,C,J) in sheet4 and retain those data items after input, but each time I make sheet4 the active one it clears the contents of all the cells including the data that I have entered into cols B,C,J. Is there a way of fixing cols B,C,J on sheet4 so that they are not cleared when sheet4 is activated. Or is there a way of writing just the the additional lines added to sheet1 since the sheet4 was previously activated rather than clearing all the data lines written previously into sheet4 and rewriting them all from scratch each time the sheet4 becomes the active one. I think you will need to see the workbook that I am working on to see how the sheets link and a zipped blank version is attached.
View Replies!
View Related
Transfer Range Values To Another Sheet
why the last line below errors when the line before doesn't? Error 1004 Method ' Range' of object '_Worksheet' failed Sub tester Dim wbThis As Workbook Dim wsSrce As Worksheet Dim wsDest As Worksheet Dim lastSrceRow As Double Dim lastDestRow As Double Set wbThis = ActiveWorkbook Set wsDest = Workbooks(wbThis.Name).Sheets("Detination") Set wsTmpl = Workbooks(wbThis.Name).Sheets("Source") I've been through countless threads, specified xlapp.... etc.
View Replies!
View Related
Cell Values Used In Sheet Names
I am trying to allow for a cell selection to initiate a macro that would take the top cell in the current column and most left cell in the current row and placing those values into the names of the workbook and sheet that I want to move to. I am tyring to allow for my users to drill down into data via selecting the cell in question that is referenced from another workbook and sheet. I want to place the value of the cell = most left cell in current row - 1-1 to be placed in Active.sheet"1-1".select The work bood would pull the value from the most top cell in the current column and place that value in Activeworkbook.place here.select.
View Replies!
View Related
Copy Trendline Values To New Sheet
I need VBA to copy the values from a treadline output box on a chart to a new sheet. The macro recorder produces the code below, but I need to make the 5th line generic so that the code will apply to any workbook. The code below will not run without the "Windows("Test.xls").Activate" statement, which restricts the code to a workbook of a specifc name (Test.xls). How can I make this run for any workbook name? Sub CopyTreadlineData() Sheets("A").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select ActiveWindow.Visible = False Windows("Test.xls").Activate Sheets("Beta").Select Range("B15").Select ActiveSheet.Paste End Sub
View Replies!
View Related
Numeric Values From One Sheet To Highlight Cells On Another
I’m trying to assign building and office keys to various personnel, by appointing different classification levels to each set of keys given out. Very similar in the way an administrator would assign permission levels to individuals. Question: •Is there a way to pick up an array of non-sequential numbers from one sheet (values 1 -36) •Find those values which are assigned to columns laid out as a table, •Then highlight those found values I’ve attached a dummy sheet to illustrate my question. I have not attempted coding this because I wouldn’t know where to start. Theoretically is this even possible?
View Replies!
View Related
Return A Value Of Two Matching Cell Values From Another Sheet
I have two sheets - one work sheet and the second - database with existing entries. Going by visual example below, I need the column C of the work sheet to automatically populate with the data pulled from the database sheet. For example, C4 on the Work sheet should find both values "Bike" and "Green" in one row on Database sheet and return the corresponding value 44444 from F97. The work sheet cells in the row C should remain empty if no match found on the database sheet. Sheet 1 (Work sheet) -------A--------B---------C 1---Auto-----Red 2---Auto----Green 3---Bike-----Red 4---Bike----Green 5---Bike-----Blue Sheet 2 (Database) -------A--------B---------F 12---Auto---Red----11111 37---Auto--Green---22222 85---Bike----Red----33333 97---Bike---Green--44444 102--Bike---Blue----55555
View Replies!
View Related
Trying To Do SUMIF: Sum The Values In Row E In Sheet 2
I am thinking I need a sumif formula but having difficulty pulling data into sheet. The facts.... Sheet 1 (Summary Model): Location where I want to dump data Sheet 2 ('Phys Demand Estimates_0-17'): Location where data exists =IF('Phys Demand Estimates_0-17'!A2:A953='Summary Model'!B2,IF('Phys Demand Estimates_0-17'!C2:C953='Summary Model'!A5,SUM('Phys Demand Estimates_0-17'!E2:E953),0)) Translated...."IF in Sheet 2 looking up duplicate fields of Value1 = same value in list box on Sheet 1, and if in Sheet 2 looking up duplicate fields of Value2 in static list in Sheet 1, sum the values in Row E in Sheet 2.
View Replies!
View Related
Rename Sheet Using Multiple Cell Values
The macro below renames the current sheet to the cell value of D4: Sub Rename_Sheet() With ActiveSheet .Name = Range("D4") End With End Sub What can i do to the code above so that it will rename the sheet based on 3 cell values instead on only 1 value? I want the end result to look like this with x's between the values: D4xD5xD6
View Replies!
View Related
Listbox Values Linked To Sheet Names
I have created a userform in excel with a listbox that has the names of 4 different sheets located within that workbook. I would like for whenever a name of the sheet is highlighted in the listbox, that sheet is opened. I have played around with the listbox.value and if then statements and can't seem to get it to work.
View Replies!
View Related
Searching Column For Values And Copying To Another Sheet
I have a sheet named "original" with columns a, b, c. My code checks each cell in column C for a value and then copys columns A,B,C to a sheet named "Order". It works fine except I need it to start pasting the results in row 16 instead of row Sub PLACEORDER() Dim lastrow_first As Long Dim lastrow_second As Long Dim x As Long lastrow_first = Sheets("ORIGINAL").Cells(Rows.Count, "C").End(xlUp).Row For x = 10 To lastrow_first If Sheets("ORIGINAL").Cells(x, 3) <> "" Or Not IsEmpty(Sheets("ORIGINAL").Cells(x, 3)) Then................
View Replies!
View Related
Display Values Of Another Sheet Based On Criteria
I need to display values from cells of sheet1 and sheet2 as comment in Corresponding cells of sheet3 whose value in sheet3 is FALSE Sub try() Dim Sheet1cellval As String, Sheet2cellval As String Worksheets("Sheet3").Range("A1:D4").Select Dim cell As Range For Each cell In Selection If Worksheets("Sheet3").cell.Value = False Then cell.AddComment cell.Comment.Visible = False 'Getting the value of Cell in Sheet1 for the corresponding cell in Sheet3 whose value is FALSE Sheet1cellval= Worksheets("Sheet1").cell.value 'Getting the value of Cell in Sheet2 for the corresponding cell in Sheet3 whose value is FALSE Sheet2cellval = Worksheets("Sheet2").cell.value 'Displaying the values of cells in Sheet1 and 2 as comment for the corresponding cells in Sheet3 Worksheets("Sheet3").cell.Comment.Text Text:="Difference" & Chr(10) & Chr(10) & "Sheet1: " & Reportcellval & Chr(10) & Sheet2cellval & Chr(10) & ""
View Replies!
View Related
Copying Rows With Multiple Colum Values To New Sheet
I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets. So from below attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5. I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.
View Replies!
View Related
Hide Or Unhide Worksheets From Cell Values In Sheet 1
I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks. I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.
View Replies!
View Related
|
TRACKER
