Relative field

Question

I'm trying to design a dashboard that shows data from 2 Quarters. The user will choose the surrent quarter from a drop down list, and the doashbaord will pick up the similar data from the previous quarter. I use Hlookup to find the correct data for the current month, but what formula can i use to pick up data from the previous month, automatically, without having to choose the month?

Answer

In your screenshot "Jun-10" was selected in the cell but there are no Jun-10 in the spreadsheet. Do you mean Jun-09? You would like the cell next to Jun-10 to pick up Mar-09 right?

If yes, you can use a combination of MATCH and OFFSET functions to do that.

For example:

=match("Jun-09",[Range]) =offset($A$1,0,[column no.])

Now, replace [column no.] by the MATCH formula.

Feel free to send me the spreadsheet if you need further help.