0

i'd like to populate a spreadsheet Nr 1 based on the entries I made in a different spreadsheet.

Here's a description of my workflow: Based on a due date, I generate a calenderweek for specific Task A. E.g. 12.12.2018 -> CW 50. Based on this result, the calenderweek, I'd like to auto-populate the spreadsheet Nr 1 with the specific Task in a cell corresponding to the CW.

Workflow

I hope I made this somewhat understandable.

Improve this question
  • Better you share the formula or Macro you have used and what write the problem you are facing with ! – Rajesh S Sep 28 '18 at 10:25
  • Unfortunately, I don't even know where to start with a macro for this particular case. If it doesn't have to be a macro, I wouldn't mind a solution based on Excel functions themselves – James Sep 28 '18 at 10:39
0

enter image description here

How it works:

  • Write this formula in Cell B130 of Sheet PO1.

    =INDEX(Overview!$C132:$H132,MATCH(Overview!$B133,INDEX(Overview!$C$133:$H$134, MATCH(Overview!$A133,Overview!$A$133:$A$134,),),0))

  • You get 1 .

  • For T2 Copy & Paste the formula in Cell C130 and you need to replace Overview!$B133 with Overview!$B134 also Overview!$A133 with Overview!$A134.

This is required because of the Format of the Table & the Formula Drag pattern.

Suggestion:

  • For smooth maneuvering you need to change the Table Format.
  • Check the Suggested format on the top & the Output.
  • Write this formula in Cell H122 & fill it down.

=INDEX(B$121:D$121,MATCH(G122,INDEX($B$122:$D$125,MATCH(F122,$A$122:$A$125,),),0))

Adjust cell references in the formula as needed.

Improve this answer
  • I just tried the solution you offered. I am afraid there might be a missunderstanding. I want to add the corresponding Task, e.g. T1 to the sheet: "Overview" to the corresponding row in the calenderweek. What I want to do ultimately is only provide a date (MM:DD:YYYY) which in turn calculates the CW and then adds the corresponding Task (e.g. T1) to the overview. Your formula seems to work in the other direction, matching the Task from the overview sheet and populate the calender week – James Oct 1 '18 at 8:09
  • @James, I've suggested both Formulas on the basis of the OP and the Screen Shot is attached with. And both are working properly. Now if you have any new idea then try to modify the Formula accordingly. Since both of my Formula pulls Column Header on cell values. And Because I found that your Table format should be modified so that I've suggested the Second Formula for it !! – Rajesh S Oct 1 '18 at 8:41
  • Thanks - i ended up using the match function in conjunction with an IF function. that finally did the trick. Like so: =IF(G$4=MATCH(P01!$C$2);Overview!$D$4:$BC$4,),indirect(P01!C$B2),"") – James Oct 3 '18 at 6:10
  • @James, glad to help you,, keep asking,,,!! ☺ – Rajesh S Oct 10 '18 at 5:16
  • @James,, why you have rejected it as answer since earlier you have accepted it !! – Rajesh S Oct 31 '18 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.