I would like to create a master workbook that can moderate other workbooks referenced by it. So something that is set up like a regular template, yet when I make changes to the master workbook, all documents that reference it will make the same change.
The only possible way I can think of doing this is linking, but 90% of the document will be all linked up and knowing how fragile links are I was hoping there was another way to do this.
I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}. My general module contains the following
View 4 Replies View RelatedI'm attempt to automatically validate QTP spreadsheets with an EXCEL VBA macro. All is going well but I now want to look smarter. Is there a method to globally remove all comments from a worksheet?: D What I actually want to do is add comments to cells which fail validation but some of these cells may already contain comments. If they do contain comments then I want to overwrite with my comments. I thought the easiest way would be to delete all comments at the start of macro but I am all ears to any other approach.
View 4 Replies View RelatedI have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09
If the sum of a Payment Due (Column C) minus the Amount Actually Paid (Column D) is results in a number greater than zero (Column E), no problem.
For instance:
C22: D22: E22:
200 150 =SUM(C22-D22)
E22 shows a result of: 50. That's great, exactly what I want it to do. However, if I paid more than what is due (D22 is greater than C22), I want the result in E22 to show as "0" instead of a negative number.
For instance:
C22: D22: E22:..............
I have some numbers in column G and column I. The formula I'm wanting to write in lamens terms is:
If the number in I4 is not within 10% of the number in G4 then I want the number in I4 to be red otherwise I want it to stay black.
Is there any simple way to build a calendar in Excel? Ideally, all events will be listed on one sheet by date and the calendar will display the events on their respective dates. Is this possible?
View 2 Replies View RelatedI'm searching for a way to make a shiftcalender in excel. The whole year i'm having troubles with my planning to do it in a system of my boss and i want to change this for next year. We need to have always a minimum of 2 people on duty and when someone calls in sick i want to see in for instance a collor that the planning is corrupt and that i have to call someone else to do the shift.
I've looked several up on the net and there are many types of shiftcalenders but not the one i have in mind.
What it needs to do is;
- There are 5 teams with 3 people in it, so 15 names,
- There are 3 shifts from 06:30 to 15:00(dayshift), 14:30 to 23:00(eveningshift) and 22:30 to 7:00(nightshift)
It's a 2-2-2 type of shift, that means 2 day-, 2 evening- and 2 nightshift and after the cyclus 4 days free which the first day a day to sleep.
I would like to proceed with the last date of the existing calender and that the sheet automatically fills in the rest.
I am created Financial Worksheets. . . ex. Balance Sheet, Income Statement, Cash Flows, Notes, Forecasted Sales Etc. and I want to make a GUI in-order to navigate easily. I already included Hyper Links to it for some detailed annexes.
View 4 Replies View RelatedI have the following code to create a 3D line graph:
View 2 Replies View RelatedI have this code ...
View 13 Replies View RelatedI am creating a form and certain fields are required. Can I force the user
to input some value before they can save?
Total newbie trying to use excel for work here. I have two problems that both need the use of "is NOT in another column". 1. I need to count the number of instances in a column that do NOT have the word "fly" in column G. The current formula is...
View 2 Replies View RelatedI just need to know that is it possible in excel that we could make box with length, width, height dimensions?
For example: we just enter 100mm is length, 70mm is width and 30mm is height.. so excel will make box according to this size
I have 12 macros in the sheet . I want to make those codes as ADD-INS. (like ASAP UTILITIES , EASY XL ....)
I have been browsing for right direction but finally Failed and I haven't found better than this place.
I am going to make a subtitle file by excel. However, I found the after I save the excel into a text format and open it in the note pad, there are " marks on every time line. I would like to know how to make the time line as clean as say 00:12:23,100 instead of "00:12:23,100".
View 1 Replies View RelatedI've just had Office 2007 installed on my computer and I HATE the new Excel layout. Is there any way I can get the old look back - I liked the old customizable toolbars and I hate these new ribbons.
I know there's one customizable easy access toolbar. Is there a way I can get more toolbars and get rid of these dangblasted ribbons?
i hv following code , which is working fine.
Sub M_0_1_project_20080107_ask_how_to_make_last_row_as_variable()
Range("F1") = "month"
Range([e2], [e6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=month(RC[-1])"
Range("g1") = "yr"
Range([f2], [f6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=YEAR(RC[-2])"
End Sub
i like to change to code as following , but it cannot work , hope some one can help me.
Private Sub Worksheet_BeforeDoubleClick _
lastrow = 6
Range("F1") = "month"
Range([e2], [e[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=month(RC[-1])" _
Range("g1") = "yr"
Range([f2], [f[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-2])" _
End Sub
As i have many variable with same function, tried of keep change last row number.
Every month I get a big 5,000 row worksheet from a mainframe download. I need to move data from the one worksheet into multiple little worksheets (about 25 of them) according to a field called "OrgUnitName".
How can I get the results, not in column D but in a textbox,so I can choose one of the
names from that text box?
Sub concatvals()
Dim strvalue As String
Dim strsearch As String
strsearch = InputBox("What Number do you want :?")
For Each c In Range("A1:A100")
If c.Value = strsearch Then
If Len(strvalue) < 1 Then
strvalue = c.Offset(0, 1).Value
Else
strvalue = strvalue & ", " & c.Offset(0, 1).Value
End If
End If
Next
Range("D1").Value = strvalue
End Sub
#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
=RtHistory("IDN",B5,B7:D7,B6,"LAY:HOR NULL:SKIP ZERO:SKIP SORT:ASC")
the #N/A errors are based on the line above. Is there a way to remove the #N/A? I want to make Row B/C/D above blank
is it possible to put a message box in a VBA code so that the macro only runs once, else the worbook needs to reopened again for it to work. Is this the best way from preventing the macro to run again?
If i have multiple users, there is always a chance one might accidently press the command button twice.
If i wanted to tack on something to the end of a formula so that it would make every 50 add 1 (example: 200 would convert to 204, because 200 divided by 50 is 4).
I am trying to figure out how to make a macro run just once a day -- the first time i open a workbook and then no matter how many times I open that workbook again it does not run......... I know how to get to run automatically when i open the book - just not how to keep it from running again THAT DAY.
Then the next day when i open that book again I want it to run again -- once......and so on..................
anyone know how to make cells number going down the row in "4's. I wish to have cells, A1,B1, C1, D1 to have a "1"in it, and the next four down (E1,F1,G1,H1)to have a "2", and the next four to have a "3",and so on.....
View 9 Replies View RelatedI made a msg box but i dont want any option to be displayed in it.. it only should display as an info to the user.
View 9 Replies View RelatedI need to use VBA to test if a certain directory exists and if not ten create it.
I do this with:
[CODE]Sub MakeDir()
Dim DirName As String
Dim FileName As String
Dim fnameshort As String
FileName = "C:TestA1ATest.xls"
fnameshort = Right(FileName, Len(FileName) - InStrRev(FileName, ""))
DirName = Left(FileName, Len(FileName) - (Len(FileName) - InStrRev(FileName, "")))
If Dir(DirName) = "" Then
MsgBox "Does not exist"
'Dir to be created using MkDIr
End if
End Sub/CODE]
My problem is that I need the script to loop back until it finds a folder that exists, and then make the respective subfolders.
Example:
C:TestA1A should be created, but currently only C:TestA exists.
VBA should then test for the full path, and when it finds out, that it does not exist, it should strip off one folder, test for that and so forth.
When it finds an existing folder, it should then make the respective subfolder, and then the subfolder to that...
how cam I make same as link? http://rapidshare.com/files/29223104/JR_DatePicker_v1. zip.html. to install my own add-ins like that...
View 5 Replies View Related
