Index, match, dynamic and connected without sorting helper column non-adjacent index match

[Underling]

Hello All,

Thanks in advance for the help and sorry if it's a complicated or confusing spreadsheet to look through. I've been searching through the posts to try to answer my questions and have gathered a lot of useful information but now I just need a little bit of help putting it all together. This link is a dead one, but I'm referencing it because he partially answered one of my questions.


http://www.excelforum.com/excel-form...e-results.html

You will probably need to use Match and Index rather than VLOOKUP, which will always just return the first value. The only way around this is to set a dynamic range, which is more difficult.

http://www.mrexcel.com/articles/exce...ndex-match.php

After you match the item, simply add a +1 to the index of the second item and it will pull in the information one cell down.


Match looks like: =MATCH(Value, array, < > or =)

Index Looks like: =INDEX(Array, Row #,[column #])

To use them together, nest match inside of index as the row #.

=INDEX(A1:A30, MATCH(part number, table of part numbers, 0))

For the next part number, if it is right below it,

=INDEX(A1:A30, MATCH(part number, table of part numbers, 0)+1)

If the second part # is not one cell below it, this will be more complicated, and you should post a sample of your issue.

MY EXAMPLE FILE
Index Match If Rows Max Count If Lookup CONFUSED_macro 3.xlsm

Question #1
NON-ADJACENT INDEX MATCH +1 LOOPHOLE???
The incoming funds section of my spreadsheet (tab=OutPut Sheet and cell=A56:A68) is referencing the count if helper columns beginning at B71 and returning the project #'s from the Office_List table on the Transfer Questionaire tab. All of the project numbers have the same OFFICE NAME. I'm able to get the correct COUNT of output in Incoming Funds Project, but they are returning the incorrect project numbers. It is only returning the FIRST project number from the list to which it is refering, whereas I want it to return ALL of the project numbers. The post above refers to a solution, but states that the data must be adjacent to eachother and that if the data are not adjacent, then it gets more complicated and you should post an example to solve this. My data is not next to eachother and it wouldn't be convienient to make it right next to eachtoher each time, so how can I work around this?

I DO NOT KNOW WHERE THE DATA WILL BE SO HOW DO I GET AROUND THIS LOOPHOLE, IT MIGHT BE CONSTANTLY CHANGING!


Incoming Funds (Consultants)
Project Consultant Office Payroll Travel Admin Total
10001
10001
10001
10001
10001
10001
10001
10001
10001
10042
FALSE
FALSE
FALSE


Question #2
CONNECTING WITHOUT SORTING / DYNAMIC / CONNECTED
Output Sheet first table refers to the project tab and uses index match to pull the information with the use of a helper column. This requires sorting the data. Is there any way that I can pull this information without using the helper column, or with using the helping column but wihtout having to worry about sorting the data in order to do so? I would also prefer the total and sum columns to be right at the end of the data as opposed to far at the bottom (if there were fewer occurences for a particular office). I am also worried about the user forgetting to add more columns as the list grows (for example, 150 offices instead of 25 offices).

Project Consultant Office Payroll Travel Admin Total
10001 Consultant 1 Office 1 15201 1500 250 16951
10004 Consultant 4 Office 1 15207 1503 253 16963
10008 Consultant 8 Office 1 15215 1507 257 16979
10012 Consultant 12 Office 1 15223 1511 261 16995
10017 Consultant 17 Office 1 15233 1516 266 17015
10022 Consultant 22 Office 1 15243 1521 271 17035
10026 Consultant 26 Office 1 15251 1525 275 17051
10030 Consultant 30 Office 1 15259 1529 279 17067
10034 Consultant 34 Office 1 15267 1533 283 17083
10038 Consultant 38 Office 1 15275 1537 287 17099
10042 Consultant 42 Office 1 15283 1541 291 17115
10046 Consultant 46 Office 1 15291 1545 295 17131
10050 Consultant 50 Office 1 15299 1549 299 17147
10054 Consultant 54 Office 1 15307 1553 303 17163
10058 Consultant 58 Office 1 15315 1557 307 17179
10062 Consultant 62 Office 1 15323 1561 311 17195
10065 Consultant 65 Office 1 15329 1564 314 17207
10069 Consultant 69 Office 1 15337 1568 318 17223
10073 Consultant 73 Office 1 15345 1572 322 17239
10077 Consultant 77 Office 1 15353 1576 326 17255
10081 Consultant 81 Office 1 15361 1580 330 17271
10085 Consultant 85 Office 1 15369 1584 334 17287
10089 Consultant 89 Office 1 15377 1588 338 17303
10093 Consultant 93 Office 1 15385 1592 342 17319
10097 Consultant 97 Office 1 15393 1596 346 17335


382441 38708 7458 428607

Question #3
DYNAMIC AND CONNECTED, OFFSET ???
Same chart as above- Output Sheet first table refers to the project tab and uses index match to pull the information with the use of a helper column. Sometimes it pulls many items (25) and sometimes it pull fewer (5). Is there any way to make use of the offset function or some other dynamic function to reduce the number or rows and columns when the number of items needing to be drawn changes? I am also interested in connecting ALL THREE OF THESE CHARTS (MASTER LIST BY OFFICE, OUTGOING FUNDS, AND INCOMING FUNDS) so that they are adjacent, dynamic, connected, and running off the same formula. That is to say that when the MASTER LIST BY OFFICE ends, it would sum itself and then the formula would automatically begin the OUTGOING FUNDS table, and after that one is finished it would automatically begin the INCOMING FUNDS table. They don't necessarily have to be all in the same formula, as long as I can find a way to tell them to begin and end closer or further from eachother as the entries change, that would be great. I am interested in having it constantly set up as though it were a report (without manually deletions, as they might forget to add it back as they search for a new office). I am not sure if this is possible.



AGAIN, SORRY FOR THE LONG POST AND QUESTIONS. Hopefully, if this is resolved I may have some follow up questions to add to the same post if you experts don't mind answering them too. They would be mostly slight deviations of this question though.
Thanks so much,
Underling


P.S
MY EXAMPLE FILE
Index Match If Rows Max Count If Lookup CONFUSED_macro 3.xlsm

[dredwolf]

First off(from title) why no helper columns? (they can be hidden in final workbook, and generally make the calculations significantly faster)

[Underling]

Well, I have helper columns in here but I don't want them if they are working the way they are currently working now. Right now the helper columns have to be sorted adjacently and that's not going to be convenient for the users. I'm not altogether opposed to helper columns if they can work while non-adjacent, but because of the understanding level of the users, the simpler the formulas can be (less helper columns and references) the better.

[Underling]

Anyone have any idea or have i made it too confusing?

[Pepe Le Mokko]

Forum rules suggest waiting about 24 hrs before bumping

[Underling]

I was very close to 24 hours, sorry for not f0ollowing the suggestion. Does anyone know the solution to my question, or was my spreadsheet too big? =0

[dredwolf]

Well, I have 3 separate solutions to the 2nd Question (mainly because it's the first in your workbook )
See attached, I would post the formulas, but that means transferring ranges as I post, and I may miss something !

First solution is a single cell solution (no helper columns/cells required)
Second Solution uses the helper column ( sort of, it actually uses the MAX(range) cell on output sheet) to speed calculations a bit
Third solution uses the built in table abilities of Excel to give the desired result (Using Master List, (copied to Sheet1), Subtotals and Filter on Office #)

I also moved the Helper column to column H (and out of the table) on Master List, and made the reference to office # on Master List (J6) = Output Sheet (C1), to insure the values being looked at are the same
Also added another Office 2 entry at the end of the list, so that you can see my formulas work for unsorted data (to check, for first 2 solutions, Change C1 to Office 2, You should get 2 Entries for office 2 (Instead of 1, the formula you have will give that, but if you add another Office 1 Entry, you get some "weird results"))
Also, I left a row between the List, and the Sub-Totals for the first 2 solutions
A Note, You can carry first 2 solutions down as far as you need, BUT if you overwrite them with the other 2 questions answers, you may lose data, so decide the maximum lenght for your information, then keep outside those bounds for the other answers..you may have to use VBA to hide the blank rows, for printing purposes, or display purposes, but try not to overwrite the formulas that already exist, just because you have a certain image in mind that does not fit what your data is !

As for the Q1 and Q3 parts of your OP, I'm not really sure what YOU expect to see there, so I left that alone till you can give more info on those
(Especially not certain on why you need a custom Eval Function, Index's and Matches (or other ways of getting row/column numbers should work)) (Not that having your own Eval Function is bad,just wondering why you feel it needed here)?

Hope this helps