Indexing formula help

[BrianHall]

Good morning,


I'm trying to create a formula that will update cell D7 in Tab #1 based upon manually entering data into monthly cell L37 in Tab #2. (In this example, we are using November that would be cell L37 in Tab #2. October would be the prior cell and so on and so forth.)

I would like just to enter the month number in cell H2 in Tab #3 to automatically change the value in cell #D7 OF Tab #1 based upon the current month value in Tab #2.

	Tab #1
	C	D
6		Current Month
7	Revenue	$ -
	Tab #2
		K	L
		Oct-13	Nov-13
37	Revenue	$ 60,784,000	$ 40,197,000
	Tab #3
		H
2	Enter the Current Report Month #	11

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY>
</TBODY>

 

[BrianHall]

Thinking maybe a hlookup, match and index combine type of formula? I definitately need assistance on this one. Anyone......

 

[AhoyNC]

Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	B	C	D	E
5
6			Current Month
7		Revenue	40,197,000
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
35
36		Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13
37	Revenue										60,784,000	40,197,000
38
Sheet2

Excel Workbook
	F	G	H	I
1
2		Rep. Month	11
3
Sheet3

 

[BrianHall]

Good afternoon AhoyNC,

Thank you for your assistance. I'm a little lost when you mentioned "This is an array formula and must be entered with CTRL-SHIFT-ENTER.".

Do you mind explaining this to me?

Thank you...

 

[BrianHall]

Good afternoon AhoyNC,

When I use the formula above I'm getting a "N/A".

Any assistance would be greatly appriciated.

Thank you again for your help..

 

[BrianHall]

Good afternoon again AhoyNC,

I just figured it out. The formula works great.

Thank you again..

Brian

 

[AhoyNC]

Glad you figured it out. An array formula looks at a series of data values rather than just a single data value. If you just use ENTER to put in the formula it will return an error (or worst some times an incorrect answer).
Thanks for the feedback.