My CF formula looks at 2 columns A and D in A there's arrival dates, in D departure dates
I have a whole calendar with this CF rule that highlights any date that falls between 2 dates with this formula:
Ya, Yaa, Yb, Ybb etc are just the names of the cells that the dates are in. It works great, but because I want to use the same calendar in the same sheet multiple times, I can't use the names. And so I just removed the names from the names manager and changed the formula accordingly to the normal cell names as such:
Problem is that when I remove the names (ya, yaa etc) from the names manager, the CF doesn't work anymore. The formula is not automatically updated by excel, and so I manually changed the CF formula to contain the normal cell names but this also doesn't work.
The last thing I want is to rename all the individual name-boxes to be able to use multiple calendars in my sheets.
(I want to use approx 100 calendars with CF on 1 sheet, and there are 48 name-boxes in the calendar that are used in the CF formula)
I've attached an example of the calendar for review : cf calendar.xlsx
I am using name manager to identify variables in formulas in order that I can be consistent with calculations. The problem is each line item have different variable amounts. In other words if I am calculating cubic yards of concrete I used (Length*Width*Depth)/27. Line Item number one is (100*3*2)/27 based on my dimensions given. Line Item two may be (50*2*1)/27 based on different dimensions. The Name Manager has Length, Depth, etc. as absolute references. What I am trying to do is copy each formula down the sheet but each time I do the new line item always calculates from the reference cell in absolute mode. Is there a way to copy this formula all the way down the sheet so it will pick up the different dimensions keyed in each time using the Namae Manager Variables set up?
Line 1 (100 x 3 x 2)/27 = 22.22 Cubic Yards Line 2 (50 x 3 x 1)/27 = 5.55 Cubic Yards
I have made a table with many dynamic formulas that I want to import into the Name Manager. It has the names in Column A and the "Refers to" formulas in Column B. Unfortunately, when highlighting the cells and using the "Create from Selection" feature of Excel 2010 it puts the Column B cell address in the "Refers to" field, not the actual formula. Is there another way to get these formulas into the "Refers to" field without typing them all out or coping and pasting them individually?
I'm trying to copy images based on an adjacent cell value using name manager but it does something strange to the image... On the attached example I want cell E5 to be the flag of the country in F5 by using an index/match formula in name manager but the flag seems to shrink and break up when the country changes and the original flag is changed.
I have a set of Raw data which I'd like to filter based on Manager. The Manager is not defined in the Raw Data, only the products he's responsible for. One manager can be responsible for multiple products.
I have attached an example which shows what I have right now.
- RawData contains the daily gathered info
- A list is created based on Product under the Master tab
- A link on the RawData is created to avoid the "You can only copy data to the Active Sheet" error.
- A Macro is created to filter the data, and assigned to the button on the Filter tab
Currently you can select per product and show that data.
The goal is to select a Manager and then show the data he's responsible for (Manager Mike will show all lines containing products "Prod X and Prod Y")
While we're on the filtering, what is needed in the Macro code to only display active fields (ie. not show Unused)?
In the attched sheet I have a list of employee ID's in column A and the Employee's Line manager ID in Column B. In Column C I need the Line Manager at the top of the pile so to speak. These line managers are listed in column J (J2:J6)
At the moment I have been writing formulas accross 11 columns (there are 11 possible levels) to check the line managers ID in Column A and see if their line manager is in the top manager list, I do this formula for all 11 columns until the line manager in the list is found. If the Line Manager is in the list I simply repeat it for the next columns. The end result is that in the 11th column all employees will have one of these Line Managers from the Top List in their row.
Is this possible to do through VBA? I have thought about how I could do this through VBA but I have just hit a brick wall.
I'm not asking for someone to do all of this for me but if someone could give me a couple of hints around how to look up a value in a list through VBA and if it is even possible to repeat that process per line until the match is found, that would be great.
I have had some problems closing down a workbook that I have. During a save and close.
A duplicate file appears and causes considerable time delay...sometimes even causes a windows crash. I can't find the source of the problem, but I have noticed that in the Windows Task Manager, the file is there as well, yet not visible on the screen.
I am working on building a better report manager using named ranges
1.) Loop through the workbook and find all named ranges Output the named ranges to a worksheet
2.) Read the values from the worksheet and print each one
Seem simple enough and is really just first step Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed
But I'm getting ahead of myself. First is to print a named range
My code is below Error msg:
Run Time Error 91 Object variable or With Block Variable Not Set
Debug points here:
Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value
1. Loop through all named ranges and output to worksheet. This part works fine:
Sub GetNamedRange() Dim nName As Name Dim wb As Workbook Dim wsPrnt As Worksheet Dim Row As Long
With Application . ScreenUpdating = False .DisplayAlerts = False .Calculation = xlCalculationManual End With
Some of my excel files, which are not massive seem to crash my computer when saving or recalulating or even using some filters. The files show as not responding for a couple of mins then come back to life after completing the requested action. Often this is not a complicate task that can cause this.
When this is happening the CPU usage shown on task manager is 99 for the excel file.
This doesn't happen on any other program, I can use word and powerpoint etc with no issues. Its just excel that my computer has a problem with.
This is really frustrating as I can end up twiddling my thumbs for 5 mins unable to do anything, when I just want the file to save or re calculate. I already use manual calculation on anything file more than 500 lines to work with the situation.
I've been working on a userform that I've been adapting from a workbook I had set up to record real time stats. Its pretty much where i want it to be now and I am trying to focus on using the stats created by the individual users to populate team stats. There will be many users using this and many different teams. so i imagine, it wouldn't take too long for just one workbook to fill up and cause problems. Would i be correct in thinking that an IF statement in my current code (that's set up to send to one master sheet at the moment) that will pick out the manager/users name from the userform and send the data to the respective teams workbook, would solve this problem. Or am i over thinking this and is there a more simple option.
Code: Private Sub UpDate_Raw_Click() Dim SourceRange As Range Dim DestRange As Range Dim DestWB As Workbook Dim DestSh As Worksheet Dim Lr As Long
I've looked through this wonderful sight and haven't found one close to my project. I am a bit rusty on VB programming. I am working on workbook that contains multiple worksheets. Each worksheet is hidden and is accessible with a login and password so that only a specific user can access. My question is: How do I modify this so that the manager can open the workbook and view ALL worksheets without having to enter the login name and password for all of his users? Any help would be great. Thank you!!
I have a sheet in which in Coloum A I have manager names, then in Colum B I have list of Employees reporting to that manager, then in Column C, I have total login time of each employee which should be => 8 hours. In a new sheet, I want to get the count of employees next to the each manager's name who login time is less then 8 hours.
I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls. When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.
Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using
I currently have a code to save my sheet as a txt file with specific formatting in the filename. I would ideally like to just export the file to txt rather than save as while keeping my specific format of the filename...that way it doesn't actually save the excel sheet itself as that filename. I currently have a workaround for it, but I think this would be easier/better. Hopefully that made a little sense. Here is the current code I am working with.
Dim MyPath As String Dim F As String
MyPath = ActiveWorkbook.Path & "" F = Sheets("Import Data").Range("M12").Value
ThisWorkbook.SaveAs Filename:=MyPath & F & ".lvy", FileFormat:= _ xlText, CreateBackup:=False
I have to match the name of colleuges with the birthdate, id number and A B C D category. Now I have to excel tables one with the names participated in company programs one with all the names in the company and the above listed information.
I want to know how can the excel automatically match these datas without copy pasting it from one to another. I mean to search for the name in the second table and if its matching to fill the birthdate id and ABCD category automatically.