HOW TO INCREMENT WITH AN OFFSET
willymontana
Posted messages
10
Status
Member
-
willymontana Posted messages 10 Status Member -
willymontana Posted messages 10 Status Member -
Good evening everyone
I want cell B to be incremented as follows:
a shift of 3 for example
B1 = A1
B2 = A4
B3 = A7
B4 = A10
I have a formula that does this increment:
=INDIRECT("$A$"&((ROW($1:1)-2)*3+3)+1) but I am looking for another formula because this one requires all the files to be open. I tried the INDIRECT.EXT formula at first it worked but then it bugged and signaled errors, so if there is another simpler way to increment with a shift please show it to me. (especially since the A and B cells are not in the same workbook) I want to use &((ROW($1:1)-2)*3+3)+1 without writing INDIRECT because I don't want to work with this formula. Thank you in advance to everyone who tries to help me, I have developed a large application based on this formula so please help me.
I want cell B to be incremented as follows:
a shift of 3 for example
B1 = A1
B2 = A4
B3 = A7
B4 = A10
I have a formula that does this increment:
=INDIRECT("$A$"&((ROW($1:1)-2)*3+3)+1) but I am looking for another formula because this one requires all the files to be open. I tried the INDIRECT.EXT formula at first it worked but then it bugged and signaled errors, so if there is another simpler way to increment with a shift please show it to me. (especially since the A and B cells are not in the same workbook) I want to use &((ROW($1:1)-2)*3+3)+1 without writing INDIRECT because I don't want to work with this formula. Thank you in advance to everyone who tries to help me, I have developed a large application based on this formula so please help me.
3 answers
Hello,
I don't see any reference to other workbooks in your formula
But it's hardly possible to do without INDIRECT for a reference resulting from a concatenation
however, you can try using a macro in VBA
https://www.commentcamarche.net/faq/32961-vba-excel-lire-dans-un-classeur-ferme
Best regards
I don't see any reference to other workbooks in your formula
But it's hardly possible to do without INDIRECT for a reference resulting from a concatenation
however, you can try using a macro in VBA
https://www.commentcamarche.net/faq/32961-vba-excel-lire-dans-un-classeur-ferme
Best regards
Thank you for your response, I tried your example but it doesn't work. To be precise, here is my formula:
=INDIRECT("[EXEDENT.xlsb]Sheet1!$A$" & ((ROWS($29:29)-2)*111+111)+29)
and the link is: C:\Documents and Settings\walide\Desktop\STATISTIQUE 2013\DECISIONS STATISTIQUE 1ER SEMESTRE 2013\DECISION WALID
=INDIRECT("[EXEDENT.xlsb]Sheet1!$A$" & ((ROWS($29:29)-2)*111+111)+29)
and the link is: C:\Documents and Settings\walide\Desktop\STATISTIQUE 2013\DECISIONS STATISTIQUE 1ER SEMESTRE 2013\DECISION WALID