Changing Column Data Based On Another Columns Focus
Mar 4, 2014
I have 1 sheet called Setup which users enter their character name, their class, and their race, additionally the entire workbook manages data for up to 9 individual characters.
On another sheet called C|R|E, I have tasks listed that are based on each class type and each race type that are available. Currently there are nine classes and four races. Currently on this sheet each class and each race has its own individual data lists and such, and formulas are done in a way that user inputted data can be entered anywhere, but wont count unless the class and race match those selected on the Setup sheet. To keep the sheet short in appearance I used command buttons to hide/unhide classes or races not in use by the user.
What I was wondering is (and this could be hard to describe), can data in one column be changed to reflect data based on another column's focus? I will explain in more detail.
Column B contains the task information for each Class and Race. For example Cells B13-B51 for one class, Burglar. Columns D-L is where users enter data to show if the task is completed or not. In this example lets say the users first character listed on the Setup sheet is a Champion. What I want to do is check the Setup sheet Char 1. Then based on what Char 1 is selected as on the Setup Sheet, C|R|E B13-B51 data is changed to reflect it, IF Column D currently has focus. If Column E gets focus then B13-B51 changes based on the class selected on the setup sheet for character 2. and etc.
If this can be done I can remove the 13 different sections and have it down to 2 sections, one for class, one for race. For now I am using check boxes to hide classes and races they are not currently using, but would like to move all classes into one table and all races into one table to they dont have trouble with columns they have to skip over currently.
This is a sample of how I am currently working on the C|R|E sheet, the full code is much much longer since I am using so many command buttons.
View 1 Replies
ADVERTISEMENT
Dec 31, 2013
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
View 3 Replies
View Related
May 2, 2009
I have 3 workbooks in XLStart. I want to change the workbook that gets the focus after they are all open. The workbook i want to have focus is DG_Tourny.xls
View 2 Replies
View Related
Feb 27, 2008
I'm trying to hide columns based on information in another column. If that column contains "A" or "B", hide columns "U" through "W". If it contains "A", "B", "C", or "D", hide columns "V" through "W". Etc, etc., so on and so forth...
I've snipped the code I found earlier and modified it to (what I thought) was correct for my application, but it doesn't seem to do anything. (btw, should I get an error if it doesn't run correctly?)
Here is the code as modified:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "P:P" Then
If LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Then
Columns("U:W").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D" Then
View 9 Replies
View Related
Jun 7, 2007
In the sheet I'm currently working on I have a column (column C) that uses a validation so that users can select from a number of call types (data tracking in a call center). I allowed for an "Other" option, with a free-form column (column D) dedicated to the "other" reason in case the list doesn't cover the type, but I'm running into a problem where people use the other column to describe call types already listed. Is there anyway to lock the Other column (column D) unless Other is selected in the drop down in the Call Type column (column C)? The sheet I'm working with contains confidential data so I can't post it, but if this isn't clear enough I can draw up a mock sheet.
View 5 Replies
View Related
Jan 29, 2014
Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?
What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.
Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul
[code]......
So for example:
row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July
row3: Feb is latest imput and "next" also 2. Result should be Apr
row8:May is latest input, "next" is 6. Res should be Nov.
Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.
View 9 Replies
View Related
Jan 16, 2014
My formula in sheet "A" in cell D7 is this
=IF(Activity_Log!C2="BH",Activity_Log!F2,"").
What I would like to do with it is fill in row D7 to AH7 with this formula without changing the row reference in the IF function, only the column reference.
On the Activity_Log worksheet, my data is listed from cell C2:C32, which is selected from a drop down list, and F2:F32, which contains the formula =(E2-D2)*24.
I've attached a sample : Sample_MonthlyReport.xlsx
View 9 Replies
View Related
Feb 4, 2014
I have a sheet which I need to arrange and it looks like -
Column A
Column B
Column C
Column D
[Code]...
the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"
Output should be -
Column A
Column B
Column C
[Code]....
so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.
View 9 Replies
View Related
Sep 25, 2013
I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)
I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.
View 1 Replies
View Related
Jun 10, 2013
I have a user form that has one combo box on it that right now references one column of data.
Now I am being asked to have three columns of data and the combo box to show one of the three when a certain criteria is met.
I believe it would be easiest to have an additional combox with a change event when the box is populated with "whatever" in the field
So S:3 to S:5 have A, B, C
And EC:1-EC:59, ED:1-ED:59, EE:1-EE:59 contain the data that should show when S:3, S:4, or S:5 is selected.
If S:3 is selected then the list in EC:1 - EC59 would show and so on.
View 2 Replies
View Related
Jul 28, 2006
way to take a long list of the following data and change it from rows to columns?
First name Last name
Address
City, State Zip code
First name Last name
Address
City, State Zip code
That is how the data is currently displayed, spaces inbetween as shown but, long list of names and addresses. There is also an occasional 4th line usually for an address. My instructions are to put data in the following format:
First name Last name,address,city,state,zip code,
View 9 Replies
View Related
Jan 12, 2009
Based on the attached (much simplified) spreadsheet, I want the values in the green cells on the "Month" page (B6:B10) to vary depending on the value in the yellow cell (B4), and for these cells to to look up the relevant column in the "Full Year" page - i.e. if B4 (on "Month") = Jan, look up C6:C10 in "Full Year"; if B4 = Feb, look up D6:D10, and so on. I know I can nest IF functions in order to achieve this, but could be a bit messy. I'm sure there must be a simpler formula within Excel ... just don't know what it's called!
View 3 Replies
View Related
Jun 20, 2007
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
for i=23, I need the result to be of the form
=vlookup(a1,table,23,false)
View 7 Replies
View Related
Jan 31, 2013
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
View 2 Replies
View Related
Jun 27, 2013
Is it possible to format a row of cells (B7:KX7) depending on the value in column (G7:G1000) changing?
For example column G might have the following value entered into them.
A
A
A
B
B
B
C
C
C
So all the rows containing the letter A would be formatted one colour, then it would notice it changed to B and format all the rows with the letter B in, then C etc... The letters would always appear in blocks of 10 or more (representing students in a group) so it wouldn't go A,B,A,B,C,D,E
I am not bothered about the letter being tied to a certain colour, it just needs to change when the letter changes. I'd have no more than 10 different variables in this column, meaning no more than 10 different colours to format. Formatting would be black text with different coloured backgrounds.
View 1 Replies
View Related
Oct 30, 2007
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View 9 Replies
View Related
Aug 16, 2013
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name
Dept
Abbruzzese,James L
188100
[Code] ......
Output
Name
Dept
Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708
Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435
View 2 Replies
View Related
Jun 24, 2009
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
View 9 Replies
View Related
Aug 25, 2009
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 2 Replies
View Related
Jan 28, 2013
Changing the data based on a drop down selection. My drop down list is based on the months I need the data (percentages for each person) to change depending on which month is chosen. So If I select Jan, the percentage fills in for each person. I've attached an example.
View 3 Replies
View Related
Sep 5, 2006
I'm facing a charting problem and i can not find any solution with a search here, anyway this is the problem: When I choose a week I would like to see the results of the 5 previous week also.
View 4 Replies
View Related
Jan 22, 2010
I have a worksheet called "zz_Exchange" that contains the exchange rate for my company for each month of the financial year. I am using this info in a summary page that uses the data for multiple calculations in the workbook.As we pass each month I would like to move the focus of a cell in the summary sheet to the relevant month in "zz_Exchange"
The column names are "currency_code" (USA Euro etc),
"currency_year" (09, 10)
"period" (1-12)
"exchange_rate" (this is where the data is held)
View 4 Replies
View Related
May 13, 2013
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies
View Related
Apr 4, 2007
I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
View 9 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
Jul 31, 2012
I am trying to create a worksheet_change event that will prevent a user from changing or deleting data in the totals column of my worksheet yet allow them to insert rows and delete rows on the worksheet. I have a few issues I can't get resolved. First, if I delete a row and then click the undo message, I get my error message about trying to change the totals. Second, I cannot copy and insert copied rows. I can only insert blank rows. Here's my code and data.
Private Sub Worksheet_Change(ByVal Target As Range)
Static lngRow As Long
Dim iStrtRow As Integer, iEndRow As Integer, rRowMrkr As Range
[Code]....
Defined range names: "Header" in cell A1 and "Totals" in cell A12
View 1 Replies
View Related
Jun 30, 2014
I have a excelsheet that looks like this:
Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept
Health Services
Internal Services
Public Works
Social Services
Los Angeles
3
12
New York
8
22
100
7
Chicago
15
56
4
View 8 Replies
View Related
Mar 5, 2013
I have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
View 2 Replies
View Related
Jul 10, 2009
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
View 2 Replies
View Related
Oct 13, 2011
I have a problem when i need to save an excel workbook. I have 7 sheets, and in one of these (sheet3) i make 3 data validating list, based an if formula.
in cell C9 (sheet3) i make a data validation list where i tiped =IF($D$8="Turism",Norma_poluare,Norma_poluare2)
in cell C10 (sheet3) i make a data validation list where i tiped
=IF($D$8="Turism",Cilindree,$A$1)
in cell C11 (sheet3) i make a data validation list where i tiped
=IF($D$8="Turism",Emisii_CO2,$A$1)
The "Norma_poluare","Norma_poluare2" tabels are in sheet 4 and the "Cilindree","Emisii_CO2" tabels are in sheet 5.
When i need to save the excel document, i must have open sheet 3, othewise excel returns me an error "One or more cells in this workbook contains data validation rules which refer to values on other worksheets.These data validation will not be saved". So I thought that before saving date, automatically to shift focul to sheet3, and after these excel to save the data. VB code for these problem.
View 4 Replies
View Related
