Indirect Formula (Excel XP)

Topic

New Reply

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 E–the 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

Reply | Quote

Replies

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 value-of-A5 columns to the right, and return the value.

Reply | Quote

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 formulaa–especially those that permit a “dynamic” reference. I grew up on Lotus and mastered its macro language, but like many lotus users, became an ex-lotus users when Excel became the worksheet of choice for most of the world. THANKS.

Reply | Quote

You could read and understand the code here in the Lounge.

Reply | Quote

Thanks..I do this. I get an e-mail 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.

Reply | Quote

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.

Reply | Quote

The Excel Books by John Walkenbach are highly rated.

Reply | Quote