In MS Excel 2011 for Macintosh can I assign a macro to an object, such that if the user clicks the object it runs the macro? That is creating a button on the spreadsheet that will execute a macro.
View 2 RepliesI am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.
I am having some success for each task with exception to relative cell values .....
As the code will be specific to the row (one button per row) Questions:
how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)
Using excel 2011 for mac and would like to learn how to write and record a macro to link to a button to "go to a layout and cell"
View 4 Replies View Relatedsample pic.png
I have two spreadsheets of data I export regularly from an outside source. I want to consolidate the data from these two sheets into one large sheet with all the data in a more concise form bringing in only the data related to certain parts I am conducting analysis on. I have a list of these parts already. My first thought on how to do this would be to use the list in an entry for an input box and have that list be the index for a for loop in which i searched for a part code, copied the entire corresponding row, and pasted it back into the master sheet. I have gone through and tried to do this but I am running into problems because sometimes I have multiple rows of data that correspond to a single part number. I have attached a template of the way that the data should look in the master file with the multiple rows of details that correspond to just one part entry.
create an excel spreadsheet, where i can enter all my products name, unit price etc. so when i am doing an invoice for the customer, if i just type the product name price will show up automatically.
View 4 Replies View RelatedHere's the spreadsheet I am talking about : [URL]
So as you can see each row = one day divided in 2 cycles of 12 hours : every 12 hours the cat gets a new injection of insulin (the columns "U" being the number of units of insulin the cat receives)
Now I want to turn this sheet into two different curves that would look like these : [URL]
As you can see the first graph shows data based on days (x) and insulin dose (y), while the second curves shows data based on days (x), and blood glucose values (y). This is basically the only way we could clearly see how the cat's disease is evolving over a long period of time.
If he had just started to take all these values from his cat I guess it would be simple to do, but since he already has a lot of data entered in his spreadsheet, I was wondering if I could use this data (and not write each number in a new document), to make things a little more simple and to avoid mistakes.
Can anyone tell me what the macro code is to do this (at whatever box I have selected at the time)
say my box is selected at E3, then when I click the button, it creates a new row E
likewise for F7, AA1, etc...
My VBA which works fine on Excel 2007/2010 does not work on Excel for Mac 2011.
how I would convert this to work on Mac 2011?
Code:
Columns("AI:AI").Select
Selection.TextToColumns Destination:=Range("AI1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
[Code]....
I am running Excel 2011 on Mac OS X. I am trying to save a file (.xls) to a server (Autumn). I have tried several configurations to try to get the file saved properly. File name is "Testfile.xls"
1. folderpath = "Autumn:Tim:TestFolder:" & filename --Activeworkbook.Saveas filename:=folderpath
2. folderpath = "Tim:TestFolder:" & filename Activeworkbook.Saveas filename:=folderpath
3 Activeworkbook.Saveas folderpath
When I try this, I get an Error 1004 indicating that it cannot find the file. Do I need to eliminate the .xls and then specify that when I do the saveas?
I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.
E.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.
I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.
I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).
Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.
If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".
For Mac Excel 2011, though I think my question would apply to any modern version of Excel.
This is a little odd. I have a sheet where, for various reasons (see below ### if interested), I need to copy a hyperlink, replace a substring, and then return the new hyperlink (with old friendly text) to the original cell. (I actually have to do this with an arbitrarily-long column of links.) This was my plan of attack:
* Pull the URL from the hyperlink cell.
* Use FIND and RIGHT to strip out what I need and replace it
* Use HYPERLINK to build a new cell with the original friendly text and the new hyperlink.
* (Manually) copy this new link (really, a column of links) back to the original cell (really, column of cells).
Here's where it breaks down:
a) If I simply cut-and-paste, the formula used to pull the hyperlink becomes circular (as do several others), and Excel complains.
b) If I cut-and-paste-special (values only), I get just the friendly text, no link info. Likewise if I try to reference the "fixed" hyperlink.
The erroneous hyperlinks are in column K. My calculated correct hyperlinks are in column S. (There are similar errors and fixes in L and T, respectively.)
I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...
Code:
Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string
[Code]....
For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.
Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?
So i am working on an attendance tracker here. I already have aligned on a separate tab all of the works aligned next to their manager
EX
Column A: Column B:
Worker Name Manager
What i would like to do is create a drop down menu with all of the Managers name listed to where someone can select that manager and when they do all of the workers names show up in Column A and then that Manager Listed next to them.
Is there a way to do this... I am using office 2011 btw. Once when i get this last part completed with this drop down menu i should be complete!
I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????
View 5 Replies View RelatedI use mac and I have "Excel for Mac 2011".
I need to exact some data from a file. I can open the file, but can't see any data of it. I am afraid I need to do some setting so that I can see the data, but I don't know what should I do
I'm using Excel 2011 on a MacBook Pro. Here's my situation:
I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.
As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:
1) Creates a new spreadsheet, with each click, named for the current month and year.
2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet.
3) Clears the data from Spreadsheet #1 AND Spreadsheet #2.
To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.
How to insert a Calendar Dropdown for Mac Excel 2011? Also, do I have to readjust this (for lack-of-a-better-word) 'add-in' everytime the calendar year changes? This will be used for a payroll template.
View 1 Replies View RelatedMy software is Excel for Mac 2011 version 14.4.3 (140616).
I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1
I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".
For example using the PV formula I know that if I have $49,019.96 invested at 1%, it will generate $10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield $10,000 per year for 5 years, again exhausting itself completely in the process, with the first $10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.
I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.
I'm using Macbook Pro Microsoft Office for Mac 2011
Please see attached file
The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.
The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.
Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"
So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C
The Value column would then show a value based on the options selected.
I am using Excel 2011 and we have a link data source to the outside SQL Server.
For the 1st step, I linked to the Database and retrieve the data.
P1.png
2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.
P2.png
3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.
P3.png
how I could retain the position of another column after the data refresh?
I have Excel MAC 2011 and my spreadsheet was created with multiple cell colors randomly throughout the spreadsheet.
I need to change all the royal blue colored cells to "no color" without changing the data contained in the cell. For example; Cells A5:A38 are blue, A39 is green, A40:A50 blue, A51:60 green, ... Cells B5:B15 are grey, B16:B20 are blue, B21 is yellow...
I can't find the "Advanced Options" for Find and Replace that might have allowed me to specify a color change.
I have switched from an ageing Mac Powerbook, Excel 2004 for Mac, and an out-of-date version of Firefox to an IMac with Excel 2011 for Mac and Safari as a browser. The only reason why I mention this is because I don't know if it was easier to do this on an older platform (one of those bizzare situations where going forwards is actually a step backwards).
In short I like doing things with Sports data in Excel. Usually I would hit my favourite website for pulling sports data (which incidentally I don't know if this has changed as I've been too busy to do much of this kind of thing for about 4 months - or if it is just a different format on a newer machine/different browser);
[URL] ....
The link should be showing NFL results for this year so far (I like the site as it has a handy amount of other sports etc.).
I would usually then go edit/paste special - unicode text and then hit the paste options, use the text import wizard, go delimited, tabs - and usually everything would come in on a row and then I could do what I need to do with it (e.g. use Excel's text functions to get the data - taking spaces out for example - to where I want it to do other things).
The problem I seem to be having now is that when I do the above - things no longer seem to come in on the same row (which makes life difficult for doing things easily with the data). Once things end up on different rows it becomes a pain to do basic things.
In short is there a way round this (or do I need to find another source for my data with tables laid out in a more friendly way to "borrow" data)?
I'm using Microsoft Excel for Mac 2011. I'm creating a drop down box from List within Data Validation. It's only letting my create a list of 17 names. My longest list is 63 names. What do I need to do to create a list that will support that?
View 3 Replies View RelatedI'm using Excel 2011 for MAC.
What I need is to clear and lock some cells based on a drop down list selection.
The case is like this:
In Cell [E14] is a drop down list : 0,1,2 or 3
Cells: (D19:E19), (D21:E21), (D23,E23) Merged are PART DESCRIPTION
Cells: [G19], [G21], [G23] are COST
So I need when 0 is selected in the dropdown list, all of the above cells are cleared and no input is permitted, Cells locked.
If 1 is selected, just the first row is allowed for data entry and the other 2 are cleared and locked as this values populate another worksheet for calculations.
So far I did this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [E14] = "0" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("D19:E19").Locked = True
ActiveSheet.Range("G19").Locked = True
ActiveSheet.Range("D21:E21").Locked = True
ActiveSheet.Range("G21").Locked = True
ActiveSheet.Range("D23:E23").Locked = True
[code]...
It works fine to lock the cells, but I can not fined the way to clear the values previously stored before locking the cells.
I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.
How to get the sorted blank cells to the top?
I'm using Excel 2011 on Mac OS X 10.7.
I've been looking for a free checkbook register template that will automatically calculate the balance as you add or subtract various amounts.
I found one which seemed to be just what I wanted but once I had reached the end of the rows for the worksheet and tried to add more rows the automatic feature quit working.
I found another Checkbook Register which seemed even better than the first one as it had a notation at the end of the rows.
It said to " Insert new rows above this point" and it had the last row greyed out.
I tried adding a new row and tested it out by adding some amount but it did not continue with the automatic calculation either.
I'm using MS Excel for Mac 2011
I have a workbook with 2 sheets.
Sheet 1 will act as a summary for sheet 2, showing only the last 5 entries from sheet 2.
Sheet 2 is based on football results of a season which will have a total of 34 league games plus cup games.
Sheet 2 has 4 columns:
Game Number Home Team Result Away Team
1 United 2-0 City
2 Sportive 4-2 Uniao
3 Real 1-0 Ateltico
4 Villa 1-3 City
5 United 2-1 Real
6 Atletico 0-0 Villa
So sheet 1 will show the last 5 results as the season progress's and new data is added.
I have been able to extract column 'GAME NUMBER' using the following formula:
=INDEX('Back end 1'!A:A,MATCH(9.99999999999999E+307,'Back end 1'!A:A)-ROWS('Back end 1'!A2:A$6)+1)
I am struggling to find a way to pull the last 5 data that is text based.
I am using Excel Mac 2011.
I manage a number if individuals in the Army and as such, track their annual training requirements. Some requirements are semi-annual, annual, quadrennial, etc...
I have a spreadsheet to track the dates that the requirement was last completed (attached). I would like to use conditional formatting to turn dates that are within 30 days of being due (or blank) RED and dates that are between 31 days and 60 days of being due YELLOW.
I am using Excel MAC 2011.
I am creating a spreadsheet for results of a survey. I am trying to give numeric values to text fields so that I can place the cell values later into a SUM function. The text fields are entered into the spreadsheet via a drop down list in each cell I created by utilizing the validate button. The text field contains choices like The text field contains choices like “1 Very poor,” or “4 Very much.” The source for the list is on a separate sheet.
EX: If cell C5 equals the choice “1 Very poor,” and cell C8 equals “4 Very much” I want the cells to have numeric values of 1 and 4 respectively so that I can utilize an equation like =SUM(6-C5+6-C8) later in the spreadsheet to calculate aggregate scores.
I am using a 2011 for Mac version
Designing a proposal on MAC EXCEL 2011
1 workbook with:
1 page needs to print vertical
1 page needs to print landscape
Is this possible? (without reloading paper and printing a second time)
Right now I can only get it to print landscape OR portrait but not both ...