LinkBack URL
About LinkBacks
Hi All!
I have a file that I'd like to sales numbers from another tab within the excel file.
Ex:
excel.JPG
I'd like the numbers be pulled by the SKU # and display under the month in the Sales LY row.
Looking for a formula.
THANK YOU!
Hi All!
I have a file that I'd like to sales numbers from another tab within the excel file.
Ex:
excel.JPG
I'd like the numbers be pulled by the SKU # and display under the month in the Sales LY row.
Looking for a formula.
THANK YOU!
VLOOKUP or SUMIFS
Edit: Actually not VLOOKUP, SUMIFS is likely your best choice, hard to say because I cant see source data. We also cannot deduce things like data type, format, underlying formulas/patterns, etc from a picture. A sanitized sample file would be best
My source data goes
QTY
H11 33
H22 51
Attach a sample workbook (not image).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I've attached. Hopefully it worked.
All I want is for my Data tab to feed into my forecast tab.
This should get you started. In D2:
=INDEX('October data'!C$2:C$4,MATCH(FORECAST!B2,'October data'!$A$2:$A$4,0))
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.
IN D2,
=IFERROR(SUMPRODUCT((INDIRECT("'"&D$1&" data'!$B$2:$B$9")=$A2)*(INDIRECT("'"&D$1&" data'!$A$2:$A$9")=$B2)*(INDIRECT("'"&D$1&" data'!$C$2:$C$9"))),"")
then copy to other cells.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Perfect! Thank you! Any chance either you can explain the detials of each part of the formula?
THANK YOU!
Which one?
Why did you use Index, or what does indirect mean in a formula.
Actually the second one doesn't work, it pulls the number 2 in each yellow row.
Here's an explanation of mine:
=INDEX('October data'!C$2:C$4,MATCH(FORECAST!B2,'October data'!$A$2:$A$4,0))
Find a value in 'October data'!C$2:C$4 ...
=INDEX('October data'!C$2:C$4,MATCH(FORECAST!B2,'October data'!$A$2:$A$4,0))
... that is on the row in 'October data'!$A$2:$A$4 that matches with FORECAST!B2.
=INDEX('October data'!C$2:C$4,MATCH(FORECAST!B2,'October data'!$A$2:$A$4,0))
Make sure it's an exact match.
The other formula offered works perfectly - you do need to add tabs for November and December, however! Perhaps you copied and pasted the formula instead of the whole cell? If so, you will need to adjust the references for each row. Much easier if you simply copy and paste the whole cell, then Excel will make the changes for you.
What is the other file had months at the top. Is it possible to then match the SKU by month to the second tab?
In D2 and copy across:
=IFERROR(SUMPRODUCT(('October data'!$B$2:$B$4=$A2)*('October data'!$A$2:$A$4=$B2)*('October data'!$C$1:$E$1=D$1)*('October data'!$C$2:$E$4)),"")
Select D2 to F2 - copy - select D5 to F5 - paste - select D8 to F8 - paste again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks