Find same date (month) and return data from same column

Question

I have a set of costs shown for a project on a month by month basis, which I need to feed into another sheet.

Sheet1 is my base data, Sheet2 is a consolidated overview. The project start date may change, so I would like to be able to change the date in Sheet1 to then feed automatically into Sheet 2. This way I can test different start dates for my project.

I need a formula which will look at the date in Sheet1 and return a value from the same column into the relevant date in Sheet2.

E.g. If cell B3 in Sheet1 matches cell D3 in Sheet2 return cell B40 from sheet 1 into cell D5 in Sheet2.

Answer 1

Using the formula: =IF(B4='Sheet1'!B12,'Sheet1'!B40,0) this only works if I keep the start date in Sheet1 the same as it is in Sheet2, which means I might as well just enter ='Sheet1'!B40

When I change the start date in Sheet1 to e.g. Oct-22 it picks nothing up in Sheet2 for Oct-22 as is only looking for the data in column H IF it matches the date.

Answer 2

Hi,

I'm struggling to articulate what I'm trying to do, and why it's not working!

An IF function doesn't achieve it, as I need the search criteria to be more than one column (I think!).

Sheet2 (below) has fixed dates month by month (using EDATE). The Costs are fed by Sheet1.

The project start date is subject to change, but the costs will be fixed to specific milestones, I need to be able to change the start date in Sheet1 (B4) and make it feed the relevant cost data into the correct month in Sheet2.

Answer 3

Hello RM_WCM,

Thank you for posting your question in our community, it is possible I did not understand your query properly but this seems like somthing an IF statement can handle.

=IF(Sheet1! B3=Sheet2! D3,Sheet1! B40,0)

Please if you were referring to something else let us know.

Best Regards, Asher