I have a spreadsheet with all 365 days of the year in row 20 along 365 columns. I have buttons for each month of the year.
The days of the month are signified along row 20 from cell "H2" onwards as 1-Jan, 2-Jan, 3-Jan and so on until 31-Dec.
When I click on the "June" button (or any of the buttons with the month) I would like the columns with all the days from that month to unhide.
I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
I have a single button I want to use to call a macro to:
1.Hide columns C:AZ if they arent already hidden
2.Unhide columns C:AZ if they are already hidden.
I want to set up a macro that will hide and unhide columns.
View 9 Replies View RelatedI'd like a macro that can hide/unhide certain columns. At the moment, the columns I want to hide/unhide are F, I, M, P, U and Y.
View 4 Replies View RelatedI need hide/show some column by using Macro Button. I have attached the excel sheet( name VBA testing.xls). I need to hide column K,L,N,O & visible column G,H by clicking button "Plan A".Similarly i need to hide the column G,H,N,O & unhide the column K,L by clicking the button "Plant 2. Similarly by clicking the Button "Plant 3", hiding the column G,H,K,L are needed whereas column N,O will be unhide.
View 7 Replies View RelatedNeed a macro for each button.
The sheet will be protected with a password (in the future, users will have varied access privileges).
Column A is designated as the "Button" Column.
There are 5 buttons here. Each representing the area on the sheet that needs to be viewed. Once the button is pressed, it takes you to that section of the sheet. At this time, I have designated each column area as:
a-z
aa-az
ba-bz
ca-cz
da-dz
I have tried this formula with opening tabs, but this won't work.
I'd like for my spreadsheet to open with only Column A visible; I'd like all other collumns to stay hidden.
I want to create a marco to unhide column(s) based on the value of an individual cell.
For example:
if I enter the number 5 into cell A1, I would like Columns B:F to unhide.
if I enter the number 2 into cell A1, I would like Columns B:C to unhide. Is this possible?
I want to hide and unhide columns based on a cell's value.
If D6 of the ‘Summary’ Worksheet Is <> to ‘Test1’ and <> ‘Test2’
THEN Hide columns D:K of the ‘Charts – Source Data’ Worksheet
OTHERWISE Unhide columns D:K
I'm trying to record a macro which will hide and unhide columns K:P of data, but I only want one button. I know how to do this to produce one button for hiding and another for unhiding...but I want one combined button.
How to use vba, how I do this via the macro recorder?
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
function in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.
Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill
I would like the function to stop filling dates at end of the month even for shorted months such as Feb.
I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.
The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.
Input Data
Result
Start Date
[Code]....
using Excel 2007/Vista
when I go a new edit a macro, I get the message
"Cannot edit a macro in a hidden workbook. Unhide the workbook using the unhide command"
Where do I find the relevant "unhide command"?
I want to convert Months into days
E.g. - in my Data F2 = Oct.13 and I want 31 days in F3.
I need to calculate the number of days chargeable in a given month for a tenancy as follows:
eg Current Month 30 Sep 2008
Begin End No of Days in Month
Tenancy 1 01 Jul 2008 31 Jul 08
Tenancy 2 01 Jul 2008 24 Sep 08
Tenancy 1 01 Jul 2008 31 Oct 08
Tenancy 1 01 Sep 2008 30 Sep 08
Tenancy 1 01 Sep 2008 31 Oct 08
Tenancy 1 01 Oct 2008 31 Dec 08
Need a way to get/calculate ALL of the days of the months from a date range entered into an inputbox in which a user may enter any day of a month? In other words, the user enters 9/14/2009 in the first inputbox then 10/8/2012 in the second and code would calculate the number of days as 9/1/2009 - 10/31/2012.
Here is the current code I'm using that calculates only the exact days:
I have is a start rent date and an end rent date. They want to break down how many days in each month the item was on rent so they have a column for each month. For example say an item was rented on 12/14/08 and returned on 1/12/09. It was rented for a total of 30days 18 days in December and 12 days in January. So I would need a function in each column that would return a 14 in the December Column and a 12 in the January Column. there are over 350 rows and they are doing the process manually right now and taking several days.
Also I just noticed that the date range is currently entered as a text in the format 2008-12-14 so YYYY-MM-DD. Don't know if the text will be a problem.
Just attached an expample. In it I am trying to find a function to fill in the green cells.
I have 5 columns: Employee Name, Start Date, Probation Time, Probation Ends, Benefits Start.
I want the Benefits Start column to populate the name of the month that follows the Probation Ends date.
For example Employee A starts on March 16. Probation time is 90 days. Probation Ends is 6/14/09. The Benefits start date is 7/1/09, however I want this column to just say July.
I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.
Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?
I need to count how many in a month per day X number is =. I originally used Countif -
=COUNTIF(B3,">25252525=36")+COUNTIF(V3,">=36")+COUNTIF(X3,">=36")+COUNTIF(Z3,">=36")
The formula above works, but i end up with several long strings of Countif. I'd like to know if there is a way wherein i can use a shorter formula.
Is there any simple formula to get no. of days in a month?
View 9 Replies View RelatedIm trying to use the equivilent of: =DAY(DATE(YEAR(H5),MONTH(H5)+1,0)) in vba. but i am having trouble with the use proper parenthesis.
basically the above will work in an excel workbook and return the number of days in a month. thats all i really need. so if there is an easier way, which im sure there is, then im up for it...
I am using the following array to determine the total dollar of contracts reviewed in one month.
=SUMPRODUCT(($C$5:$C$3002>$H18)*($C$5:$C$3002<$I18)*(D$5:D$3002))
Column C has the actual Date
Column H the beginning of the month
Column I the end of the month and
Column D the dollar amount
Now I would like to count the number of contracts that fall in the following categories in a given month. The minimum number is in Column N and the maximum in Column O.
1-499,999
500,000-2,499,999
2,500,000-4,999,999
5,000,000-12,499,999
12,500,000-24,999,999
25,000,000-64,999,999
65,000,000+
I can get the count for each category but am having difficulty with the count in the specific month. Does any one have any suggestions - other than a pivot table?
I am attaching a small sample.
I am looking for a formula that will tell me how many days in each month for which a given time span is active. For example the time span 1/1/2014-2/28/14. I want to know the time span is active for 31 days in January and 28 days in February. Another example: time span of 1/15/2014-2/28/2014. I want to know that the time span would be active for 16 days in January and 28 days in February. I am looking for a formula that will capture the amount of days in each month for which the time span covers. as I only know how to write a formula with multiple if statements that require a different formula for each given month, rather than one formula that applies to all months.
View 6 Replies View RelatedI am facing problem in "Month & days" formula
In my current salary sheet "Days fill auto according to given months" from 1-31 as you can see in my attached salary sheet
Salary.xls
the Big problem is i want my salary sheet give dates from 23-22 for example if i select "January" sheet should display date from "Jan-Feb" starting from 23rd Jan to 22 Feb only.... and if I select February than sheet should display date from 23rd Feb to 22 March.... and so on
and if there any blank cell left in the dates area it will highlight it with RED auto as u can see in attached sheet.
I receive an extract from our Financial System monthly which list all the balances for each date of the month (both workings day and week-end(Sat. and Sun.)).
I would like to create a macro which will extract only working date (Mon.- Fri) and the balances in the columns.
Having a problem trying to figure out how to create a auto list of days of the month.
Currently I have an input cell with the format mmmm/yy
I then have a list using Weekdays($A$1)... +1....+2.... etc etc
While this works for this current month ( novemeber ) as soon as I chnage the input cell to decemember the list messes up.
The list is in the format of ddd dd
How do i count no of days in a month; excluding saturday & sunday.
View 14 Replies View RelatedI'm have problems workdays in a month. I have highlighted errors in red.
Sheet1
ABCDEF1JoinedPosition EndJan-12Feb-12Mar-12Apr-12213/06/1101/01/1222212221331/01/1122/03/1322212221419 ...