Link Cell From 1 Sheet To Another: Pull Data Based On Time
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).
What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Pull Data From Closed Workbook Based On Cell Values
I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file. Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous month’s name and thus source file name will be placed on cell J1. The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.
View Replies!
View Related
Macro Pull Data From Each Sheet And Create A Summary Sheet
I need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better. The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
View Replies!
View Related
Cant Pull Data From Another Sheet After Drop Down List Selection From Another Sheet
I have an excel workbook with two sheets. Sheet 1 has a list of people names, rank, address, and phone numbers. Sheet 2 has boxes where a user can select a last name from all last names in sheet1. What I want to happen after the user selects the last name in sheet 2 is for it to automatically fill in the rest of the data (ie Rank, First Name, Address, city, Home phone number, Cell Phone number). What do I need to do in order for this to work?. Attached is a very small example of my much larger project.
View Replies!
View Related
To Pull Some Data From One Sheet Into Another
I need to pull some data from one sheet into another. Here is an example of what I am trying to do if anybody know hows to do this? I need a formula that will look in sheet 1 look at the idnumber, match it with the idnumber in sheet 2 ande fill in the serial. sheet1 idnumber serial number 12345 66181 12346 66182 12347 66183 sheet2 idnumber serial number 12345 insert serial number here 12346 12347
View Replies!
View Related
Data Extraction (pull The Data To Another Sheet For Each Line)
I want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below: Col. ACol. BCol. C Smith101 Jones512 Green65 Black1214 Smith 3612 Jones1512 Dual25 Green1225 I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below. So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.
View Replies!
View Related
Pull Data From Worksheet Based On ID Number
I have 2 worksheets (Sheet1 and Sheet2). Sheet 1 contains around 3000 records with multiple fields (columns). Column A contains the ID number. Sheet 2 contains some records with different fields except for the ID number which is common. I need a macro to retrieve information from sheet1 and copy it to sheet 2 based on the ID Number; i.e the macro needs to get the ID number from sheet2, locate it in sheet1 and copy the data from column D in sheet1 for that particular record and paste it in sheet2.
View Replies!
View Related
Pull Out The Data In The DATA TABLE Sheet
I've been playing around with VLOOPUP, MATCH, SUMPRODUCT... But I can't seem to get this one right.. See the attached Excel file.. I think it should be fairly explanatory.. I want to use the dates in SUMMARY sheet, to pull out the data in the DATA TABLE sheet. The numbers in Column A on the DATA TABLE sheet, is first date in each month, and the numbers represent the day of the month.. . Example: 01.01.2009 1 2 3 4 5 .. 30 01.02.2009
View Replies!
View Related
Pull Data Out Of A Closed File Based On A Date Range
I am trying to make Excel pull data out of a closed file based on a date range. The closed file is a log where people input data and enter the day they are doing it. I need to pull this data in my file and make it print into my worksheets so I can pass a report on to others with only the data they request from the log. The range would be for 7 days and would have to be able to choose a few columns off this report.
View Replies!
View Related
Link The Form To A Sheet To Record Data
once I get the pretty boxes and such, how do I link this form to a sheet to record data? How do I get it to run? I have tried searching the forum, but it seems that most of you already know that part and don't have any problems there. I know this is probably very very basic and I will smack my face with my hand when you point it out. I just have never created forms with Excel before. I am used to using SalesLogix and when I create forms there, I have a database table that I can link each field to, so I keep looking for how to link to the table and I can't figure it out.
View Replies!
View Related
Autosort Data When Data Changes And Then Link To Sheet
I have a column of data that constantly changes in length and want this column to autosort the data alphabetically when the data changes. the list contains the names of worksheets I then want to display in the next couple of columns data from the worksheets listed in the column. i know i can do this manualy by saying that that the cell equals sheet? cell etc but was wondering instead on putting in a formula of ='A sheet'!E20 i can put something where A Sheet is that uses the name in the column of data ?
View Replies!
View Related
Populate Adjacent Cell Based On Pull Down Choice
I have three hidden columns (Text only) one column with Parts, then Description, then Cost. I have a pull down menu (i.e. Cell 3, A) listing all of the choices in the hidden Parts column. I want to automatically populate Cell 3, B with the corresponding Description when a Part is chosen from the pull down menu, and the same in another column for the Cost. I am using Excel X for Mac, in OS 10.5.2
View Replies!
View Related
Macro To Link Cell To Sheet & Cell Stated By User
I have a macro that inputs a sheet and cell from the user. when the macro tries to make a formula it outputs =data!'AE3'. I need it to output =data!AE3 Dim Sheet As String Sheet = InputBox("Take Data from where?") Dim startcell As String startcell = InputBox("Start Cell?") ActiveCell.FormulaR1C1 = "=" & Sheet & "!" & startcell & ""
View Replies!
View Related
Rename A Sheet Based On Cell Data
I'm looking for a solution that will rename the sheet to whatever is enterd into particular cell. Ie: cell A1 = "Joe Bloggs", then the sheet is renamed to Joe Bloggs Also, on the same sheet, is it possible to change the sheet tab colour based a data in a different cell? Ie: cell B1 = "Parts", then the sheet tab colour changes to pink.
View Replies!
View Related
Hide Columns Based On A Cell Link
I need to hide columns in a spreadsheet without using the hide/unhide columns option. I found an article which suggested setting up a combobox from the forms toolbar, this is populated by an input range and linked to a cell for a numeric value. The article although using a macro to hide rows suggested this code Private Sub ComboBox1_Change() If ComboBox1.Value = "A" Then ActiveSheet.Range("1:3").EntireRow.Hidden = True ActiveSheet.Range("4:6").EntireRow.Hidden = False ElseIf ComboBox1.Value = "B" Then ActiveSheet.Range("1:3").EntireRow.Hidden = False ActiveSheet.Range("4:6").EntireRow.Hidden = True End If End Sub I want to amend this code so that when the number in my linked cell changes the various columns are hidden. For example if the cell link number = 1 hide columns e to az if the cell link number = 2 hide columns d and f to az etc. I have had varying success with amending this code to entirecolumn.Hidden and changing the Combobox1. to dropdown1. but am unable to get it to work, I also stupidly deleted the code that was partially working and am now unable to remember what I'd done.
View Replies!
View Related
Link Formula To File Based On Cell Value
I have a problem that may be a little bit diff. from must of the Readonly post that I have found here. Most people would like to have all of there files opened as readonly files, but I don't in this case. I am copying a number of files that has VBA code in just about all of them. I am using a CD writer to copy the files to a CD using Windows Explorer and that is working just great, No problem copying the files. My problems comes in when I copy the files from the CD to another computer, all of the files are Readonly files on the new PC. I have not saved them as readonly and don't want them to be readonly files. Does anyone have any ideas why this is happening what I need to check or change to get the files to another computer without being a readonly file.
View Replies!
View Related
Add Hyperlinks To Range & Link Based On Cell Value
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001. So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders e.g. \servernamefolderRequestsRXXX-0001.doc There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too. FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders. I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.
View Replies!
View Related
Link Data In 1 Cell
Sample data: Machine Part Number machine 1 123 machine 2 123 machine 3 123 machine 1 123 machine 2 456 machine 1 456 machine 3 456 i need to based on tis sample data come out with sth like tt: Used In: 123 Machine 1, Machine 2, Machine 3 456 Machine 2, Machine 1, Machine 3 I need to group those machines under 1 part number. For instance, part 123 is used in which machines, I need to state them in a way like above. But repeated should not be stated again.
View Replies!
View Related
Pull Data From Different Columns Based On Info In Column "A".
I'm having a little trouble figuring out what formula to use for situation. I have 5 columns of information. Column A = School # of current school Column B = School 1 Name Column C = School 2 Name Column D = School 3 Name Column E = School 4 Name What I want to do is in Column F to look through a row of information and find the current school. So: If A="1", pull from B If A="2", pull from C If A="3", pull from D If A="4", pull from E I tried doing a VLOOKUP table, but I think I'd have to do one for every row, but I could be wrong. I also thought of INDEX(MATCH), but I couldn't quite figure it out.
View Replies!
View Related
Compare Data On 2 Separate Worksheets And Pull A Reference Cell
I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions. The format is: Sheet 2 A/B/ 334702/UEMR88QX 334703/UEMR85QX 334704/UEMR81QX 334707/UEMR8JQX The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesn’t exist on the inventory sheet? Master A/B/C/D/E/ 35/ 465/881676311350/311350/UEMR8ZTU 36/469/881676310722/310722/UEMR8V5V 37/483/881676310924/310924/UEMR8XYU 38/805/881676331096/331096/UEMR8J13 My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month. I know I can copy the column and past is as a value so I won’t lose the numbers as the master changes but getting the data onto that months inventory has been a pain.
View Replies!
View Related
Link Formula: Use Cell Data As Workbook Name
If I have a two files called test1.xls and test2.xls for example. Is it possible to link test1.xls to test2.xls by putting the filename in a cell? i.e. In test2 put the filename in say A1 and in the link put the path and instead of naming the file you want to link to put in A1 instead. I know it doesn't work if you do it that way I was just wondering if there was a way to do it.
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
Macro Create A Hyper Link Once Data Is Entered Into Specific Cell
Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system. Each stock item has its own worksheet and the product code is entered in to cell E3 (which in turn has a macro in it to change the sheet name to match the product code). the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link. i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet. I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.
View Replies!
View Related
Pull Out The Sample Date And Time 20 Times
I have a file that samples performance on a cpu. The output shows the sampling date and time in column A. This column changes depending on how long I run the file. No matter how long the column, I only want to pull out the sample date and time 20 times. So if I had data in column A with 300 rows, I want to divide 300 by 20 and pull those 20 numbers and put them in column B in chronological order. Same thing if I had 4000 rows in column A, I only want 20 samples.
View Replies!
View Related
Formula: Link Every Other 13th Row In Sheet 1 To Sheet 2
I have worksheet1 with 200+ customers and 12 months of data for each customer. I want to link each month to a separate worksheet (worksheet2, 3....12) for all customers. (i.e., customer 1's January activity across row 1, customers 2's January activity across row 2.....) Is there a way for me to create a formula or copy comand to link row1, worksheet1's data to row1 worksheet2 and then every 13th row in worksheet1 to row2, row3...in worksheet2? Right now I'm just putting the = in worksheet2 and mousing over to worksheet1.
View Replies!
View Related
Link To Cell Instead Of Getting Cell Data
I want to create a summary page with the tab name and a link to the contents in a specific cell. In the code below, I can get the sheet I want but in the second column, I would like to link to the data instead of getting the data in the cell. Can I do this with a simple modification to this Dim a As Long Sheets.Add.Name = "Notes" For a = 1 To Sheets.Count Worksheets("Notes").Cells(a, 1) = Worksheets(a).Name 'The next line gets me data, but I want to link to this cell in all worksheets Worksheets("Notes").Cells(a, 2) = Worksheets(a).Cells(15, 18) Next a
View Replies!
View Related
Copy Rows From Selected Sheet To New Sheet Based On Cell Value
i have a workbook that has a lot of sheets but i need to pull information from the one sheet "Veneer Log" i Need it to make new sheets with the same heading as on the "Veneer Log" (Rows 1 & 2) Sheet but it needs to be filtered by the "Product" Column (H) with a new sheet made for all the diffrent products i.e. Dimensional, Drywall, Corners - Thin V., Accents,..... so each product will have a new sheet with i am hoping someone can help me with this. This log changes Daily and it would be nice to have a sheet with only the same product on it to compare new orders so we can batch run. i hope i have given you enough information so someone can help me with this. i have attached a sample log the real log has about 10 worksheet for diffrent departments but i only need info from the Veneer Log Sheet.
View Replies!
View Related
Copy Data From Userform To Sheet, On A New Row Each Time
I have designed a userform that allows teachers to input assessment grades and calculate overall module grades based on these... I'd like to develop a macro that would then allow the teacher to click a "save" button on the userform, triggering the transfer of the information on the userform into the next sheet and then clear the contents of the userform ready for the next calculation. I have made an attempt; unfortunately I have very limited experience of Excel and am therefore running into difficulties; the macro is as follows: .....
View Replies!
View Related
Index Function - Data Identified Based On A List Of Account Numbers And Return This Data To A New Sheet
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet. In "Money" I have: IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc. (above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars) So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes. I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View Replies!
View Related
Automatic Time Stamp Cell Based On Adjacent Cell
I am looking to add a function to a current spreadsheet that writes the current date to a cell when another is updated. The sheets function is to have a user record when a particular action has been completed and then remind them after a given amount of time. The user is faced with an option to input a "1" to essentially "start the timer" and then the date that is automatically input by the code will be used to compare with the current date. I have tried the following on the Workbook_SheetChange sub If ActiveCell.Column = 9 And ActiveCell.Value <> 1 Then ActiveCell.Offset(-1, 2).Value = Date End If But cannot figure out how to eliminate user error. Let me explain. If a user enters a value (will only ever be a 1 to indicate "yes") into column 9 then the date appears in the correct place (two cells to the left) If however a user deletes a value then the date will update one cell too high!? I also thought of using a cell based if statement (if j7 = 1 then today() else "" but this only updates with todays value each time you open where I want the date to stay as the day the cell was updated. The date is going to be used as a way of working out when to change the original value of the cell in column 9 to "".
View Replies!
View Related
HOW CAN I PULL INFORMATION FROM ONE SHEET TO ANOTHER IF NOT #
I need a formula that will take info from sheet A column K only if sheet A column E is equal to sheet B column E. I need it to paste into Sheet B column K exactly as shown in sheet A column K. I tried using a Vlookup formula but I got #N/A and I am not sure if that is because there is text as opposed to numbers or if I am using the wrong function entirely.
View Replies!
View Related
Offset Real Time Data Based On Validation List
What I have: I have 12 months in a year and each number represent a month. I have generated lists of data mainly interest and principle payments from 12 amortization sheets which i have just copied into the attached spreadsheet and will change accordingly based on the loan terms which doesn concern us. For our purposes we wont make distintion between principle and payment as i just need to see how to make what i need work. What I need: I need to offset data vertically automatically once i the user selects an option value (Number in this Case) from the validation list located within the Payment control box. Going from left to right you see the raw data but in this example i need the data on the right hand side to be offset down the number of rows which may be found in the control box as a validation list. IE: So if i were to use "Loan I" [L6] and select a "2"[J6] from the "Payment Control" then the data on the right hand side [L6[ would reflect a $0 but instead the new value for L7 would be "$24.96" and would continue down for about 374 rows. This will have to be done for Loans I - Loans N [L6:Q6] respectively. This is all i need in order to compile and make use of in my spreadsheets. Thanks and cannot be a formula as forumlas will be overwritten and cannot be a macro so it must be a module in itself so it an run when the list is changed. the single digit values in coulmns M6:Q3 are there mainly so you can see the difference between the base and the position at which the data is placed at. its not need but is there to stress and to show the number rows in offset of data.
View Replies!
View Related
Lookup And Pull Results Into New Sheet
I am trying to find a function that will scan down a column to find a particular criteria. I would like all the cells that meet this criteria to have their entire row pulled through to a new sheet. For Example, In the attached spreadsheet, I would like everyone in sheet 1 to have their details pulled through to sheet 2 if they are in class 1, sheet 3 if they are in class 2, and sheet 4 if they are in class 3.
View Replies!
View Related
Pull From Another Sheet And Sumproduct And TEXT
I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same row as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working b/c it wont take TEXT. Plus I want it to search across all of the columns, not just one. Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) : =SUMPRODUCT((Sheet2!B1:B6500=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500)) Is there someway to fix this, it would be a huge time saver. -------------------------------------- 1Q08 Revenue Direct Costs Gross Profit Indirect Costs Stock Based Compensation Restructure Charge ....................
View Replies!
View Related
Insert Rows Based On Cell Time Value
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time. For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
View Replies!
View Related
Return Result Based On Time In Cell
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time. If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs) If done at 0830 hrs, Shift 2 (0801 to 1600hrs) If done at 2030 hrs, Shift 3 (1601 to 2359 hrs) I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
View Replies!
View Related
Date And Time Based On Shift In Cell
If I use =now() in a cell it will enter the current date and time. For shift workers, this does not work. I'm tring to figure out a formula that will only change the date at 6:00am every day. This way a person working the night shift will not see a change in the date on his sheet at all, only the day worker.
View Replies!
View Related
Restrict Time Entries Based On Another Cell
The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.
View Replies!
View Related
Creating A Source Sheet To Pull Information From.
I'm trying to do is create a macro that will autopopulate information from a source sheet. What I have is a different route sheet for 25 or so technicians within my company on one worksheet. I want to be able to have a source sheet that I can change daily and have it autopopulate the information needed into the appropriate cells/sheets from this one source sheet. I am pretty sure there is a way to do this, but I'm not familiar enough with creating macros to do this myself, although if I had an example of one cell and a simple explanation of how to fit this to the information needed I think I could do this on my own.
View Replies!
View Related
|
TRACKER
