Referencing data in different worksheet based on month name
 

Hi all,

I've been getting more ambitious with Excel and now I'm in a bit over
my head - hope someone can help. I sense it's an easy one for those in
the know...

This is for a charitable organization I volunteer for. I have two
worksheets - let's call them "YearSheet" and "MonthSheet".

"YearSheet" has a row for each month. Month names run down the left
hand side (let's say A1 to A12). Various fundraising amounts for each
month follow, with a grand total in the last column.

On "MonthSheet", I have a cell wherein you type in the name of the
month you want a detail report on. Below that are various figures taken
from that month's row in "YearSheet". But I want those to get filled in
automatically depending on which month you've typed into the box at the
top.

Essentially I want to say, "Find the cell in 'YearSheet' with the same
month-name that's typed in on 'MonthSheet', then get figures from a
cell a certain distance to the right of that one."

I've played around with "OFFSET" and "MATCH", but can't seem to combine
the two. Also toyed with naming the month cells in "YearSheet" but
can't seem to combine a reference to a cell name with an OFFSET.

Does this make sense? There must be an easy way to do this... right? I
just want to avoid having to type any figures in again on "MonthSheet"
- I want them all to be taken from "YearSheet" automatically.

Thanks heaps!

Adam

Try the VLOOKUP function.

For example if you type the month in cell A1 on the Monthseet then your
formula could be:
=VLOOKUP($A$1,YearSheet!$A$1:$H$12,5,0).

More info on vlookup in Excel Help.

Regards
Rowan


"cosmo_canuck" wrote:

Hi all,

I've been getting more ambitious with Excel and now I'm in a bit over
my head - hope someone can help. I sense it's an easy one for those in
the know...

This is for a charitable organization I volunteer for. I have two
worksheets - let's call them "YearSheet" and "MonthSheet".

"YearSheet" has a row for each month. Month names run down the left
hand side (let's say A1 to A12). Various fundraising amounts for each
month follow, with a grand total in the last column.

On "MonthSheet", I have a cell wherein you type in the name of the
month you want a detail report on. Below that are various figures taken
from that month's row in "YearSheet". But I want those to get filled in
automatically depending on which month you've typed into the box at the
top.

Essentially I want to say, "Find the cell in 'YearSheet' with the same
month-name that's typed in on 'MonthSheet', then get figures from a
cell a certain distance to the right of that one."

I've played around with "OFFSET" and "MATCH", but can't seem to combine
the two. Also toyed with naming the month cells in "YearSheet" but
can't seem to combine a reference to a cell name with an OFFSET.

Does this make sense? There must be an easy way to do this... right? I
just want to avoid having to type any figures in again on "MonthSheet"
- I want them all to be taken from "YearSheet" automatically.

Thanks heaps!

Adam

Thanks Rowan, that's brilliant! My problems are solved.

One day I must sit down and learn all of Excel's nooks and crannies
from top to bottom. Hard though since I usually only have one
particular thing I want to do and there's so many specific functions..
ah well, RTFM as they say, right?

Again, many thanks!

Adam

You're welcome. Good luck with that manual <g.

"cosmo_canuck" wrote:

Thanks Rowan, that's brilliant! My problems are solved.

One day I must sit down and learn all of Excel's nooks and crannies
from top to bottom. Hard though since I usually only have one
particular thing I want to do and there's so many specific functions..
ah well, RTFM as they say, right?

Again, many thanks!

Adam