Hi all
I am trying to do a vlookup and sum multiple columns #s based on a moving target month, comparing them to the current year.
IE currently August, so this years YTD totals against last years july and august totals summed.
Hope this make sense. Attached is a dummy spreadsheet to help me work this out.
Thanks
Ηι
Can you pls give us some expected results according your sample example?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
In C3 Cell of Client Sheet
Formula:
Please Login or Register to view this content.
Drag it down...
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Please try this file.
Note: If you want to add a sheet name like 2011,2014 etc., then you have to use the same numbers in the Column title & drag the formula.
Last edited by ramananhrm; 08-20-2013 at 02:35 AM.
Please click 'Add reputation', if my answer helped you.
ok so on the first sheet under 2012 heading, i need to calculate (sum) the july to august 2012 figures for the relevant top 2013 client. something like vlookup client in column A, sum columns july to august, or july to december in sheet 2012. IE based on current month.
the month at the top left corner will change each month as it is a today() formula. So next month the 2012 figures should auto calculate july to september to relevant client on that row.
Sorry was busy typing reply to Fotis1991.
*Sixthsense - Thanks for the quick solution.
**Ramananhrm - Unfortunately it only works if the full years figures are there. Thanks anyway,
OFFSET tends to be a volatile function. Use INDEX instead
=SUM(INDEX('2012'!$B$3:$B$9,MATCH(A3,'2012'!$A$3:$A$9,0),0):INDEX('2012'!$B$3:$M$9,MATCH(A3,'2012'!$A$3:$A$9,0),MATCH($A$1,'2012'!$B$2:$M$2,0)))
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi,
Just a thought but have you considered making cell A1 on your Client sheet (showing current month) a Drop-down list then you could select any current or prior month to get the result you seek using the suggested formulas?
Cheers,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks