Hi,
I am needing help with a formula that i can place in the single column to read from the 4 other columns containing the data that is out of order but place it in order under the single column.
Any thoughts on how i can achieve this?
Hi,
I am needing help with a formula that i can place in the single column to read from the 4 other columns containing the data that is out of order but place it in order under the single column.
Any thoughts on how i can achieve this?
Last edited by jleal; 04-23-2014 at 12:30 PM.
I really need help with this one.
I am trying to take data from columns on 4 different sheets that are not right underneath each other but place them one underneath each other on a different sheet.
If i typed any number under Column 1 on sheet 1 then it places it first on Final sheet then if i was to type another number under neath sheet 2 in the same column it would be placed directly under the first input from sheet 1 onto the final sheet with no spaces in between the two.
Hi and welcome to the forum
1st, please note that forum rules require you to wait at least 24 hours before bumpy/asking for help.
2nd, it would help if you provided a few samples of what your expected outcome would look like.
Having said that, give this a try, copied down....
=LARGE('Sheet (1):Sheet (4)'!$I$5:$K$46,ROW(A1))
It will put ALL the values in highest to lowest ranking. If you want it t small to bih, use SMALL() instead
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I apologize for jumping the gun on bumping the thread. I have attached an example of what i am trying to accomplish on getting info from the other 4 sheets to the final sheet in order. The formula you gave me worked but it was putting the values in order from big to small/small to big but instead i am wanting to keep the values that are in each cell being transferred to the final sheet.
OK I had a feeling you wanted the matching rows to stay together. Different approach...
1. create a small table containing ALL your sheet names
2. Give that a range name of sheettabs
3. in C11, copied down...
=IFERROR(SMALL('Sheet (1):Sheet (4)'!$I$5:$I$46,ROW(A1)),"")
4. in D11, copied down...
=IF(C11="","",SUMPRODUCT(SUMIF(INDIRECT("'"&sheettabs&"'!I5:I46"),C11,INDIRECT("'"&sheettabs&"'!j5:j45"))))
5. In E11 copied down...
=IF(C11="","",SUMPRODUCT(SUMIF(INDIRECT("'"&sheettabs&"'!I5:I46"),C11,INDIRECT("'"&sheettabs&"'!K5:K45"))))
This is exactly what i needed and it is working very well but my question now is how do i add more cells for it to read from for example
=IFERROR(SMALL('Sheet (1):Sheet (4)'!I5:I46,[/B]I60:I101,I115:I156 and so on eventually utilizing all the blue cells on each sheet accordinglyROW(A1)),"")
I have attached an example of what i am needing now because the previous formula worked real well but was not reading entire blue highlighted areas on each sheet just I4-I46 and i was needing it to read the others on the entire sheet/all of the sheets as well.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks