Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using this formula:

=IF(A3=A2,
    pull(CONCATENATE("'F:\ITO\",A3," Project","\", "[",A3,".xlsx]Server Config'!","A5")),
    pull(CONCATENATE("'F:\ITO\",A3," Project","\","[",A3,".xlsx]Server Config'!","A4")))

Where Column A contains the project number. Not all project have the same number of lineas, and every time the project change it have to restart counting for A4.

How can I do to say this formula, or another that if the number of the project is the same that the previous row, continue counting with A5, A6..... till the time the project number will change, in this case it has to start again with A4?

The structure of this table is the follow:

A2 = "Project Number"

B2 = =pull(CONCATENATE("'F:\ITO\",A2," Project","\","[",A2,".xlsx]Server Config'!","A4"))

A3 = "Project Number"

B3 = =IF(A3=A2,pull(CONCATENATE("'F:\ITO\",A3," Project","\","[",A3,".xlsx]Server Config'!","A5")),pull(CONCATENATE("'F:\ITO\",A3," Project","\","[",A3,".xlsx]Server Config'!","A4")))

Every project is the reference to one file, and every file contains different information in the same cells.

share|improve this question

1 Answer 1

So basically you want to count the number of previous cells in the A column that reference the same project, and then choose the appropriate cell from the external document from that count?

How about using COUNTIF to count the project references, and then use that count to add to "A4"? If you put a dollar sign in front of the first row number that will remain static while you copy the formula down, e.g. always counting from the top of the sheet down to the current row.

=COUNTIF(A$1:A1;A1)
=COUNTIF(A$1:A2;A2)

The final formula would be something like

B2 = =pull(CONCATENATE("'F:\ITO\",A2," Project","\","[",A2,".xlsx]Server Config'!","A", 3+COUNTIF(A$2:A2;A2)))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.