Forum Discussion

SarahJP1040's avatar
SarahJP1040
Copper Contributor
Mar 30, 2023

Excel - Add value based on colour

Hi All,   I have an excel sheet where I am trying to create a cash flow estimate based on a programme of building works.    Column C has the planned week number, Column D has the planned duration...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Mar 31, 2023
Is it possible to have a file (without sensitive data) where you explain step by step what you intend to do?
  • SarahJP1040's avatar
    SarahJP1040
    Copper Contributor
    Mar 31, 2023

    Hi NikolinoDE - Thank you for looking at the sheet, if you look back at it, I've amended the duration column to 4 but the formula you have created doesn't apply the value to each of the new weeks added, that is why I'm looking for the formula to read the format of the cell in order to include the value. I'm sorry to be such a pain! 

    • FrankHamilton's avatar
      FrankHamilton
      Copper Contributor
      Sep 12, 2024

      SarahJP1040 Did you figure this out? I completely understand what you are trying to do and I have the exact same task I am trying to achieve. Its not highlighting based on a value, but returning a value based on highlighting. 

  • SarahJP1040's avatar
    SarahJP1040
    Copper Contributor
    Mar 31, 2023

    Column B represents a planned activity.

    Column C is the Planned Start of the activity.

    Column D is the Planned Duration.

    Columns I to CE are numbered in Row 6 to represent Week Numbers 1 to 60.

    When you 'plan' the activity, for say, week 1 with a 1 week duration, the sheet is conditionally formatted to fill these cells in peach.

    I then want the sheet to add the Forecast Value per Week from Column 8 into the peach cells. 

Resources