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: OFFSET
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).
Search across sheets in function
Just when you need to search for a thing across sheets in a workbook.
I am bored, so I am adding articles from my old archive.
Min Max vehicle
Finding which vehicle with minimum (or maximum) amount of savings among peers.
This is an old request, found its formula in my archive to calculate which of the 4 columns is actually has the minimum (or maximum) of savings among other columns.
Trick is as you guessed it was OFFSET.
Looplist – Repeating months using formulas
Using formulas to repeat list, I call it LoopList
Used to list Months for several years, in away that with 1 formula, it will loop through all months, then jumps into next year and loop through its months again, and so on
Skills Grid
A small Excel file to show how we can create a chart-like Excel sheet
Used in my Resume to show different skill sets and the level of expertise in each
OFFSET+MATCH
One of the power of combining functions in one cell is using the best of these functions
An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.
Why do we want to do that?
VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time
We needed several times to locate a row by using a column on the right, then get the value from a column left to it.
This is when VLOOKUP can not help us.
Why I ran from =INDIRECT() function
I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()
Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed
Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.
You see INDIRECT does a simple job
Converts a text that represent a reference, into reference
So…
If you do
=INDIRECT("G4")
It gives you the value in G4
Notice that “G4” has the double-quotations, means
=INDIRECT("G"&SUM(S1:S4))
Fx to cut long column into two columns
Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column
This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB
Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.
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:
Extract Address into table
Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table
Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above
Which is usually what you got from any list of addresses online, and you want to convert it to table.
This is exactly what one of clients had and needed, so here is the file that does that.
Basically you need to have 5 columns with formula in column G as:
=OFFSET($B$1,(ROW()-5)*4+4,0)
to extract first line (Starting from cell B5
Then in column H as: