An easy way to scroll through set of data in a graphic way without graphs.
A-pure-formula xlsx workbook. These were done using formulas, conditional formatting, and ActiveX control.
Category: COLUMN
Repeated Offset
I needed to have an offset of columns to be repeated every 12 months.
This is part of a project, we needed to calculate the coefficient of sales prediction based on the month number, we do have coefficient saved in another table that repeated every year (12 months).
Get column name (columnname as A,B,C, etc) as input inside cell
Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters
However, the COLUMN() is kind of tricky
Because of the fact that cell addresses (or references) are based on the old style of R1C1
=Column()
Read More “Get column name (columnname as A,B,C, etc) as input inside cell” »
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:
Rotate table using 1 formula
Rotating table (Transpose) using functions so that the table is updated once the source table is updated. Also can be used as automatically rotating tables with variable number of rows and columns Sheet name can be used inside formula so that you can have multiple tables from different sheets to be rotate it into one…
Sort list dynamically (functions)
Sorting a list automatically using formulas, with no need to press the sort command Also if the source table is changed, the destination table will do also. What you need is basically two formulas, one for the sort-by column to list items by order. Use SMALL to sort ascending, or LARGE to sort descending, then…
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…