Excel Formula

GM228

Apologies if this is not the correct place for this (feel free to move if that's the case MODs), but I'm wondering if it is possible to set an Excel formula to copy data to another location, but only based on data conditions in another cell (not sure if explaining myself correctly).

So for example in Cell A1, I have a particular date (which is an automated based on a date somewhere else in the worksheet), in cell A2 I have a figure. To the right of this there are up to 31 boxes (B1 to B31) representing each day of the month - 1 = the 1st and so forth, what I'm trying to do is automatically assign the figure from A2 into one of the 31 boxes based on the date above it.

So for example if A1 shows 3/5/19 and A2 shows the number 10, the number 10 then also shows in the B3 box, but if I change the date to say 5/5/19 the number 10 is now shown at the B5 box and not the B3 etc. In other words each box in the B row will only mimic the A2 cell when the appropriate date condition is met.

On top of that any of the other boxes not populated in the B row should be the number 0 as opposed to blank.

Not sure if explained that properly but I think I have given the jist of it, any help appreciated.

Ghost Train

You could put these into column b

=if(day(a1)=1,a2,0)
=if(day(a1)=2,a2,0)
Etc

GM228

To make it more complicated (and what I should have stated in my OP) is there are several A1 and A2s repeated. So for example there could be A1 with 5/3/19 and A2 with 10, then there could be A3 with 7/3/19 and A4 with 15. The B boxs need to correspond with a number of cells.

In other words B1 for example would be looking for a corresponding date from A1, A3, A5 etc and taking the appropriate corresponding figure (dates will never be duplicated between A1, A3 etc.

My actual cells are as follows:-

Date/Corresponding Figure: B3/B8, C3/C8, D3/D8, E3/E8 and F3/F8.

Days 1 to 31:- A36 to A66

So for example A35 is not just looking for it's date in B3, rather it is looking for it's date in five cells.

Edit: Figured the formula out in the end

Philipx

Can you post up the formula, was trying to figure it out!

GM228

Philipx wrote: »

Can you post up the formula, was trying to figure it out!

=IF(DAY(B3)=1,B8,IF(DAY(C3)=1,C8,IF(DAY(D3)=1,D8,IF(DAY(E3)=1,E8,IF(DAY(F3)=1,F8,0)))))

=IF(DAY(B3)=2,B8,IF(DAY(C3)=2,C8,IF(DAY(D3)=2,D8,IF(DAY(E3)=2,E8,IF(DAY(F3)=2,F8,0)))))

Etc

GM228

Further to my query does anyone know how I could limit the result to a given month only?

For example say the first four dates are in June and the fifth is in July one of the 31 boxs will still show the July date because it satisfies the criteria by having any date number between 1 and 31, so say you have 2nd July it shows in the 2 box as if it is June 2nd.

Lucas Hood

Try r/Excel I got great help on there.