Hello,
I have a complicated situation with Excel since I need a formula that can help me SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet and try to help me a formula that can retrieve the same information.
Thanks a lot Formula Test.xlsx
Plop this into C12 and copy over.
=SUM(OFFSET('Pull 1'!$A$7:$A$25,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(A1)-1))
This takes the range of A7:A25, and offsets it by the number of columns where it finds a match for the validation, and then sums. The column(a1) part just increments the offset by 0, 1, & 2 to pull the other parts of the group.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Thank you for the formula. I have adjusted your formula a bit to serve my purpose. I have another question. If I have another pull 2 worksheet in consist of more product family, so my dropdown list now expanded. How can I retrieve data from 2 lookup worksheets now. I understand that why I don't put 2 pull worksheet together, this data I retrieve automatically via smartview so it is limited in columns I have to seperate them into 3 different spreadsheet to look up entire product family. I attached a new sample worksheet with new dropdown list. Please help, thanks.Formula Test.xlsx
You can always just sum them together, you just need to be careful that data pull is lined up the same way every time.
In your attached example, the data begin on row 7 for Pull 1 and row 6 for Pull 2.
In the perfect world where you force the line to line up, it's a simple matter of repeating the given equation and just changing the tab references, and then also insulating them within IFERRORs to evaluate mismatches to zero.
B12:
=IFERROR(SUM(OFFSET('Pull 1'!$A$6,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 1'!A:A))),0)+IFERROR(SUM(OFFSET('Pull 2'!$A$6,0,MATCH(Summary!$B$9,'Pull 2'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 2'!A:A))),0)
B13:
=IFERROR(SUM(OFFSET('Pull 1'!$A$7:$A$25,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(F1)-1)),0)+IFERROR(SUM(OFFSET('Pull 2'!$A$7:$A$25,0,MATCH(Summary!$B$9,'Pull 2'!$B$3:$BG$3,0)+COLUMN(F1)-1)),0)
And copy both over.
For a third, repeat the same process.
B12:
=IFERROR(SUM(OFFSET('Pull 1'!$A$6,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 1'!A:A))),0)+IFERROR(SUM(OFFSET('Pull 2'!$A$6,0,MATCH(Summary!$B$9,'Pull 2'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 2'!A:A))),0)+IFERROR(SUM(OFFSET('Pull 3'!$A$6,0,MATCH(Summary!$B$9,'Pull 3'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 3'!A:A))),0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks