LinkBack URL
About LinkBacks
Hello. I want to create a lookup formula that will return monthly values and YTD values from another sheet in a workbook. I've attached a workbook with two worksheets.
Hello. I want to create a lookup formula that will return monthly values and YTD values from another sheet in a workbook. I've attached a workbook with two worksheets.
1st, I suggest you use real dates, instead of month name/year, it will make the calcs much easier
2nd, make sure your names mact across sheets. Sheet1 uses "Sales 1" (with a space), but sheet 2 uses "Sales2" No space
2nd, assuming you now have real dates, starting 3/1/14 to 12/1/14...
for the month
=INDEX(Sheet1!$F$3:$O$5,MATCH(Sheet2!$B4,Sheet1!$C$3:$C$5,0),MATCH(Sheet2!C$3,Sheet1!$F$2:$O$2,0))
For the year, something like...
=SUMIFS(Sheet1!$F3:$O3,Sheet1!$F$2:$O$2,">="&Sheet2!C$3,Sheet1!$F$2:$O$2,"<="&Sheet2!D$3)
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
Thanks. Why are you starting from F3? I'm getting an error.
I assumed that was where your data started. If you only start from Sales 2, then change the range to F4. That is just to indicate the top-left corner of your data range.
I also got an error, until I entered Sales 1 in C3 (figured you had forgotten to enter it)
I figured out how to modify your first formula for the month, but I have no idea what you are doing with the sumif formula. THe references are all off I think.
This is how I tried to redo your second formula but it didn't work. =SUMIFS(Sheet1!$D3:$O3,Sheet1!$D$2:$O$2,">="&Sheet2!C$3,Sheet1!$D$2:$D$2,"<="&Sheet2!D$3)
No, the references for the SUMIFS were not off, they were based on teh rows on both sheets being in the same sequence.
Perhaps this would be better....
=SUMPRODUCT(--(Sheet1!$F$2:$O$2>=Sheet2!C$3)*(Sheet1!$F$2:$O$2<=Sheet2!D$3)*(Sheet1!$C$3:$C$5=Sheet2!$B4)*(Sheet1!$F$3:$O$5))
Never mind. Figured it out. Thanks.
Glad you got it resolved, thanks for the rep
Hey Mr. Dibbins. I realized that this formula isn't dynamic. SUMIFS(Sheet1!$D3:$O3,Sheet1!$D$2:$O$2,">="&Sheet2!C$3,Sheet1!$D$2:$O$2,"<="&Sheet2!D$3)
I'm using it for another sheet and if I want to drag it down it doesn't work because the rows refer to different rows in the data sheet. This one just refers to the next row.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks