Fixed reference for column in a table formula, like $ in normal excel

**taranaki88** · 11-21-2013, 11:52 PM

Hi all I have the following formula to pull the number of visits from a table (VisitVolume): =INDEX(VisitVolume,MATCH($A45,VisitVolume[OPD Departments],0),MATCH(AdmissionsVolume[[#Headers],[1]],VisitVolume[#Headers],0))

The table lists hospital departments as rows and years in the columns. So for example in year one visit volume for surgery will be 1000, year two will be 2000 etc. In year one visit volume for pediatrics will be 3000 and in year 2 3500 etc. I use the formula above to pull the visit information I need for a particular department in a particular year to then use in another formula in another table - AdmissionVolume. The actual visit volume for a particular year will be multiplied by a percentage to give me the number of admissions for that department, for that year.

My problem is fixing the column [OPD Departments] as a permanent reference. I have placed the formula in the first cell of the AdmissionVolume and it works correctly. However when I drag the formula across to year no. 2 the column reference [OPD Departments] in the VisitVolume table is changed in the formula to the next column along: INDEX(VisitVolume,MATCH($A45,VisitVolume[1],0),MATCH(AdmissionsVolume[[#Headers],[2]],VisitVolume[#Headers],0))

I need VisitVolume[OPD Departments] to always stay as that, however I still need AdmissionsVolume[[#Headers],[1]] - which pulls the visit information from the first year - to change to AdmissionsVolume[[#Headers],[2]] - i.e. now pulling the visit information from the second year. If I drag the formula to fill cells then the AdmissionsVolume[[#Headers],[1]] changes correctly however VisitVolume[OPD Departments] doesn't. If I copy and paste the formula then VisitVolume[OPD Departments] stays the same but then the year doesn't update in AdmissionsVolume[[#Headers],[1]].

Argh! In normal excel it is easy to just throw a $ in front of the row or column you wish to remain fixed. How can I tell the formula that I want [OPD Departments] to stay fixed but to increment AdmissionsVolume[[#Headers],[1]]?

Thanks in advance for your help!

**TMS** · 11-22-2013, 12:31 AM

See the information in this link:

http://www.excelcampus.com/tips/abso...uctured-table/

Regards, TMS

**taranaki88** · 11-22-2013, 03:41 AM

This is perfect! Thanks so much Trevor you really have made me happy - I spent so long trying to find the answer.

How do I mark this as solved?

**TMS** · 11-22-2013, 03:47 AM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.