Formulas and cell referencing question

For some reason what I'm asking would be SO easy to SHOW you, but when I try to explain it in words it seems to get real confusing. As a result, I have included a sample workbook to make this easier, if you have time please look at it.

Sample excel workbook.xlsx

In the workbook you can see there are 7 tabs, a "summary" and then months january-june. On the summary tab, i'm trying to compile the data for the headings "Consumption" and # of days" for each month for 4 different towns.

NB: Consumption for all the months are under the C column (in the monthly sheets, not summary), while # of days are all under the E column. The ONLY thing that changes per month is the row location, hence why I have compiled a Row # list.

My current manual procedure is:

Insert $ before the column letter in each formula for town 1 so as to fix it, since they stay the same for each town.
Then i copy the data cells (B5-B10 and C5-C10) and paste them in the same position for towns 2, 3 etc.

Use find feature to locate "town x", record row # for each month and place it in the "row #" column on the summary sheet. (Remember, the column letter stays the same for all consumption and for all # of days charged).

Manually change the incorrect row # in the formulas for consumption and # days to correct row #. This way, the formula refers to the correct cell and displays the correct information.

Basically, is there any way for the formula to automatically "pick up" the number i have under "Row #s"
For example, the formula for Town 1 # of days charged Jan is =Jan!$E6. What I want it to be is =Jan!$E(Summary!D6) where it keeps the Column reference of the January sheet but goes to the summary sheet D6 for the row reference, understand?

Or better yet, if there is a faster/easier way to populate the information then PLEASE let me know.

Thanks in advance for reading this and i appreciate any advice you can offer!

Are you familiar with the indirect function? I am working on a simple solution now for you.

I highlighted in Yellow the changes I made to the formatting, because Town 3 was not in the same location relative to the formulas, in YOUR version of the file.

The formulas are using helper cells in YELLOW just for kicks, but if you want to include the C or E in the forumla you can simply change the reference from teh cell to "C" or whatever. I have changed the formulas in rows F:G (or town 2)to show you the other method. The advantage of the D and E in the helper cells is you wont have to manually change the formula, you can just make sure the helper cell is appropriate.


I hope this helps.


Let me know if you have any questions, it helps me because I never get a chance to use INDIRECT it seems haha.

The formulas under town 2 do not use the helper cells, but if you want to include them it is easy enough to format the text white or hide the row once you are done.

I don't have a preference either way, I would choose whichever required the least amount of work on my end. LoL

As for how indirect works, basically it allows you to use a string of text as a reference.

Im having trouble pasting links because I am on my phone

http://www.cpearson.com/excel/indirect.htm

http://office.microsoft.com/en-us/ex...010062414.aspx

So the match function is pretty straight forward. It looks for the reference in a set array and returns its location relative to the starting point. The indirect function allows you to use references instead of having fixed references in the formula. So instead of the formula saying =jan!c9 I can use =indirect("jan!c9") or a mixture of text and references like in your example.

Also, check youtube for a few indirect examples. That's how I learned it recently.

http://chandoo.org/wp/2008/11/19/vlo...-spreadcheats/

Chandoo is awesome.


http://www.excelcentral.com/excel201...FRJo7AodpAwALg

This has a sample data set, which is nice.

If you seriously want to learn excel I strongly suggest getting a book that has sample data to play with and work through a few chapters a week.

I tried using the sample solution on my actual workbook and keep getting the "#REF!" error and i'm not sure why. I forgot to mention that I am using Mac 2011 version of excel, could this be the reason why?

Anyone else? The solution posed above doesn't seem to work after all =/

My solution works fine. Does it work fine when you open the sample solution I gave you? If so then your implementation is failing you, if not then blame Mac.

It works fine for me. I would guess its your implementation, but just a guess

Copy and paste the formula that is giving you a ref error.

Look at the chandoos link above to see how the match formula works.