If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamically reference a cell based on cell content
Hi All,
I do not know how below copy-paste will show on the post, but I will try to depict the question in self-explanatory way, so you will gatch what's my problem: I have an Average function to calculate the average for the last 12 months just before Total Colum - (I have several prior periods, but I want only averge the last twelve month from current month backward). I can do this by the help of below formula: 1217.416667 =AVERAGE(OFFSET(Q17,,-1):OFFSET(Q17,,-12,,12)) PER-1 PER-2 PER-3 PER-4 PER-5 PER-6 PER-7 PER-8 PER-9 PER-10 PER-11 PER-12 PER-13 PER-14 TOTAL 4567 1234 12456 1235 124 432 45 234 23 12 12 12 12 12 12 However, I do not want to manually type Cell Q17, R17, S17 depending on every time I add new period. So what I want is to ask Excel to find the column/cell reference where word TOTAL stands and counts back. Does anyone has got a tip...? Thanks in advance Regards, Fuad |
#2
|
|||
|
|||
Dynamically reference a cell based on cell content
I dont' see exactly what you are trying to do but if you want to reference
the word Total you can use the match function that will tell you at what position you will find the word. For example =match("Total", A3:AA3, 0) will find the word total in the Range A3:AA3 and tell the number of cells to the left of A3 where you matched. -- HTH... Jim Thomlinson "Fuad" wrote: Hi All, I do not know how below copy-paste will show on the post, but I will try to depict the question in self-explanatory way, so you will gatch what's my problem: I have an Average function to calculate the average for the last 12 months just before Total Colum - (I have several prior periods, but I want only averge the last twelve month from current month backward). I can do this by the help of below formula: 1217.416667 =AVERAGE(OFFSET(Q17,,-1):OFFSET(Q17,,-12,,12)) PER-1 PER-2 PER-3 PER-4 PER-5 PER-6 PER-7 PER-8 PER-9 PER-10 PER-11 PER-12 PER-13 PER-14 TOTAL 4567 1234 12456 1235 124 432 45 234 23 12 12 12 12 12 12 However, I do not want to manually type Cell Q17, R17, S17 depending on every time I add new period. So what I want is to ask Excel to find the column/cell reference where word TOTAL stands and counts back. Does anyone has got a tip...? Thanks in advance Regards, Fuad |
Thread Tools | |
Display Modes | |
|
|