krim rachid
asked on
Macro to fill an automatic sheet with date interval from two source sheets
Hello
I wish to have a macro that informs me my ” Synthése” sheet from my two source sheets by date start and end date that I define.
I want to enter the sum of the data in column G and H of the "source1" sheet in column C and D of the” Synthése” sheet by agence Client in a date range between 02 dates 2017
And the type of the "Source 2" sheet in number and amount by agence Client in ” Synthése” sheet
Namely that I do it manually every week for several leaves Sources.
Thank you very much.
Attached an example of source sheets from my working file.
Calcul-REC---Equip-au-31-01-2017.xlsx
I wish to have a macro that informs me my ” Synthése” sheet from my two source sheets by date start and end date that I define.
I want to enter the sum of the data in column G and H of the "source1" sheet in column C and D of the” Synthése” sheet by agence Client in a date range between 02 dates 2017
And the type of the "Source 2" sheet in number and amount by agence Client in ” Synthése” sheet
Namely that I do it manually every week for several leaves Sources.
Thank you very much.
Attached an example of source sheets from my working file.
Calcul-REC---Equip-au-31-01-2017.xlsx
ASKER
HI
date E for Equip and date F for REC IN Sources 1
for 1 EQUIP date E>=2017 and F < 2017.
date D for Sources 2 for thre Type PPI PPO & Travaux.
thanks
date E for Equip and date F for REC IN Sources 1
for 1 EQUIP date E>=2017 and F < 2017.
date D for Sources 2 for thre Type PPI PPO & Travaux.
thanks
ASKER
for 1 REC = date F >2017.
Sorry, I didn't understand about PPI, PPO & Travaux? Which columns they are in Source2 sheet?
Type in Source2 should be in which column of Synthese Sheet?
Type in Source2 should be in which column of Synthese Sheet?
ASKER
type
You could try something like this:
The StartDate and EndDate are named ranges for the Start and End that you have in L4 and M4 respectively.
Then you can do the same for the Equip Nb just update the ranges.
=SUMIFS(Sources1!G2:G3636,Sources1!E2:E3636,">"&StartDate,Sources1!B2:B3636,B3)+SUMIFS(Sources1!G2:G3636,Sources1!E2:E3636,"<"&StartDate,Sources1!B2:B3636,B3)The StartDate and EndDate are named ranges for the Start and End that you have in L4 and M4 respectively.
Then you can do the same for the Equip Nb just update the ranges.
ASKER
this is update file like i want
Calcul-REC---Equip-au-31-01-2017-up.xlsx
Calcul-REC---Equip-au-31-01-2017-up.xlsx
ASKER
i have french excel !
I've added the formulas to the cells. Let me know if this is what you were looking to achieve.
Calcul-REC---Equip-au-31-01-2017.xlsx
Calcul-REC---Equip-au-31-01-2017.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thenk you Mike
Or it's a part what I'm looking for except when I change date start and end date it does not give me the right result
I want the synthetic sheet to be interactive with date in L4 and M4
Or it's a part what I'm looking for except when I change date start and end date it does not give me the right result
I want the synthetic sheet to be interactive with date in L4 and M4
ASKER
shums you are perfect
excume me for my english
thank you very much
excume me for my english
thank you very much
You're Welcome Krim! Glad I was able to help :)
What should be date reference in Source1 Sheet? Column E or Column F?
Type & Amount from Source2 Sheet, should extract to which Columns in Synthese Sheet?