I have Given Name for Data Column as Below
[Data_Table=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A$2:$A$28),COUNTA(Data!$1:$1))]
Now If I delete Raw 2 System is giving Error #Ref!
I may have to delete Raw-2 frequently
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.
Small clarification:
- 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 a big system of data, me not knowing better i have it such that the categories is listed in the start of the row, and new data for periods are listed in the columns following. when i mark the data and click table, it puts the scroll down thing on top of the columns, which is of no use to me at all, i need them at the start of the row. is this possible, or do i need to reorganize all the data, and VBA code used to sort and collect it ?
View 7 Replies View RelatedI export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted
I have a blank template spreadsheet that is copied every time I need to work on some data exports from another system (the data needs to be kept, hence the copying of the original blank template first).
I have set up conditional formatting using;
=MATCH($A1,range,0)
This formula is always in the entire column of row A in Worksheet1 (for the sake of argument)
Problem is in Worksheet2 where the range it's using to compare is always a different number of rows deep...it can be 70, it can be 2000. I always call the range 'interest'.
Is there anyway (in the blank template) of setting column A as a dynamic range called 'interest' so that after data import it automatically sets the range at the desired size?
I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.
View 1 Replies View RelatedI want to combine all data in a single graphic. It must be like a coordinate system.
The file is in that address: [URL] .......
I have following macro recorded to extract data from a mainframe system
open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
The requirement here is run the above macro for the next 30 days automatically, for example
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro
i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.
What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....
I defined few name in manager but that are not working. I Defined names with multiple sheets with same Column.
See the attachment : Demo.xlsx
Looks like this question was asked in 2009 with no replies. Is there a way to export your list of table names and refers to formulas from Name Manager?
I would like to bulk manipulate some of them in a spreadsheet.
I'd like to name a range such as
View 3 Replies View RelatedMy 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:
[Code] ......
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:
[Code] .....
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 have a workbook that I use to calculate sales by agents. In the workbook I have the following sheets:
Monthly_Report - Where the results are return to the from end user
team_ref_sheet - Where team / manager details are added / updated
data_sheet - Where my raw data is added
On my team_ref_sheet I have created 4 columns that have been renamed using name manager as follows:
team_manager
agent_names
dealer_code
employee_number
On my "Monthly_Report" sheet I have created a table where I want m data to be displayed to the front end user. The table starts in column C8.
In column D10 through to all the other rows I would like my sheet to reference to my "team_ref_sheet" and return all agent names in the range, up until it reaches a blank row.
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 been trying to save myself alot of time by using the name manager and an external link to another workbook without success.
The real directory name is very long. There are 200+ different markets like forex1
and 25 different "Locations" that refer to the same things
Name Manager name Value
-----------------------------------------------------------------------
Directory ="C:DemoVP["
Forex1 ="Australian Dollar Japanese Yen Cash.xls"
Location1 ="]Sheet1'!A2"
Now I am trying to add these 3 thing together to get an external link without success with the following =Directory&Forex1&Location1
I get this below, but no external link 'C:DemoVP[Australian Dollar Japanese Yen Cash.xls]Sheet1'!A2
I have a workbook that has a saved range. I created it with the Name Manager. See below:
[Code]....
When it looks like that the VBA works great and creates my pivots. But after I run my first macro to mess format my source data the range ends up looking like below:
[Code] .......
It is for some reason changing the "A" to "Y".
How I can get it to stay with the original data?
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?
View 2 Replies View RelatedIs there a way to create a PIM in excel? Know of any? I want each sheet to be a seperate progress manager.
View 9 Replies View Relatedcode for VBA Excel that
Starts Processes
End Processes - Already got one but not that good.
View Processes
I am running into a problem with my code. It sounds almost exactly like a previously posted problem 2 Copies of The Same Workbook Open in Task Manager but I never saw a fix.
I am also getting two copies of a workbook in task manager. It appears when I do a Workbooks.Open filename.
how do I edit external links automatically in name manager?
I got external links like
='C:folder1[file1.xls]SHEET1'!$CM$15
It is easy to replase manually few links, but what about few hundreds...
How do I replace from
='C:folder1[file1.xls]SHEET1'!
to
='SHEET1'!
automatically ?
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.
View 2 Replies View RelatedHow do I calculate the days between submissions by manager in the below example?
Customer Submit DateManager
2361047/2/2013 Jackson
2361667/19/2013Andrews
2480589/30/2013Smith
2480589/30/2013Smith
803139/30/2013Thompson
[Code]...
i need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....
View 9 Replies View RelatedI 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:
Option Explicit
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
[Code] .......