SOLVED

Formula which present the values as required

%3CLINGO-SUB%20id%3D%22lingo-sub-1930713%22%20slang%3D%22en-US%22%3EFormula%20which%20present%20the%20values%20as%20required%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1930713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20tried%20at%20my%20end%20to%20make%20such%20a%20formula%20which%20fulfill%20the%20data%20Quarterly%20as%20Q12020%2C%20Q22020%20after%20that%20then%20H22020%2C%20H12021%2C%20H22021%20and%20so%20on.%20But%20i%20could%20not%20done%20this%20then%20posted%20here%20to%20get%20the%20help.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EActually%20the%20thing%20i%20have%20been%20trying%20to%20get%20by%20formula%20is%20that%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20cell%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22H2%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bhas%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EQ12020%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bthis%20quarter%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ethen%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%22M2%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B(has%20a%20formula)%20that%20should%20be%20started%20from%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EQ12020%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bthis%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Eand%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22M3%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bwould%20be%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EQ22020%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3E%22M2%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Band%20%22%3C%2FSPAN%3E%3CSTRONG%3EM3%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bwill%20always%20have%20quarter%20(that%20could%20be%20Q12020%2C%20Q22020%2C%20Q32020%2C%20Q42020%20according%20to%20years)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Eand%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22M4%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bwould%20be%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EH22020%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3Ethen%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSTRONG%3E%22M5%22%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%3Ewould%20be%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EH12021%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3Ethen%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSTRONG%3E%22M6%22%20%3D%20H22021%3CBR%20%2F%3E%22M7%22%20%3D%20H12022%3CBR%20%2F%3E%22M8%22%20%3D%20H22022%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3Eand%20so%20on.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EFirst%206%20months%20will%20be%20consider%20as%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EH1%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3ELast%20six%20months%20will%20be%20consider%20as%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EH2%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20i%20change%20the%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EQ12020%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bto%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EQ32020%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bin%20cell%20%22%3C%2FSPAN%3E%3CSTRONG%3EH2%22%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%3Ethen%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22M2%22%20%3D%20Q32020%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%3Eand%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22M3%22%20%3D%20Q42020%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3Ethen%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Eand%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22M4%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bwould%20be%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EH12021%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3Ethen%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSTRONG%3E%22M5%22%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%3Ewould%20be%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EH22021%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3Eand%20so%20on%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20would%20be%20glad%20if%20someone%20can%20help%20me%20with%20this.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AjInpusmnhTAgR2FWTeRWU7cwy6I%3Fe%3DprPl6U%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AjInpusmnhTAgR2FWTeRWU7cwy6I%3Fe%3DprPl6U%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1930713%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1930901%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20which%20present%20the%20values%20as%20required%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1930901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743969%22%20target%3D%22_blank%22%3E%40LearningExcelVBA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2Fqv53f6jad837nuk%2FBook1.xlsx%3Fdl%3D1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2Fqv53f6jad837nuk%2FBook1.xlsx%3Fdl%3D1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1931749%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20which%20present%20the%20values%20as%20required%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1931749%22%20slang%3D%22en-US%22%3Ethank%20you%20for%20the%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1937392%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20which%20present%20the%20values%20as%20required%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937392%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%20I%20hope%20you%20are%20doi%20n%20well.%20I%20need%20some%20changes%20nin%20formula%20if%20you%20can%20help%20me%20out%20further%20with%20this%20that%2C%3CBR%20%2F%3EIf%20I%20select%20Q42020%20then%20first%20cell%20shoul%20show%3CBR%20%2F%3EQ42020%3CBR%20%2F%3EQ12021%3CBR%20%2F%3EQ22021%3CBR%20%2F%3EThen%3CBR%20%2F%3EH22021%3CBR%20%2F%3Eand%20further%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESame%20if%20i%20select%3CBR%20%2F%3EQ22020%3CBR%20%2F%3EThen%20formula%20first%20cell%20will%20be%3CBR%20%2F%3EQ22020%3CBR%20%2F%3EQ32020%3CBR%20%2F%3EQ42020%3CBR%20%2F%3EThen%3CBR%20%2F%3EH12021%3CBR%20%2F%3EH22021%3CBR%20%2F%3Eand%20so%20on%3CBR%20%2F%3ERest%20will%20be%20same%20as%20already%20available%20in%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20select%20Q12020%20or%20Q32020%3CBR%20%2F%3EThen%20first%202%20cell%20will%20be%20quarter%20then%20half.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1937566%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20which%20present%20the%20values%20as%20required%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743969%22%20target%3D%22_blank%22%3E%40LearningExcelVBA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2Fqv53f6jad837nuk%2FBook1.xlsx%3Fdl%3D1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2Fqv53f6jad837nuk%2FBook1.xlsx%3Fdl%3D1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1937574%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20which%20present%20the%20values%20as%20required%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Sir%20Thank%20you%20so%20much%20for%20the%20consistent%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20great.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have tried at my end to make such a formula which fulfill the data Quarterly as Q12020, Q22020 after that then H22020, H12021, H22021 and so on. But i could not done this then posted here to get the help.

Actually the thing i have been trying to get by formula is that
If cell "H2" has Q12020 this quarter
then "M2" (has a formula) that should be started from Q12020 this
and "M3" would be Q22020


"M2" and "M3" will always have quarter (that could be Q12020, Q22020, Q32020, Q42020 according to years)


and "M4" would be H22020
then
"M5" would be H12021
then
"M6" = H22021
"M7" = H12022
"M8" = H22022

and so on.


First 6 months will be consider as H1
Last six months will be consider as H2


If i change the Q12020 to Q32020 in cell "H2" then "M2" = Q32020 and "M3" = Q42020
then
and "M4" would be H12021
then
"M5" would be H22021
and so on

I would be glad if someone can help me with this.

 

 

https://1drv.ms/x/s!AjInpusmnhTAgR2FWTeRWU7cwy6I?e=prPl6U

5 Replies
best response confirmed by LearningExcelVBA (New Contributor)
thank you for the help.
Hi Hans, I hope you are doi n well. I need some changes nin formula if you can help me out further with this that,
If I select Q42020 then first cell shoul show
Q42020
Q12021
Q22021
Then
H22021
and further


Same if i select
Q22020
Then formula first cell will be
Q22020
Q32020
Q42020
Then
H12021
H22021
and so on
Rest will be same as already available in formula.

If I select Q12020 or Q32020
Then first 2 cell will be quarter then half.

@Hans Vogelaar  Sir Thank you so much for the consistent help.

 

You are great.