I have a worksheet (Data Lookup) that initially I just required row 1 to be frozen. Now my worksheet would be much easier to read if I was able to freeze row 115 and row 263 when they reached the top of the sheet respectively.
e.g. Row 1 frozen, when I scroll down the sheet and row 115 hits row 1 that would also be frozen and the same with row 263 when it comes into contact with row 115.
I have looked around and cant find a solution and to be honest I'm not sure if it is even possible.
I'm using a fairly large spreadsheet to put all the cost and benefits of a large area development phased over different years and then calculate the NPV of the total project. The costs/benefits are on separate sheets and are divided into categories with headers. Every category has a summation row as last row.
Because the project is divided into subprojects I created a column which has a dropdown box (using the Validate function) in which I can attribute that cost/benefit to a certain subproject. Using IF functions and another dropdownbox on my Overview page I can get a insight in the total cost and benefits of the subproject I select in the dropdown box.
So far so good of course, but what I really want is not only to be able to get the Overview page per subproject, but also the Cost and Benefits pages. I was thinking about putting a filter on the column which has the dropdown boxes with the subproject number in them, but when I select a number I want all the headers and "summation rows" of all the categories to freeze/stay in sight, because otherwise the output of the filter is useless (for printing and evaluating) ...
I have a situation which I hope is fixable. I have frozen panes above and to the left of cell F5, therefore all columns to the right of column E scroll left and right and all rows below row 4 scroll up and down as required. My problem is that I also need the whole top row to remain static all the time.
I have a spreadsheet of a couple hundred products, their sales over the last 18 months, and then each product's 18 month average monthly sales. What I would like to do is format them so if any of the months' sales are below their respective average, that cell/month with be highlighted. Excel told me that I cannot do this for multiple rows at a time, and I don't want to go row by row for this many products. Attached is a sample file of the first row.
Is it possible to alternate how a condition works over many rows? Say column C has work order numbers eg. C2:C7 shows WO1234, C8:C15 shows WO1235, C16:C25 shows WO1236 and C26:C30 shows WO1237. What I would like to do is have rows 2:7 red, rows 8:15 blue, row 16:25 red, rows 26:30 blue and repeat to end (the range would be column A to column N. Ive used =MOD(ROW(),2) for every second row but am not sure how to get it to look at many rows and alternate as needed.
I have an Excel worksheet with about 300 rows and 8 columns per row. I want to shade the highest number and the lowest number for each row. I assume the built in conditional formatting is the best place to start, but I don't really know. Sample worksheet attached.
I have data in cells A2 - N2 and cell O2 is currently blank. I am trying to set conditional formatting where if O2 has a value entered into it, it will conditional format A2 - N2 and highlight the entire row red.
I tried a formula like =O2"" but that does not work. Also tried a few other things but no luck.
Also, once the formula is set, what is the best way to apply the conditional formatting to rows 3 - 30. I tried the format painter tool but that seems to want to extend the formatting range from rows 3-30 and not just per row.
I'm looking for a Conditional Formatting formula that will check two columns before highlighting the duplicate rows. I need it to be conditional formatting because I know nothing about writing macros or vba (what-ever that is?). Data is entered into Columns A, B, and C. I need to check both column A and C before it highlights the duplicates, based on those two columns. (The format only unique or duplicate values checks only one column.) I have attached an example, but this is just an example, as I have hundreds of lines to go through on the original. (For this example, Row 2 and Row 7 are the duplicates I need highlighted.)
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:
[url]
The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...
I am looking to freeze row 18 in an excel sheet, however in doing so this really limits the amount of rows below row 18 that can be viewed and scrolled through.
I would like to know if there is any way that you could freeze row 18 and when you scroll down on the worksheet, you can scroll down past rows 1-17 and when row 18 reaches the top row it would lock there? And you would then be able to continue scrolling down the worksheet beyond row 18..
I am having problems with the VBA Editor freezing at the most inconvenient of times when writing code and then clicking to the Sheet I'm working on, it just freezes up the whole of Excel and it has to be shut down and opened up again.
It seems like a memory problem at first, but there is sufficient memory ( 768Mgs, OS is XP Pro, Excel 2000, 9.02).
The work is not lost becuase I'm able to Save even at PC Re-boot, fortunately.
I've tested on 2 different PC's and the same happens. The only way to avoid the freeze it seems, is before I switch to view a worksheet, is to Save then click off VBA Editor and re-open. When doing so, another symptom is it seems a huge amount of memory is freed up, ( pardon the terminology)
There is also a message that pops up when re-opening that same workbook that there are Formulas Linked to another workbook, but unable to find any after tediously searching every worksheet by using "Find, Look in Formula" there is nothing found, yet.
This freezing may happen twice a minute whilst pointing the mouse cursor from any VBA Edit window or worksheet and it's getting to a point of repetitiously having to Save, click off VBA Editor, and Click back on again each time I need to look at a respective Worksheet.
I would like to have the column titles stay so I have frozen row 1. But I would also like to freeze row 200 so I can keep up with the running totals at the bottom of the form as I fill in information. Is it possible to freeze a pain at the top and the bottom?
I need is for a block of cells (A1:I4) to be visible at all times. I've frozen the sheet so that the top four rows are always visible, but unfortunately the sheet is quite large, horizontally-speaking, so that when users scroll horizontally, important stuff scrolls off the screen.
I have a table that is frozen in b5 meaning that the first column ("A") and rows 1-4 are frozen. The table is large with ever expanding rows. What I want to do is, a good few rows down (say row 300), freeze the row at the bottom of my view and use that row as a mean average row. Therefore allowing me to enter much more data above, but constantly be able to see the averages of the data I am adding.
I am using Row 3 as a my template to insert a row for every new line of data i enter. I have a macro that copies the format and formula on this template row to insert at the end every time. The problem is on ROW 3 Cell F i am using a Today() function and everytime i insert a row the date is populated. But the next day the date automatically update. In my search on this forum, i found this macro to supposedly lock in the dates, but i doesn't work.
VBA: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range("F2:F10000"), Target) Is Nothing Then storage = Target.Value Target.Value = Format(storage, "dd/mm/yy") End If Application.EnableEvents = True End Sub
How can I freeze a sheet's tab location so it doesn't roll off of the screen? I have a workbook with 30 worksheets all hyperlinked off of the 1st sheet which is the INDEX sheet. The INDEX sheet only exists to make it easier for users to go directly to their desired sheet without tabbing horizontally through many sheet names. However when users use the bottom left arrow buttons to tab through the worksheets, the leftmost INDEX worksheet tab scrolls off of their screen. I realize that they can right click on the left/right tabbing arrows to see a list of all worksheet names, but it would be nice if the INDEX worksheet tab never rolled off of the horizontal tab scroll bar.
I have a progress bar on a form......it is there for no reason other than for me to practise such things...the code came off these forums and the bar is powered by a random number...
now my problem is the moment it start running i cannot do anything on the form....i would like my bar to do it's thing but still be able to click on the cancel button to get out of the form any help on this would be fantastic
Private Sub UserForm_Activate() Dim dTime As Date Dim i As Integer
Do Until i = 100 For i = 1 To 100 Step 100 / 8 dTime = Now + TimeValue("0:00:01") Application.Wait TimeValue(dTime) ' ProgressBar1.Value = RandNumGen(1, 98, 1) 'AboutScreen.Repaint Next i 'AboutScreen.Repaint Loop
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
I know I can freeze panes eithe across a column or row but is it possibleto do both at the same time so that I can have a header row and a few columns on the left of the screen frozen?
click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?
what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell
I have a multipage userform with about 7 pages. When page 5 is activated, it caused excel to completely lock up. This does not happen everytime but randomly, and I can't seem to figure out what is causing this.
Here is the code - If Me.MultiPage1.Value = 5 Then Call LaborCosts End If
Sub LaborCosts()
If Not Range("A46").Text = "" Then frmpricingtemplate.Label29.Visible = True frmpricingtemplate.TextBox46.Visible = True frmpricingtemplate.Label29.Caption = Range("A46").Text Else
