Hello, I need to extract data from a column on a separate tab (based on criteria from another cell) but I need it to skip blanks (consolidate the data in the destination column)
Please see attached / I need:
P/N in "Plating cost calculator" (column A) to automatically populate P/N from "Eastern" Part# (Column C) if there is an X on "Eastern" Ready for plating process (Column T) But I don't want blanks in between P/N in the "plating cost calculator" (Column A)
Guess
Plating Cost Calculator sheet Cell A5:A10 formular
Thanks. However I am finding that the formula is changing by itself in from 200 to 199 etc. in INDEX(Eastern!C:C,SMALL(IF(Eastern!$S$2:$S$200
INDEX(Eastern!C:C,SMALL(IF(Eastern!$S$2:$S$200="x",ROW($2:$200),4^8),ROW(1:1)))&""
yy defines a name to increase according to the increase in the number of rows in column C of your worksheet (Eastern), and it is directly attached to you to help you complete it.
Last edited by AliGW; 08-28-2020 at 02:15 AM. Reason: Please don't quote unnecessarily!
Hello wk9128. I really appreciate your help. However, I am unable to open the latest attachment that you sent because it is needing repair and I am still unable to open. Are you able to resend or post the formula?
Re-upload and Downgrade Office version 2003
Last edited by AliGW; 08-28-2020 at 02:15 AM. Reason: Please don't quote unnecessarily!
@wk9128
Sorry for off-topic interjection, but you seem to do this every time you provide help:
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Noted
Got it
Thanks for the help! I needed to verify that no other users were going to have issues. I believe this problem is solved. Thanks again.
May I reopen this post to build one more function into my spreadsheet? I need to add a source to extract the data in the attached spreadsheet.
Currently, the tab "Plating Cost Calculator" pulls data (Eastern tab column C) into column A (Plating cost calculator) from the "Eastern tab" when "x" is entered in corresponding column S (Eastern tab)
Formula is: {=INDEX(Eastern!C:C,SMALL(IF(INDIRECT("Eastern"&"!S2:S"&200)="x",ROW(INDIRECT("2:"&200)),4^8),ROW(1:1)))&""}
I need to also pull into the same info from the "millroom_laser" tab (data from column C based on column R).
Is it possible to pull from both sources into the same destination?
Please select cell A5 and paste the following array entered formula** into the formula bar:Formula:
Please Login or Register to view this content.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. Do this before copying the formula to other cells.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
This is great but i am having the same issue as before where the "16" in "Eastern!$S$2:$s$16" is changing automatically. I believe it was remedied when adding the "INDIRECT" function but I am unsure how to tie it all together.
Last edited by Dawn Clark; 09-24-2020 at 02:29 PM.
Are any changes being made to the Eastern sheet that precedes the formula changing itself?
I really am not sure why this changes. All I know is the previous person helped me fix it. Is there a way to use your formula without discarding the INDIRECT formula as per below?
=INDEX(Eastern!C:C,SMALL(IF(INDIRECT("Eastern"&"!S2:S"&200)="x",ROW(INDIRECT("2:"&200)),4^8),ROW(1:1)))&""
Note that indirect is a volatile function, meaning that it reevaluates whenever a change is made to another cell. That has the potential of causing the program to run slowly and sometimes stop. To me that better strategy would be to find under what conditions the formula makes the change and work on that.
That said here is a modification to the array entered formula (see post #11) for cell A5 and down that locks the range using INDIRECT:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
I will address the INDIRECT later. Thanks for that info. I may need to find a better alternative. For now this works except, I need it not to show a #ref error. I need it to be zero because i am calculating results from these arrays. Is there a solution for this?
Please try:Formula:Please Login or Register to view this content.
Edit: This is more computationally efficient:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Last edited by JeteMc; 09-24-2020 at 08:46 PM. Reason: Added better formula
Thank you so much!! I just returned to the office to use this so this is the first day attempting it. Not sure if I am the one to mark this post as solved but I would like to wait a few days to do so in order to make sure other users don't have issues.
You're Welcome and thank you for the feedback. If the other users don't have issues, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
Can I re-open this thread? Seems I need to make changes again and I do not understand the formula so I need to get help every time I make a change. (Sorry) I am open to other solutions if you have any suggestions.
in the attached spreadsheet I need operators to be able to enter "x" into the column named "ready for chem film process" on the first 3 tabs: "CNC-1", "CNC-2" & Mill-room_Laser", and I need the corresponding P/N in those lines with the "x" to auto-populate on the 4th tab "AA-1 Plating Cost Calculator". If it makes it easier, they do not have to be in ascending order. They just need to populate the next available P/N cell on the calculator.
I have the current formula that pulls info from 2 tabs (Mill-room_Laser & Eastern) onto a tab called "AA-1 Plating Cost Calculator". But now I need it to pull info from 3 tabs: " Millroom_Laser", "CNC-1", & "CNC-2"
Current formula: (Eastern in this formula is now "CNC-1")
=IF(ROW(A1:A1)>SUM(COUNTA(INDIRECT("Eastern!$u$2:$u$200")),COUNTA(INDIRECT("Millroom_Laser!r$2:r$200"))),0,IF(ISERROR(INDEX(INDIRECT("Eastern!d$2:d$200"),SMALL(IF(INDIRECT("Eastern!$u$2:$u$200")="x",ROW(A$2:A$200)-1,4^8),ROWS(A$1:A1)))),INDEX(INDIRECT("Millroom_Laser!c$2:c$200"),SMALL(IF(INDIRECT("Millroom_Laser!r$2:r$200")="x",ROW(A$2:A$200)-1,4^8),ROWS(A$1:A1)-COUNTA(INDIRECT("Eastern!$u$2:$u$200")))),INDEX(INDIRECT("Eastern!d$2:d$200"),SMALL(IF(INDIRECT("Eastern!$u$2:$u$200")="x",ROW(A$2:A$200)-1,4^8),ROWS(A$1:A1)))))
Perhaps breaking the process down will make it more understandable.
K1:M1 are populated using the other sheet names.
K2:M2 are populated with the number of X's on each sheet using: =COUNTA(INDIRECT("'"&K1&"'!U2:U50")) and =COUNTA(INDIRECT("'"&M1&"'!R2:R50")) since the Millroom_Laser sheet is different.
K3:M3 are populated with the cumulative numbers from row 2 using: =SUM($K2:K2)
K5 and down are populated with the name of the sheet from which the P/N will be recorded using: =IFERROR(INDEX(K$1:M$1,AGGREGATE(15,6,(COLUMN(K$1:M$1)-COLUMN(J$1))/(K$3:M$3>=ROWS(A$1:A1)),1)),"")
L5 and down are populated with the column and rows which could be populated with X's using: =IF(K5="","",IF(K5="Millroom_Laser","R2:R50","U2:U50"))
Finally the P/N's are populated using: =INDEX(INDIRECT("'"&K5&"'!C2:C50"),AGGREGATE(15,6,(ROW(C$2:C$50)-ROW(C$1))/(INDIRECT("'"&K5&"'!"&L5)="x"),COUNTIFS(K$5:K5,K5)))
There may be ways to make the process less volatile using Power Query, however we need know the version of Excel being used as opposed to the version of Windows.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks