SUM / LINK FORMULA PROBLEM EXCEL 1

[elfa]

Hello,

Cell C82 contains the number 4. This represents a number of a month. ie. Jan 1 feb 2 etc etc.

I need to be able to go from 1 to 12. The formula below does not let me get past 7.

Do you know another way?

Thanks.


=IF(Charts!C82=1,Forecast!C53,IF(Charts!C82=2,Forecast!D53,IF(Charts!C82=3,Forecast!E53,IF(Charts!C82=4,Forecast!F53,IF(Charts!C82=5,Forecast!G53,IF(Charts!C82=6,Forecast!H53,IF(Charts!C82=7,Forecast!I53IF(Charts!C82=7,Forecast!I53))))))))

 

[striker83716]

I am assuming somewere above each of the cells on your worksheet titled forecast you have the month identified.

If not this might be a good idea to do so. Once you have that use the Hlookup function
column #
52 53 54 55 56 57 58 59 60 61 62 63
Row C 1 2 3 4 5 6 7 8 9 10 11 12
Row D A B C D E F G H I J K L

formula =HLOOKUP(C82,forecast!C52:N53,2) Your example of C82=4, the formula will return D

Note: This formula looks at the area c52:N53 and returns the value found in the 2nd row . If you have more rows of data between the month and the data you are trying to return then adjust to formula to your needs. i.e
=Hlookup(C82,forecast!A52:N53,53)

Good luck

 

[striker83716]

The last formula was incorrect and should have read

=Hlookup(C82,forecast!C1:N53,53)

It is getting late
Good luck

 

[KenWright]

Just use the following:-

=OFFSET(B53,,Charts!C82)

or

=OFFSET(C53,,Charts!C82-1)

Syntax
=OFFSET(Start_Here,Offset_Rows,Offset_Columns)

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GlennUK]

And in addition to Ken's useful syntax help, if you want to have more than one cell returned by OFFSET this is the syntax:

=OFFSET(Start_Here,Offset_Rows,Offset_Columns,Num_Rows,Num_Cols)

Cheers, Glenn.

 

[elfa]

Cheers Ken,

Spot on.

Regards
Elfa.

 

[KenWright]

You're welcome, and thanks for the feedback

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------