I am working with two tables in one sheet. The first table requires seven columns. Under this table, i want to have another table that has 14 columns right under it so that their widths are the same. Is this possible? It would be like having two sheets in one.
View 2 Replies
For the past 4 months I have been desperate to find a way to split my sheet so that I can determine the column widths above and below that point independently.
For example:
small width from a1-b1
but
really large width from a2-b2
Is there a way to do that?
Is it possible to create independent filters. I have data for five departments and sales for different quarters of the years. These sales for the different quarters are independent. But If I select Q1 for first department and Q2 for second department it do not give me the result that I seek i.e. sales for first department for Q1 and Sales for second department for Q2.
View 9 Replies View RelatedIf the issue is not immediately clear:
If I select, for example, columns B:E and group (Data -> group and outline -> group). These can be hidden/unhidden easily with the line and + button created in the process. A similar set of columns (e.g. P:S) will behave in exactly the same way. If I have grouped columns B:E however and then try to group F:H, I end up with one large group spanning B:H.
If it is possible to create these 'separate' or 'independent' adjacent groups, I'd love to know how; if not, I'd be interested to know if anyone else has found this irritating
How can I make the files path independent. Lets say I have 6 workbooks, one of them having macro that connects all the six books. While working on my pc, I entered the file locations by myself. The macro runs fine.
Now I have to send the files to someone and would not like them to add their download directory at most places in the code. I want that after downloading, they have to only open the main file and press the button to run macro. How should I do that.
Right now am using this approach:-
sub main{
str 1file;
set 1file = Workbooks.Open ("c:/downloads...../1stfile.xls")
and similar code for calling other files.
I have two spreadsheets which carry the same information in three cells. Instead of just placing a link, =B2 etc., I would like to have the cells linked to each other through code.
For the one sheet I can use this with no problem, but as soon as I place this worksheet change event in the other sheet but reverse the cells references Excel gets unhappy.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B2, M2, N2")
[Code]....
Any proper way to update a cell no matter which spreadsheet the change takes place?
Sheet1 >> change is made to cell B2 and that same value is applied to Sheet cell C4.
I need a macro that can autofill irrespective of where the source cells are. Like, this is what I want to do:
Select two cells containing a descending order such as 50, 40. Hit the hotkey and the macro should autofill the same way as double clicking the bottom right corner of the selection.
The problem is that the two source cells can be anywhere in the sheet, and autofill range isn't fixed. Essentially, I just want a keyboard shortcut for Excel's own Autofill function.
I want this macro to be rid of the mouse when I'm working on excel (it really slows me down to use the mouse every time I want to autofill).
In the attached spread sheet, i have two set of data, for two different months.Columns A to D have the value for the first month and columns F to I have the values for the second month. In the first set of Values i have the dollar value in the column "D" and in the second set of values i have the dollar value in column "F".
I want a macro, to compare these two cells and arrange the data accordingly. If both the values are the same, then both the lines corresponding to the values can remain in the same row. If one value is greater that other, then the set of values which have the lower dollar value should come to the next row. I have attachec the sample data. The lines highlighted in green is the raw data and the one in yellow is the finished data.
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.
I am developing a Excel gantt chat i have chosen to display the bars by the lenght of the cell because the data is also most real time so as the time reduces so will the bars however the problem comes when there is tasks being done under one above it as i cant have the cells with different widths
What would be the best way to get around it as i want to keep the almost and the appearance of the real time nature of the cell width equal to the value.
The following code works fine if the data is sorted from oldest to newest. I need for it to work when the data is sorted from newest to oldest. For this to work, I would need a change that works something like this: "total1 = total1 + close_(a, 1) * a" would be "total1 = total1 + close_(a, 1) * b" with "b" incremented in the opposite direction of "a". I code very infrequently & have tried a lot of different things, searched the Internet, etc. all to no avail.
For instance, with "close" data in column "I", the data sorted as follows works with the formula
"=WMA(OFFSET(I2,0,0,9,1) )" to correctly return "791.50", but the opposite sort returns the wrong number.
The correct calculation is: (807.19*9+798.38*8+793.81*7+793.06*6+776.28*5+769.48*4+773.2*3+789.01*2+793.87*1)/(9+8+7+6+5+4+3+2+1).
DateClose
11/12/2012793.87
11/13/2012789.01
11/14/2012773.2
11/15/2012769.48
11/16/2012776.28
11/19/2012793.06
11/20/2012793.81
11/21/2012798.38
11/23/2012807.18
Code:
Public Function WMA(close_)
total1 = 0
n = WorksheetFunction.Count(close_)
divisor = (n * (n + 1)) / 2
[Code] ..........
I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.
The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.
Finish
Zone 1
Zone 1
Zone 2
[Code]...
I extract text into a new column using =Left(). I want to make that column permanent. That is, I want to delete the source column but keep me extracted column. How?
View 2 Replies View RelatedI have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedFrom every sheet I want to create the same type of graph, so a macro could be very useful.
First I recorded a macro. The worked fine, but it could only be used by only one worksheet. Every time I used the macro it returned to the original worksheet where I created the macro.
With some searching I found out how to change the code so that the macro could be used for the active sheet.
But now I get stuck with the following error: "compile error: invalid qualification" (translated from Dutch).
With error tracking I get stuck at the first line "Sub grafiek()"
I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file
I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe A 1001
Joe A 1002
Joe A 1003
Jill B 1001
Jill B 1002
Jill B 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range
[Code]....
I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.
I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.
how to merge data automatically in one sheet (master sheet) from specific sheet with different name sheet but same format?
Adding, deleting or editing should be automatic update to master sheet.
For example:
Sheet name:
Sheet 1: Salesman 1 - with data range A10:F310 (as his data file)
Sheet 2: Salesman 2 - with data range A10:F310 (as his data file)
Sheet 3: Salesman 3 - with data range A10:F310 (as his data file)
Sheet 4: Salesman 4 - with data range A10:F310 (as his data file)
Sheet 5: Salesman 5 - with data range A10:F310 (as his data file)
Sheet 6: Salesman 6 - with data range A10:F310 (as his data file)
Sheet 7: All Salesman - - with prepared data range A11:F2000 (as master data file/record for 6 salesman)
Column Name as a Header:
A10: Customer Number
B10: Customer Name
C10: Salesman ID
D10: Promotion Type
E10: Promo Item
F10: Free Item
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
I have a sheet1 with following column name Ab,Bb,Cb,Db,Eb,Fb and sheet two contains Ab,Xc,Eb,Sv,Db,Fb,Gm,Cb,Hb these headings are on 7 line of both excel sheet.
Now I want to check each cell under column name Ab in Sheet1 and compare it with all the cells under column name Ab in Sheet2. If both matches then update Cb,Db,Eb,Fb of Sheet1 to the corresponding columns in sheet2 for that column name.
I need to fill in the third column on sheet 2 from the 3rd column in sheet 1. do I need an IF statement combined with vlookup??
sheet 1
mark l XYZ l 45
katy l ABC l 3
mark l ABC l 6
katy l XYZ l 45
mark l DEFl 4
sheet 2:
mark l XYZ l
katy l ABC l
mark l ABC l
katy l XYZ l
mark l DEF l
I have a worksheet, "District", that has names of team members from A2:A (The number of team members will vary, so I would need the macro to stop when the list ends). I need these to be distributed to column A on another sheet, "Input", from A11 down until it hits a row that has a blank cell in column F. I've tried a couple of things, but just can't seem to get it to work.
View 14 Replies View RelatedTrying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View RelatedI'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
I just making a good format for the main sheet of the excel but when I insert another sheet the format change to default (colmun size for exemple).
I want to make the format for all the sheet os the excel.
I'm looking for a code that automatically changes the format of any pasted in data to Match destination formatting. Not getting the results I want from protecting the sheet.
View 4 Replies View RelatedIs there a way to force the inputs of a pop-up window to be a certain format? I have a macro that copies and renames a sheet based upon user inputs....but it only uses a pop-up window with dialogue only explaining that that is how they SHOULD name the sheet (DD-MM-YY) without using restricted characters. Moreover, it would be great if it would check the name against names already in the workbook to prevent an error for duplicate sheet names or force it to be whatever DD-MM-YY they input as DD-MM-YY(2) etc.
Sub CopyandRenameSummary()
Application. ScreenUpdating = False
'Copies and Renames Summary
Sheets("Summary").Select
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
On Error Goto ErrorHandler
Dim Myinput
Myinput = InputBox("Enter the Count Date in the format DD-MM-YY (INCLUDING DASHES). You cannot use any of the following symbols: : / ? * [ ]. You cannot prepare more than one count sheet per date input.")
I have I workbook that contains 2 sheets. The first sheet "Business Objects" is the master list. The second sheet "Gene" contains similar data but is incomplete. There is a unique identifer for both sheets and that is item ID. what I would like to do is look at the Item ID column on sheet1 compare it to SS# on sheet two and copy any rows from sheet1 to the end of the sheet in sheet 2. I have found in my searches on this forum a bit of code that identifies the ones in sheet1 that are not in Sheet2 and highlights them red(which is not neccessary for me, but I am struggling to figure out how to take that and paste it to sheet2.
View 3 Replies View Related
