Changing Sheets Depending On Dropdownbox
Feb 23, 2009
I have bunch of sheets lets call them
Germany, France, Belgium, Poland, Russia, Data
In the data sheet I have in F1 "Germany", F2 "France", H1 "Belgium", H2 "Poland", H3 "Russia"
In each of the sheets (appart from data) I have a validation list in cell D6 listing the data sheet values F1,F2,H1,H2,H3.
Now basicaly what I need is when I select Germany from the list it would take me to Germany sheet, when I select Belgium from the list it would take me to Belgium sheet etc.
Basically the sheets have always same name as the name in the list. I know i can write the code in individually but because I have a lot of these I was hoping there might be an easier way?
View 9 Replies
ADVERTISEMENT
Dec 1, 2007
it is possible to change the colour of a cell text and / or cell background depending on a value.
For example I have currently 2 cells with numbers in
G3 = 4.38
K3 = 8.68
What I wolud like to happen is for the cell with the lowest value to change to Red.
The Column letters will always remain the same but I do plan to add a lot more rows.
View 10 Replies
View Related
Sep 9, 2007
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
View 4 Replies
View Related
Jun 18, 2014
I'm trying to figure a function wherein if I change the category, the results will automatically changes. see attached sheet.
View 6 Replies
View Related
Jan 16, 2007
I'm putting together a spreadsheet to do with football betting, that involves calculating various stakes and odds and probabilities. I've done pretty well, but I want to take it to the next level.
At the moment, I have 4 sheets which are completely self contained:
sheet one = betting on 2 matches
sheet two = betting on 3 matches
etc
This was the easiest way to do things when I was learning.
Now I want to look at incorporating more flexibility into my spreadsheet. I want to investigate the possibilities of having a small form to be filled in, such as the following:
Number of matches to bet on... a drop down menu for the user to choose from
and then for the visible area of the sheet to change to show the appropriate layout for their selection. So they'd choose the number of matches they want to bet on, and the spreadsheet would display what they need automatically.
I've looked at putting forms in and think I could do that quite easily, but how can achieve the effect of changing the layout/visible area depending on the chosen option?
View 11 Replies
View Related
Sep 4, 2013
I have a table showing data for 10 teams. There are however 10 different measures which are pulled from a seperate sheet using a dropdown box to select the measure.
One of the measures is example 390823 which needs to be format 390,823 so that thousands are shown, and other formats are, example, 89.9 which need to be 89.9%.
Ive gone to my other sheet and all the raw data is showing correctly with % and ,s. But my dashboard table can only have one format. How do i make it so that each measure pulls in the correct format when selected, without dragging the whole area and changing each time?
View 1 Replies
View Related
Jan 15, 2014
I inherited a worksheet with 70k+ rows of data. As shown below each row contains a record number(Col A) with the date(Col B) it was created and value(Col C). There are 5 records per day. You can see in the example below that Col A has the value 30 duplicated for each day. Is it possible to rename only the second "30" in Col A for each day?
Col A
Col B
Col C
10
1/14/2014
10
[Code] .........
View 3 Replies
View Related
Jan 26, 2007
I'm having a difficult time figuring out out to coordinate two cells together.
If a "1" or "2" is entered into cell A1, I would like cell A2 to have a drop down menu show a "0". If a "3" is entered into A1, I would like B1 to have a drop down menu show "1" or "2". If a "4" is entered into A1, I would like B1 to have a drop down menu show "3" or "4". Lastly, if a "5" is entered into A1, I would like B1 to show "5" or "6".
I don't know if this is possible without using VBA.
View 9 Replies
View Related
Feb 20, 2007
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies
View Related
Jan 6, 2009
how I would do the following, as I have been having a play but not managed to get anything.
I have two cells, C10 and C11, they both contain numbers. I want to create a hyper link that goes to the sheet called: Semester C11 Year C10
e.g. if the cell values are 2 and 1 respectively, I want the link to go to the sheet called 'Semester 1 Year 2'
View 9 Replies
View Related
May 9, 2004
Possible to write code to hide selected sheets in an excel file depending on which user is using the file i.e. hiding sheets that are not applicable to that person while just showing the applicable sheets?
View 2 Replies
View Related
Nov 13, 2012
I'm actually using a macro that saves all the sheets from a workbook in new separate files, and save them in a specified folder.
Code:
Sub Estrazione_Schede()
Dim n As Long
Dim myNome As String
Dim myPath As String
myPath = "D:path"
[Code] ......
What I'm trying to do is that, instead of saving all the sheets in one folder, the macro would save them in different folders, depending on specific values in a specific cell of each sheet. I tried to modify the macro like this, but it doesn't seem to work properly:
Code:
Dim n As Long
Dim myNome As String
Dim myPathOne As String
Dim myPathTwo As String
myPathOne = "D:pathOne"
[Code] .....
With this code the macro ends up to check the condition only in the first sheet and then it copies all the sheet in the same folder.
View 2 Replies
View Related
Feb 7, 2007
I’ve done a search of the forum for a macro and found some post that seems to be about what I want to do, but unfortunately my minimal vb experience prevents me from adapting them to my requirements.
I‘ve got a workbook with three sheets; say Sheet A, Sheet B and Sheet C. I want the info in Sheet C copied to either Sheet A or B depending on the info in cells in Column A of Sheet C.
Sheet C contains customer info, there are about 9 column headings and up to 30 000 rows (Individual customers).
The cells in Column A will contain a number between 0 - 23. What I want the macro to do is, if a cell in Column A contains any of these numbers, 0, 1, 2, 6,7,8,9,10,17,19,20,21,22,23, I want that whole row copied to Sheet A and if it contains 3,4,5,11,12,13,14,15,16,18, I want it copied to Sheet B. The cells will only contain one number, never a combination.
View 3 Replies
View Related
Jun 12, 2007
Let's say I've got nine entries of data. Column A is aligned like this:
One
One
One
Two
Two
Two
Three
Three
Three
What I would like to do is write a macro so that the three entries with "One" in Column A get cut and pasted into a newly-created sheet named "One." All of the Two's get cut and pasted into sheet "Two" and the same thing with the Three's.
View 9 Replies
View Related
Aug 7, 2014
I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
View 6 Replies
View Related
Nov 19, 2008
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies
View Related
Jan 28, 2014
I was looking for some code that I could change the data in cell E5 to "Bom RevD" on all sheets in my workbook.
View 2 Replies
View Related
Feb 21, 2014
I'm looking to perform a Find and Replace over all text boxes on all sheets.
I found this vba over at excel ribbon.tips which does the trick, but only works on the active sheet... I'm looking for it to work over all sheets. I'd also like it to look for 2 different instances.. For example, Find A replace B, and Find C replace with D.
[Code] ....
View 8 Replies
View Related
Mar 27, 2009
Made a macro to Copy a value and paste is as "Special" for Value only.
The problem is it won't function correctly Unless I force a switch to the sheet where the calculations are. (Which I want to avoid)
The current code is:
View 5 Replies
View Related
Jan 11, 2008
I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -
View 5 Replies
View Related
Jul 13, 2006
I have a raw_data sheet with a list that is linked through
Insert> Name>Define. This list is used for Validation Data List drop
down menus in other sheets. I would like to update a
list element in my raw_data sheet and have the corresponding element in
other sheets also update. Is this possible or is there another way to
do this?
View 9 Replies
View Related
Feb 25, 2008
I've been referencing sheets in my code directly with things like
Sheet1. Range(NamedRange)
however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to
#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2
'and then use these throughout the code....
SHEET_SC_CONFIG.Range(NamedRange)
(cannot reference by sheet name, as end user may change these)
View 6 Replies
View Related
May 18, 2006
How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher.
I have a group of 50+ workbooks which are all set up in the following format:
-Contains 30+ sheets.
-Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible.
-However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.
Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.
View 2 Replies
View Related
Jan 27, 2014
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
View 3 Replies
View Related
Jul 4, 2012
I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.
I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.
This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).
View 2 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Aug 29, 2008
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
View 7 Replies
View Related
Feb 26, 2012
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
View 2 Replies
View Related
Aug 9, 2007
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
View 9 Replies
View Related
Jan 25, 2014
[URL] ....
I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.
View 1 Replies
View Related
