Results 1 to 7 of 7
Thread: Indirect Formula (Excel XP)

20050519, 13:52 #1
 Join Date
 Mar 2001
 Location
 Canton, Ohio, USA
 Posts
 270
 Thanks
 3
 Thanked 0 Times in 0 Posts
Indirect Formula (Excel XP)
Does anyone know if the indirect formula will derive the column location versus a row location? I have a worksheet with descriptions in column A and monthly data in the next twelve columns. Since we are through April, only columns B, C, D, and E are populated with financial data. What I am trying to is pull for another worksheet in the same workbook a report. I want the latest information.
For example, Row 5 has sales information. As of this date, I want to pull the cell value in E5 as that is the APril sales and is the latest available information. In May, when I generate the report I want it to pull F5 (may sales) and for June, pull G5, etc.
I do have a month number counter in the worksheet to calculate year to date budget. This cell is currently set to 4 and I will manually change it to 5 before running the May reports.
What I want to do is have a dynamic formula that takes the month counter (now at 4) and adds one to it (i.e., to reflect column A with descriptions and not monthly data) to dynamically select the proper column (i.e., for April this calculation would yield column Ethe fifth column from the left. The sales information is always in row 5.
Can the indirect formula be used to derive column location...all the examples I have found calculate the row number once the column is given. I can't change the format of this worksheet to accommodate the calculation of the row location. What I need is the calculation of the column location. THANKS

20050519, 14:05 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Indirect Formula (Excel XP)
1. You could put the following formula in a cell to calculate the number of the previous month automatically:
=MONTH(TODAY())1+12*(MONTH(TODAY())=1)
2. You can use the OFFSET function to get a cell using a shift from another cell. Say that the number of the previous month (entered manually or as a formula) is in cell A1. The following formula will get the value from the appropriate cell in row 5:
=OFFSET(A5,0,A1)
This means: start at A5, move 0 rows down and valueofA5 columns to the right, and return the value.

20050519, 15:01 #3
 Join Date
 Mar 2001
 Location
 Canton, Ohio, USA
 Posts
 270
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Indirect Formula (Excel XP)
Hans,
As always, your advice is greatly appreciated....I used the offset function...this worked great! I do have one more question for you...how does one learn about all the formulas available in EXCEL? I have an "begining type" excel book, but it does not provide a complete list of the formulas available in EXCEL...do you just study the help and browse through the functions? I have found this method is hard on my eyes. I guess I need to get more disciplined as I never seem to find the "right" answer and try to make a formula that I know about work in the situation needed...like in this case I tired to make the indirect function calculate a column versus a row location.
Is there a "good" excel book that specifically teachs the formulas already available and how to use them? It seems so many of the "beginning" books are the very worksheet navigational basics, which I know most of this material...but am weak on the formulaaespecially those that permit a "dynamic" reference. I grew up on Lotus and mastered its macro language, but like many lotus users, became an exlotus users when Excel became the worksheet of choice for most of the world. THANKS.

20050519, 15:04 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Indirect Formula (Excel XP)
You could read and understand the code here in the Lounge.
Legare Coleman

20050519, 15:10 #5
 Join Date
 Mar 2001
 Location
 Canton, Ohio, USA
 Posts
 270
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Indirect Formula (Excel XP)
Thanks..I do this. I get an email every night of all the Excel posts from the previous day and I do read them. I guess I am learning...but I feel like I need the lounge help too much. Like today, I was so convinced that I could make the indirect function work for my application...after two hours, I gave up and posted in the lounge. I suppose I need to adjust my frustration tolerance and either post sooner to the lounge or not get so frustrated when I don't know how to solve my problem or am using the wrong function or formula. THANKS.

20050519, 15:11 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Indirect Formula (Excel XP)
The Excel Books by John Walkenbach are highly rated.

20050519, 16:19 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Indirect Formula (Excel XP)
We all need help once in a while, and as soon as you think that you have it down pretty good, Microsoft comes out with a new version with a bunch of changes in it. You seem to already have one of the best methods of learning down pat, trying to do it yourself first and asking for help when you get stuck. Those who ask for help first seldom learn much from the help they get. Excel is big and complex, and does take a while before it starts to make sense.
Legare Coleman