Now we need to convert week number to month number
This is exactly the same as week number to date (reverse weeknum)
So, when the week number is in A5, then formula below will get you the month number
Category: Logical
iframe in Excel (XLiFrame)
This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.
Formulas used to bring actual data is:
=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))
While formula used to bring header labels:
Convert 2-column into wide (column-row) table
This Excel file will convert a table of two columns into a Column-Row table
Using functions only and auto-updated once the Main table update
In another word, analyze the table into wider form
I needed this one few days ago and you will need this one too.
The main formula that you need to use is:
Multiple-Validations (connected to each other)
Here is the free file for a special request by a client
He wanted to have multiple cells having Data – Validation to same list but minus the selected one
In English, once you select one from cell1, cell2 will bring the same list but without the selected one, then when you select another item is selected in cell2, cell3 will bring what left from the list.
Does that make sense, check out the screenshots
Read More “Multiple-Validations (connected to each other)” »
Insert Blank rows into table using functions
Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…
Change Calculator – calculate change in multiple bills/coins
An Excel file that will determine what cash to return to the buyer (How much of each cash unit, or how many of Fifty dollars and how many of Twenty dollars and so on) in a very interesting way using as less functions as needed So, if you need to return 15.45, this file will…
Read More “Change Calculator – calculate change in multiple bills/coins” »
Tally chart in functions
Using a single function can make a graphical view to your number Function is REPT along with IF and INT to show that number in graphical view, formula used here is: =IF(B4<5,REPT(“|”,B4),REPT(REPT(“|”,5)&”-“,INT(B4/5))&REPT(“|”,B4-INT(B4/5)*5))
CamelCase to Camel Case
Here is a good practice for combining functions to get a full smartsheet It basically add a space before any capital letter in a text, helpful when you have strings without spaces and want to convert them. It is all in formulas. Put additional spaces before any upper case letter. made specially for custom request….