This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(144, 21%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EWG%3C/text%3E%3C/svg%3E)
I am trying to make an Excel form to track progress of a construction project. I want to be able to use the file for future projects, but not have to input the formulas by hand every time. When I drag the formula it increments like normal, but when I grab 3 columns of formulas and drag. It only increments by 3. I need the formula to increment by 11 per column, because the data sheet repeats every 11 columns for each system install being tracked. Is there a formula I can write to get it to increment by 11? I would prefer non-VBA, because there are many people who have very little experience with Excel accessing the file.
Here is the formula I want to increment.
=IFERROR(IF(('Trim Summary'!H$36)*100<101,REPT(CHAR(10),(('Trim Summary'!H$36)*100)/4)&CHAR(10)&'Trim Summary'!H$36*100&"%"&" "&TEXT('Trim Summary'!G$36,"MMM-DD"),"?"),"NA")
So "H" becomes "S" becomes "AD" the rows stay the same. "G" becomes "R" becomes "AC".
I tried to use this code to do the incrementing, but it changes my data. It does not increment the formula.
https://www.extendoffice.com/documents/excel/3969-excel-increase-cell-reference-by-3.html
=OFFSET('Trim Summary'!$H$4,0,(COLUMN()-2)*11)
Hello WesleyGoodwin-6831 - Use the Offset Function as per Formula described in Cell H2 - Sheet1 in the attached file.. Formula would be like below =IFERROR(IF((OFFSET('Trim Summary'!$H2,0,H$1,1,1))*100<101,REPT(CHAR(10),((OFFSET('Trim Summary'!$H2,0,H$1,1,1))*100)/4)&CHAR(10)&(OFFSET('Trim Summary'!$H2,0,H$1,1,1))*100&"%"&" "&TEXT((OFFSET('Trim Summary'!$H2,0,H$1-1,1,1)),"MMM-DD"),"?"),"NA")
After I update the $H2 to H36 it gives me the wrong data. It gives me 0% and Jan-00. It should be 0% and Jul-27, and 50% Aug-07.
@Wesley Goodwin
Is there any update?
So, the answers do fill in the formulas. My problem is that I want people to be able to create other columns with the same equations just taken from a different system in the 'Trim Summary' tab. So, I want Tab 'Dashboard' Column "B" to have its equations filled in by tab 'Trim Summary' Column "H" and column "G". Then column "C" of 'Dashboard' to have its equations filled in by 'Trim Summary' Column "S" and column "R".
When I drag all the proposed equations it just changes the column in 'Trim Summary' by 1 column. So, if I have the equation in 'Dashboard' column "B" then drag the equation to column "C" the equation goes from 'Trim Summary' column "H" to column "I". I need the equation to go from column "H" to column "S" when I drag the equation to auto-populate. When I drag two columns of equations it only moves 2 columns on the 'Trim Summary'. Even if the two equations are already set up 11 columns apart.
Sorry, I wrote extra equals, I will edit my previous reply. In addition, I created a sample as following, and this formula works fine. I’m not sure, whether the result of the following gif is your requirement. Any misunderstanding, please let me know.
Please try to modify H$36 in formula to be INDIRECT(ADDRESS(36,COLUMN(H$36)*11-80,2))
Change G$36 be =INDIRECT(ADDRESS(36,COLUMN(G$36)*11-70,2))
Here is the gif of my sample and formula.
The formula in my sample is:
=IFERROR(IF(INDIRECT(ADDRESS(36,COLUMN(Sheet1!H$36)*11-80,2))*100<101,REPT(CHAR(10),(INDIRECT(ADDRESS(36,COLUMN(Sheet1!H$36)*11-80,2))*100)/4)&CHAR(10)&INDIRECT(ADDRESS(36,COLUMN(Sheet1!H$36)*11-80,2))*100&"%"&" "&TEXT(INDIRECT(ADDRESS(36,COLUMN(Sheet1!G$36)*11-70,2)),"MMM-DD"),"?"),"NA")
Well, first I had to delete the '=' to get the equation to start. Then I am getting 0% and Jan-00 for all of them.
@Wesley Goodwin
Thanks for your sample. Now I modify my formula again, please check it.
=IFERROR(IF(INDIRECT("'Trim Summary'!"&ADDRESS(36,COLUMN('Trim Summary'!H$36)*11-80,2))*100<101,REPT(CHAR(10),(INDIRECT("'Trim Summary'!"&ADDRESS(36,COLUMN('Trim Summary'!H$36)*11-80,2))*100)/4)&CHAR(10)&INDIRECT("'Trim Summary'!"&ADDRESS(36,COLUMN('Trim Summary'!H$36)*11-80,2))*100&"%"&" "&TEXT(INDIRECT("'Trim Summary'!"&ADDRESS(36,COLUMN('Trim Summary'!G$36)*11-70,2)),"MMM-DD"),"?"),"NA")
Ok, actually tried trouble shooting it more, and it does work. I was looking at the column in the equation. Not the data it was putting in the cell. Thank you
increment by 11：Only the formula,
11* for every 11 columns (x(+1)).
If there is a real thing, it may be possible to process it better.
G/H
=IFERROR(IF(('Trim Summary'!H$36)*100<101,REPT(CHAR(10),(('Trim Summary'!H$36)*100)/4)&CHAR(10)&'Trim Summary'!H$36*100&"%"&" "&TEXT('Trim Summary'!G$36,"MMM-DD"),"?"),"NA")
R/S
=IFERROR(IF(('Trim Summary'!(OFFSET($H36,0,11))*100<101,REPT(CHAR(10),(('Trim Summary'!(OFFSET($H36,0,11))*100)/4)&CHAR(10)&'Trim Summary'!(OFFSET($H36,0,11)*100&"%"&" "&TEXT('Trim Summary'!(OFFSET($G36,0,11),"MMM-DD"),"?"),"NA")
AC/AD
=IFERROR(IF(('Trim Summary'!(OFFSET($H36,0,11*2))*100<101,REPT(CHAR(10),(('Trim Summary'!(OFFSET($H36,0,11*2))*100)/4)&CHAR(10)&'Trim Summary'!(OFFSET($H36,0,11*2)*100&"%"&" "&TEXT('Trim Summary'!(OFFSET($G36,0,11*2),"MMM-DD"),"?"),"NA")
AN/AO
=IFERROR(IF(('Trim Summary'!(OFFSET($H36,0,11*3))*100<101,REPT(CHAR(10),(('Trim Summary'!(OFFSET($H36,0,11*3))*100)/4)&CHAR(10)&'Trim Summary'!(OFFSET($H36,0,11*3)*100&"%"&" "&TEXT('Trim Summary'!(OFFSET($G36,0,11*3),"MMM-DD"),"?"),"NA")
Here is the same question on google docs with a copy of the file.
https://support.google.com/docs/thread/60670962?hl=en
Could you please check this link? I could not access this copy.
@Wesley Goodwin ,
Please check whether my formula is helpful.