Get data based on where another datapoint is

Hi,

I need help with a formula I´m writing for work. I have already come up with a formula that checks for unique project names across 12 different pages, and if there are duplicates it only takes the one from the first page it checks. The row under then takes into account which project names have already been picked and takes the next unique one.
I need help with a fomula that gathers data (for example) 3 collumns to the right and 6 rows up from every unique project name square mentioned above. I can´t get the INDEX function to work scince I cannot know which page it has to gather the data from. I do however know which collumn and that there are 5 rows in this collumn where the projectnames can be found. I am clueless on what to do, I know that i can brute force it with the same 30 line code that checks for unique project names but it will take hours, there must be an easier way.

It's difficult to envisage how your data is laid out, so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Here is a sample excel file

Wow!

You could have a very similar formula in B27 of the Ack sheet, but instead of returning the project name you can return the appropriate sheet name, i.e. it would start off like this:

=IF(Dec!A19<>"","Dec",IF(Nov!A19<>"","Nov",IF(Nov!A29<>"","Nov" … etc.

Then you could use an INDEX/MATCH formula together with INDIRECT to retrieve the data that you need.

Hope this helps.

Pete

Hi,

thank you for your help so far, however i cannot get my head around how to construct the data gathering with INDEX/MATCH with INDIRECT. I am somewhat of a begginer when it comes to more advanced formulas.

Best regards
Erik

Please confirm some details that your sample file suggests, so that we can look at simplifying your monster formulae.

Does each month always have 5 Projects in it, or could there be more (or fewer)?
Do you only have the project names in column A of the monthly sheets, or could there be other data in that column?

Pete

Each month could have fewer projects but not more than 5. The project names are always in the A column, but there is more data in the same column. However, the project names are always either on A19, A29, A39, A49 or A59.

Erik

Hi Erik,

I've worked out a way of extracting your unique project names, without needing those massive formulae that you used (by using a number of simpler formulae in helper columns). I just need to extract the data from the relevant cells and I'll be able to post the file to you. I have some visitors with me at the moment, so it might be later on when I send the file through.

Pete

Hi Erik,

In the attached file I've set up a new sheet (just called Sheet1) which extracts the data that you require. I listed the 12 sheet names in column A in the order you want to use them, and then this formula in B2:

=COUNTA(INDIRECT("'"&A2&"'!A19"),INDIRECT("'"&A2&"'!A29"),INDIRECT("'"&A2&"'!A39"),INDIRECT("'"&A2&"'!A49"),INDIRECT("'"&A2&"'!A59"),INDIRECT("'"&A2&"'!A69"),INDIRECT("'"&A2&"'!A79"))

counts the number of project names that occur in the particular cells of each sheet. The formula will count up to 7, and I've deleted some of those in some sheets so you can see some variety. I put zero in cell C1 (important), and then this formula in C2:

=B2+C1

just gives a cumulative number of project names. These two formulae are copied down to the bottom of your list of sheet names, and I have coloured this block green.

The next block (purple) expands the data depending on the number of project names. This formula in E2:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1)-1,C:C)+1))

repeats the sheet names for as many times as there are project names in that sheet. In addition, I have recorded the row where each name will occur using this formula in F2:

=IF(E2="","",(ROWS($1:1)-INDEX(C:C,MATCH(ROWS($1:1)-1,C:C)))*10+9)

and then the actual project name is returned with this formula in G2:

=IF(E2="","",INDEX(INDIRECT("'"&E2&"'!a:a"),F2))

The first occurrence of a particular sheet name is obtained using this formula in H2:

=IF(E2="","",IF(COUNTIF(G$2:G2,G2)=1,MAX(H$1:H1)+1,"-"))

and this allocates a sequential number to each particular name. These four formula need to be copied down to cover the maximum number in column C, to ensure that all the data is picked up, so I've copied down to row 70.

The unique project names can then be listed in column J, with this formula in J2:

=IFERROR(INDEX(G:G,MATCH(ROWS($1:1),$H:$H,0)),"")

To make it easier for the final formula (in M), I've used columns K and L as further helpers (coloured blue), with this formula in K2:

=IFERROR(INDEX(E:E,MATCH(ROWS($1:1),$H:$H,0)),"")

to return the sheet name where the project name first occurs, and this one in L2:

=IFERROR(INDEX(F:F,MATCH(ROWS($1:1),$H:$H,0)),"")

to return the row number on that sheet where the name occurs:

=IFERROR(INDEX(F:F,MATCH(ROWS($1:1),$H:$H,0)),"")

Your final requirement was to return the data from 3 columns across and 6 rows above where the project name occurred, so I think that means column D in rows 13, 23, 33 etc. I've put some data in the appropriate cells on the monthly sheets, so that you can see where that data comes from, and this formula is used to extract that data in M2:

=IFERROR(INDIRECT("'"&K2&"'!D"&L2-6),"")

The formulae in this block has been copied down to row 20 in the attached file, and you can use it as is - you can hide any of the coloured columns if you are only interested in the project names and data.

I haven't needed any of the formulae that you had in the Ack sheet, so you could delete that if you wish and rename Sheet1 to Ack. However, I notice that you have left 26 blank rows on the Ack sheet (presumably for other things), so if you want to use it you can have this formula in A27 of that sheet:

=INDEX(Sheet1!J:J,ROWS($1:2))&""

and this one in B27:

=INDEX(Sheet1!M:M,ROWS($1:2))&""

copied down until you get blanks, and in that case you could hide Sheet1 as it is fully dynamic.

Hope this helps.

Pete

Hi Pete,

Thank you so very much for this help, if there´s any way i could return the favor just tell me how. It seems to be working just as i inteded it to, I however have one small problem, which i guess is really easy to fix in the H collumn. Is it possible for it not to count up if the project name is " "? If there for some reason someone hasen't filled out the project name i would want it not to get added to the list of unique project names.

Best regards
Erik

Originally Posted by ErikWi

... If there for some reason someone hasen't filled out the project name i would want it not to get added to the list of unique project names ...

It's not so easy to solve that through the formulae - it would be easier for you to ensure that it did not happen, especially as I'm going away for a few days so I can't look into it now.

Originally Posted by ErikWi

... if there´s any way I could return the favor just tell me how ...

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete