SOLVED
Home

Relating formula between 2 worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1297593%22%20slang%3D%22en-US%22%3ERelating%20formula%20between%202%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297593%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20date%20in%20cell%20C2%20in%20attached%20spreadsheet%20as%2001%2F03%2F2020.%20The%20values%20in%20cell%20F7%20to%20F12%20should%20reflect%20from%20%22B%22%20tab%20from%20cells%20%22AB%22%20to%20%22AD%22%20i.e.%20from%20Jan%20to%20Mar%20%26amp%3B%20from%20%22C%22%20tab%20from%20cells%20%22AE%22%20to%20%22AM%22%20i.e.%20from%20month%20April%20to%20Dec.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20date%20in%20cell%20C2%20is%20changed%20to%2001%2F04%2F2018%2C%20then%20values%20in%20cell%20F7%20to%20F12%20should%20reflect%20from%20%22B%22%20tab%20from%20cells%20%22D%22%20to%20%22G%22%20i.e.%20from%20Jan%20to%20April%20%26amp%3B%20from%20%22C%22%20tab%20from%20cells%20%22H%22%20to%20%22O%22%20i.e.%20from%20month%20May%20to%20Dec.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20should%20work%20looking%20into%20values%20corresponding%20in%20C7%20to%20C12.%20It%20is%20like%20when%20we%20change%20date%2C%20it%20should%20look%20values%20from%20tab%20A%20and%20B%20considering%20corresponding%20values%26nbsp%3Bin%20C7%20to%20C12.%3C%2FP%3E%3CP%3ELet%20me%20know%20in%20case%20of%20any%20clarification.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1297593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297742%22%20slang%3D%22en-US%22%3ERe%3A%20Relating%20formula%20between%202%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3BNoticed%20that%20the%20row%20headers%20on%20B%20and%20C%20were%20not%20in%20the%20same%20order%20as%20in%20A.%20Perhaps%20you%20did%20this%20on%20purposes%2C%20but%20it%20makes%20solving%20your%20problem%20a%20lot%20more%20difficult.%20So%2C%20I%20changed%20that%20and%20offer%20you%20the%20simpler%20solution%20in%20sheet%20A.%20It%20uses%20SUMPRODUCT%20and%20two%20extra%20date%20fileds%20to%20also%20determine%20the%20start%20and%20end%20of%20year%20you%20want%20to%20sum.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297776%22%20slang%3D%22en-US%22%3ERe%3A%20Relating%20formula%20between%202%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297776%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EThank%20you%20for%20reply.%20The%20formula%20works%20partially%20as%20there%20should%20not%20be%20multiple%20dates%20in%20row%20C.%20The%20formula%20should%20work%20when%20we%20just%20enter%20date%20in%20Cell%20C2.%20I%20would%20love%20it%20if%20it%20is%20solved.%3CBR%20%2F%3EThanks%20again%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297799%22%20slang%3D%22en-US%22%3ERe%3A%20Relating%20formula%20between%202%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297799%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3BMoved%20the%20first%20and%20last%20date%20of%20the%20year%20to%20the%20hidden%20column%20H%20and%20they%20are%20now%20%22calculated%22%20by%20the%20DATE%20function%2C%20based%20on%20the%20date%20in%20C2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297809%22%20slang%3D%22en-US%22%3ERe%3A%20Relating%20formula%20between%202%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297809%22%20slang%3D%22en-US%22%3ELol!%20It%20was%20so%20basic.%20I%20bothered%20you%20for%20that.%20Thanks%20alot%20for%20everything.%20Would%20love%20to%20get%20help%20repeatedly%20from%20stalwart%20like%20you.%20Thanks.%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi,

 

There is a date in cell C2 in attached spreadsheet as 01/03/2020. The values in cell F7 to F12 should reflect from "B" tab from cells "AB" to "AD" i.e. from Jan to Mar & from "C" tab from cells "AE" to "AM" i.e. from month April to Dec. 

If date in cell C2 is changed to 01/04/2018, then values in cell F7 to F12 should reflect from "B" tab from cells "D" to "G" i.e. from Jan to April & from "C" tab from cells "H" to "O" i.e. from month May to Dec. 

The formula should work looking into values corresponding in C7 to C12. It is like when we change date, it should look values from tab A and B considering corresponding values in C7 to C12.

Let me know in case of any clarification. 

4 Replies
Highlighted

@Dharmendra_Bharwad Noticed that the row headers on B and C were not in the same order as in A. Perhaps you did this on purposes, but it makes solving your problem a lot more difficult. So, I changed that and offer you the simpler solution in sheet A. It uses SUMPRODUCT and two extra date fileds to also determine the start and end of year you want to sum.

Highlighted
Hi,
Thank you for reply. The formula works partially as there should not be multiple dates in row C. The formula should work when we just enter date in Cell C2. I would love it if it is solved.
Thanks again for the help!
Highlighted
Solution

@Dharmendra_Bharwad Moved the first and last date of the year to the hidden column H and they are now "calculated" by the DATE function, based on the date in C2.

 

Highlighted
Lol! It was so basic. I bothered you for that. Thanks alot for everything. Would love to get help repeatedly from stalwart like you. Thanks.