2

I've built a crop planning calendar in which I enter the date I want a crop to be ready in one sheet, and it is automatically populated in the following sheet using INDEX.

I enter data in these cells on the first sheet.

The goal is to enter the date I want the crop to be ready in the first sheet, and the formula in the second sheet "reverse" dates back to the date I need to plant it (which is an earlier date), using:

=IF(U$3+MROUND(($BH4+$BG4)/7,1)>=53, "", IF($C4="Baby", INDEX('TP Bedft'!$D4:$BC4,1,U$3+MROUND(($BG4)/7,1)), INDEX('TP Bedft'!$D4:$BC4,1,U$3+MROUND(($BH4)/7,1))))

Then on the next sheet, I use INDEX to move the data to the week I need to plant the crop.

On the third sheet is the day on which I'm supposed to seed it in the greenhouse (which is even earlier). However, I not only want the data to move to the right cell (week), but I also want to do an operation on that number to convert it to, in this case, the number of trays to be seeded. Right now I'm using:

=IF('TP Schedule'!U4="","", MROUND('TP Schedule'!U4*$BF4*$BH4/$BG4,0.5))

This changes the number from the second sheet to "# trays", but how do I do that calculation AND move it to the right cell using INDEX within the same sheet?

Here the data in the cell needs to move back by a certain number of weeks, but it also has to be converted to another number by way of a calculation.

  • Thank you for the images of sheets 1, 2, and 3; it is informative though not clear whether all relevant columns are displayed. You are asking for help with a formula but you haven't shared the existing formula (sheet2 and sheet3). Would you please edit your question to include a (plain text) example of the relevant formulae and an example of a successful outcome for sheet#3. I suggest that you use a hypothetical example (say Baby Chard cropping due May 31) and display the formula and outcomes for each sheet. Without this additional information, I can't see how we are able to assist you. – Tedinoz Nov 21 at 1:04
  • Sorry about that! I'll include screenshots with the rest of the columns and text of the formulas. – Denis Heraud Nov 21 at 1:16
1

In the spreadsheet: on Sheets 1, 2 and 3,

  • row#1: start date for the week (Sunday);
  • row#2: cumulative number of days in that week since the start date (multiple of 7);
  • row#3: week number;

There are several challenges on Sheet3.

  • calculate the number of seedling trays that are required (formula exists).
  • identify the number of days required for seedlings to be ready for planting (value not available/not obvious), and
  • display the date on which the seedling trays must be prepared (formula required).

Time for seedlings to be ready for planting: This variable could not be identified, so I created a new variable in Column BJ of Sheet3 - "Days seedling->Plant".

Display the date on which seedling trays must be prepared: Enter this formula in Sheet3, cell R4. Copy the formula down and across the date columns/product rows.

=if(INDIRECT(address(4,column()+MROUND(($BJ4)/7,1),2,1,"TP Schedule"))="","",MROUND(INDIRECT(address(4,column()+MROUND(($BJ4)/7,1),2,1,"TP Schedule"))*$BF4*$BH4/$BG4,0.5))

Logic

  • ADDRESS() constructs the cell reference on sheet="TP Schedule"
    • MROUND(($BJ4)/7,1): using the existing methodology, this calculates the number of weeks between seedlings and planting. This is added to the column() to cross reference to the relevant cell in the relevant week on sheet="TP Schedule"
  • INDIRECT returns the value of the cell defined by address.
  • IF: if the cell on "TP Schedule" is blank, then the cell on sheet 3 is blank;
    • otherwise the cell on sheet 3 will display the number of seed trays required.

Sheet1 (TP Schedule)

Sheet1


Sheet2 (TP planting)

Sheet2


Sheet3

Sheet3

| improve this answer | |
  • Your answer was just what I needed! Thank you for your time and for educating me on the use of INDIRECT. The only thing about your answer that did not work in the formula you pasted is that you used a number for the row, whereas when I apply the formula to other rows it kept using "4" as the row number. So I simply swapped out "4" for row() in both instances. – Denis Heraud Nov 23 at 15:51
1

The final formula:

The final formula

I also added an IF statement that will prevent the formula from looking at anything beyond week 52 so as to not get "trash" data appearing in the later weeks of the year.

=IF(column()+MROUND(('TP Schedule'!$BF4)/7,1)>52,"", IF(INDIRECT(address(row(),column()+MROUND(('TP Schedule'!$BF4)/7,1),2,1,"TP Schedule"))="","",MROUND(INDIRECT(address(row(),column()+MROUND(('TP Schedule'!$BF4)/7,1),2,1,"TP Schedule"))*$BF4*$BH4/$BG4,0.25)))

Thanks for the help and explanation @Tedinoz.

| improve this answer | |
  • A sensible and well crafted solution. Would you please add a plain text version of your formula to your answer so that future users can easily copy and modify it. – Tedinoz Nov 23 at 21:37
  • Thanks for your help and guidance! – Denis Heraud Nov 24 at 14:26

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.