Select Tabs Based On Cell Entry
i'm trying to basically use VBA, so that when a list of work tab names are entered into a column in say Sheet 1..these tabs are then selected.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Rename Tabs (Sheets) Based On Cell Contents
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets. Eg. In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically. In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
View Replies!
View Related
Cell Sound Based On Entry
I will try putting the Code Tags in correct this time, I think I figured out what I have been doing wrong. This code will allow one cell to make a sound when a condition is met. I am trying to have each cell stand on its own. Example cell A1 would sound a Ding if the number >100 and cell A2 would sound a Tada if the word Cat would be returned ....
View Replies!
View Related
Countif - Based On Other Cell Entry
dont know if this can be done using a formula rather than VBA...but is there a formula that would change the countif range based on another cell entry. so the formula below has a column range of 2...so the other cell entry would be '2'. =COUNTIF(B1:C1,"=Y"). If i then changed the cell entry to '4' the formula would change to. =COUNTIF(B1:E1,"=Y")
View Replies!
View Related
Fill Value In A Cell Based On User Entry
I have a dilemma. 1.I want to be able to a have a cell be filled with a certain value when the user enter data in Column C. 2.I know that this can be accomplished by a formula, but my spreadsheet will work my efficiently with a macro. 3.I do not know much about VBA but I believe that I will need a macro for each sheet that fills in the word.
View Replies!
View Related
Force Cell Entry Based On A Condition
I need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
View Replies!
View Related
Restrict Cell Entry To Numbers Based On Another Cell
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple: Cell A1, they can select Rice, Cheese, or Rabbit I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10. Can I do that with data validation? I can't get any if thens to work in it.
View Replies!
View Related
Printing Select Tabs Only
I have a Services Quote work book that contains many tabs only some of which may be used for a particular quote. I would like to have a macro that I could assign to a button that would check the value of a particular field on each tab and if that field is greater than 0, then it should select that tab as one to be printed. Once all tabs are checked and either selected or not selected, the macro should print the selected tabs.
View Replies!
View Related
Insert Formula Based On Cell Entry Using Vba
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3 i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
View Replies!
View Related
Copy Data Based On Cell Entry To Different Spreadsheet
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid) example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account. If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1. If D:D is "payment pending" do same as above but in row 2 and so on.
View Replies!
View Related
Code To Select All Tabs In A Workbook
I have been recording different sets of commands then copying/pasteing them together to complete what I want. I have tried to clean it up as best I can but im sure it does things it does not need to. Is there a way to tell it to select all sheets in "Book2.xls" then remove the formatting (font color,comments,shading) rather than using the Array and naming each and every sheet? What this will end up doing is openeing about 12 files, copying specific tabs from each of those files and pasteing them all to the same workbook (in this example it would be Book2). Then removing the formatting. Workbooks.Open Filename:="F:123Book2.xls" Windows("Book1.xls").Activate Cells.Select Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy Before:=Workbooks("Book2.xls").Sheets(1) Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Cells.Select Selection.Interior.ColorIndex = xlNone Selection.Font.ColorIndex = 0 Selection.ClearComments
View Replies!
View Related
Restrict Cell Entry Based On Another Cell
I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell. So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
View Replies!
View Related
Formula To Return The Value In A Column Based On Cell Entry In Row
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case). I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View Replies!
View Related
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M. I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting: Cell value is less than or equal to =TODAY() All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M. In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View Replies!
View Related
Hide/Show Sheets Based On Cell Values & Validate Entry
excal VBA programming.I have attached the file name "help" for your easy explanation purpose. 1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell. 2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Select Multiple Worksheets Based On Cell
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was Sub Selects() Dim strSht As String Sheets("Criteria").Select strSht = ActiveSheet.Range("L31") Sheets( Array(strSht)).Select End Sub
View Replies!
View Related
Select Sheet Based On Cell Content
I am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the sheets( range("c5")).select to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Macro To Select Dates Based On Cell Values
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive. H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page. Below is what my limited understanding of VB came up with. Sub Date_Range() Dim First As Date Dim Last As Date First = Range("H4").Value Last = Range("I4").Value Sheets("Graph Data").Select Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _ , Criteria2:="
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Single Cell Entry To Multi Cell Entry
i have an address that is all in one cell displayed like 101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS but i need to have it split in to individual cells so Cell A1 would be 101 hampton court B2 Hampton Heath C2 Hampton Town D3 Hamptonshire E5 HA01 1AS each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle. also i need it to work backwards ie it finds the post code first, then the county then the town as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
View Replies!
View Related
Sum Tabs Based On Criteria
I need to create the formula in "Sum state" sheet B2 to sum items for tab AZ (b2:b6). The formula needs to work when I drag it down to other cells. For example if I drag formula to B3 it needs to adjust the tab from AZ to CA, and add things for that tab. Each cell from A2 :A 11 represent a state and each tab has actual info for each state
View Replies!
View Related
How Do You Print Only Certain Tabs Based On A Fields Value
I was recently posed with the following by our PMO: "Can I, or how do I, print only those tabs that belong to specific PM? I know I can go and select just those tabs collectively and print but I hate sifting through these 40+ tabs to find the ones that belong to PM "X". Can't you just use, or create, a macro or something?" I replied that this might be limitation of Excel but that I would research it and see. The project template cell reference is D5, there are 11 tabs containing project info for this PM (out of 43), it is running on XP, and is in Excel 2002.
View Replies!
View Related
Hide Tabs Based On A Value In Another Tab?
I have a "master" tab that has a dropdown menu (via a validation list) which will determine what the spreadsheet does. Based on the value selected via this dropdown, I would like to show the relevant tabs and hide the irrelevant tabs. I would prefer for this to happen automatically without a macro.
View Replies!
View Related
Count Sheet Tabs Based On Color
I have a worksheet with like lots of color coded tabs and the tabs increase everyday. The tabs are categorized based on 6 colors. I created a first tab in order to give how many tabs i have another each color. Is that doable in excel. Do I need a macro for this. All I want is each time i open the excel sheet the first tab will be automatically updated with how many tabs I have under each color instead of me counting the tabs.
View Replies!
View Related
Macro To Create New Tabs Based On A Contract Number
I work with contracts and have to report the sales for each contract by customer number, name, address, product code or sku, sales amount, quantity, and invoice date. Each contract number is associated with a product group and within that product group are a laundry list of product codes or skus. Customers are often on different contracts for different products and may purchase some or all of the products on that contract. When I run a query from the system to extract this data, I get one sheet that contains 20,000 lines that have to then be sorted and seperated into different tabs based on the contract number. Keep in mind that a customer may be listed multiple times because each line in the query represents a product code and an invoice date. I currently have a macro that will format the reporting such that it sorts the data, however, what it doesnt do is look for each instance of a particular contract number and separate it into different tabs. So below are two examples: April and May 2008 Reports April 2008 Report Contract 1 contains 5,000 lines of data - extract into new tab Contract 2 contains 5,000 lines of data - extract into new tab Contract 3 contains 5,000 lines of data - extract into new tab Contract 4 contains 5,000 lines of data - extract into new tab Total lines = 20,000 May 2008 Report Contract 1 contains 7,500 lines of data - extract into new tab Contract 2 contains 5,500 lines of data - extract into new tab Contract 3 contains 8,000 lines of data - extract into new tab Contract 4 contains 9,000 lines of data - extract into new tab Total lines = 30,000 So for the April Example, I would like excel to take this one list and break into 4 new tabs based on the contract number and carry over all of the data into that tab. Where this may become difficult is that there may be more lines of data one month and fewer another. Im not sure if there is a function that will tell excel to stop when it reaches the last line for one contract number and to start a new tab for the next contract number. (see the month of May example)
View Replies!
View Related
Click Button To Rename Sheet Tabs Based On Range
I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc). What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).
View Replies!
View Related
Entry Based Execution Of Macro
I have to make eighty to ninety entries daily in Column B. Then transfer these entries to another column N,(by copy/paste) after that a maco "text to column" is executed. Now I have linked Column B to N like =B2, =B3, etc. etc. thus instead of copy/paste, the data when entered in Column B, is automatically going to Column N. I wish that the macro (text to entry) may be executed by each entry made in Column B. Is it possible with a macro?
View Replies!
View Related
Countng Cells Based On The Entry
i have the following formula which counts various cells containing dates and i need to add in a count for 1 cell, BV21 but it needs to count 3 if theres a date and 0 if the cell is empty or reads NA =COUNT(AS21:AX21,BH21:BN21,BR21:BU21,BW21:BZ21)+ (COUNT(AY21,BB21,BE21,BO21)>0) +(COUNT(AZ21,BC21,BF21,BP21)>0)+(COUNT(BA21,BD21,BG21,BQ21)>0) +IF(CB21>0,CB21,0)+IF(CD21>0,CD21,0)+IF(AP21="Y",2)[/size] might be simple to some but it really has me stumped.
View Replies!
View Related
Automatic Formatting Based On Number Entry
So the title isn't really totally descriptive but it was all I could come up with. I have a work book that has 30 identical sets of 5 cells for users to enter in information. Some of my users will not need all 30 sets. I'm wondering if there is a way for the user to enter the number of cells they need and have excel then format so they have the desired number, instead of 30. Then have excel change all the instances of this number in formulas so that it matches the new number of cells. I hope I explained this well enough for someone to help.
View Replies!
View Related
Forumla Highlight A Entry Based On Count If
If I minus H is less that 90 count as 1. But if I minus H is greater than 90 count as one but highlight the row on the speadsheet in yellow. What I am doing is I have a list of about 3000 entrys and I need to determine if (I minus H is greater or less than 90). I then need to count how many times out of the 3000 entrys each entry is greater or less than 90 and highlight the entrys in yellow that are greater than 90. It would be ideal to determine the difference between I minus H and then categorize the findings of the 3000 entrys to see how many were say 59 and how many were 47 and how many were 92 etc. I have never seen a forumla highlight a entry based on count if. I am currently rapidly searching the forums here but to no avail.
View Replies!
View Related
Selecting Cells Based On Textbox Entry
I have a user form with some option buttons (1,5,10,etc...) that are used so the user can select a given number of cells they want selected. My last option button is labled Other(optOther) and has a textbox next to it(txtOther). What I am trying to do is give the user the option to either select one of the given numbers or be able to enter their own number. The data will then be extracted from one worksheet to another. I have all the coding for the optiong buttons with the given numbers, just cant figure the textbox one out. My data starts on B5, so what i am trying to do is when the user enters number n, i would need data from B5:B(n+4).
View Replies!
View Related
Hide Columns Based On A User Entry
Is there a way of a user can add a number in a cell and this would hide a different amount of columns. OR : Ideally I would prefer a drop down with dates and the user could select a forward date and all the columns with dates up to that would appear. This would mean they could look as far our as required and all unwanted data would be hidden.
View Replies!
View Related
Lock Cells Based On Entry In Other Column
I have a spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
View Replies!
View Related
Color Cells Based On Entry. More Than 3 Conditions
I was hoping there was an alternative to having to use so many named ranges for each sublist (there would many sublists in the actual workbook). Anyway, I'm a little uncertain about the code you posted - do the LIST and SUBLIST codes go in Source in Data Validation, or in Source in Dependent Validation Lists Based On Header Values? Can you give me a bit more instruction on this? Also, I'm trying to use the following code to color cells upon a change in value in V1 or V2, but can't get it to work. Can you kindly tell me what's wrong with it? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "V1" Or "V2" Then Exit Sub Select Case [V1].Value Case "A" Target.Interior.ColorIndex = 40 Case "B" Target.Interior.ColorIndex = 35 Case "C" Target.Interior.ColorIndex = 36 Case "D" Target.Interior.ColorIndex = 34 Case "E" Target.Interior.ColorIndex = 19 Case "F" Target.Interior.ColorIndex = 24 End Select End Sub
View Replies!
View Related
Copy Information Between Worksheets Based On Specific Column Entry
My workbook contains several worksheets each recording the results of equipment tests - one sheet for each piece of equipment. Rather than open each worksheet to check the date of the last test, other 'overview' worksheets pick up and display the last test date from each equipment record using the formula =MAX('sheetref'!A15:A500). Is there a better formula to do this? Some items of equipment are tested at more than one frequency, ie Monthly, Yearly, etc. Therefore, the entry on row 15 of an equipment record may be column A 28/10/08 column D 'M' and row 16 column A 29/10/08 column D 'Y'. The above formula will only display the last test date, irrespective of frequency. There is a seperate overview sheet for each test frequency therefore, I need to modify the formula so that the date it copies from the equipment record worksheet to the overview worksheet is the date of the last test for the specified frequency.
View Replies!
View Related
Automatic Entry Of Dates Based On Month & Year In Cells
I want to create a monthly timesheet which contains 9 columns for (Date, Day, Project no., Activity, Time In, Time Out, Total Hours, OT Hours, Remarks) I have used IF Function to calculate Total Hours & OT hours automatically. Time IN & Time OUT, Project No., will be entered manually on daily basis. Weekday function is used in the Day column to return the corresponding day of the date in the Date column. Name of the Month and Year will be manually entered in the designated cells I3 and I4 respectively. Now the solution I am looking for is, the dates should be automatically entered in the Date column (in cells A8 to A38) based on the Month & Year entered in cells I3 & I4. Dates of the corresponding month of the year should only be filled in. (If a month is not having 29, 30 or 31st day, the corresponding cells should be left blank. i.e. nothing should be displayed in the corresponding cells). I am looking for some sort of formula to enter in the cells of Date column (A8 to A38) achieve this. I have searched the forum and could not find anything which could at least give me an idea about the kind of function or formula to be used. Attached here is the time sheet I am trying to create.
View Replies!
View Related
|
TRACKER
