Link data from multiple data sheets

I am trying to link data from multiple sheets in a file into one sheet. This is the following I want to achieve.

1) I want to pull data from the tab - Tab 1 (in the attached sheet) from column F only if the column E cell has 3, into the "Plan" Sheet in cell D5. I want all the 3's information from Tab 1 (F12, F14, F16 and F17) to be in the same cell D5 with alt+Enter spacing. The trick is these cells may not be always 3 they can be either 1,2,3. So the function needs to go through the entire range E10:E69 to find where there are 3 in the E column and then return the corresponding data from the F column to the "Plan" sheet in Tab 1.

I would really appreciate if someone can help me on this, it has been a nightmare to figure this out.

Thank you friends

This is called string concatenation, and it's not built into Excel natively. You have to add it.

String concatenation is conditionally gathering text strings into a single cell and separating them by some "delimiter". The most common delimiter is a comma, but your use of Alt-Enter is essentially the same thing.

First, here's the special user-defined function (UDF) you will need to add to your sheet:

Please Login or Register  to view this content.

=======
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
========

This UDF is used very much like a SUMIF(). First parameter is the evaluation range, the second is the value to match, the third is the return values range. The fourth is the "delimiter" to separate each matching value, and the fifth is TRUE/FALSE if you want to suppress duplicate answers.

So, on in D5, the formula would be:
=CONCATIF('Tab 1'!E10:E69, 3, 'Tab 1'!F10:F69, CHAR(10), FALSE)

You'll have to format those cells with WRAP TEXT turned on.

I can already see your next question, how to have one formula down the column D that will grab the correct tab without having to customize each formula. You'll need INDIRECT() for that. The replacement D5 formula would be:

=CONCATIF(INDIRECT("'" & A5 & "'!E10:E69"), 3, INDIRECT("'" & A5 & "'!F10:F69"), CHAR(10), FALSE)

Attached is your sample sheet with the UDF installed. I've shown the two versions of the formula in the sheet, too.

The 4th parameter is your delimiter. You'll have to play with that part. If you want a text delimiter like a comma, you would list like any text string in a formula in quotes, like this

", "

We used CHAR(10) for our delimiter because that's the ASCII code for the soft-return. If you can determine the ascii code for the characters you want, we can fiddle with it, but hey...it's working. I say call it a win.

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

You know, I got intrigued by your 1) 2) 3) idea, and tweaked the code a little to accomplish that. Replace the UDF I gave you with this one and you'll get the numbered list you were looking for:

Please Login or Register  to view this content.