Excel formula help!

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
97
Hi guys,

Need your help to sort this. I got this sheet below that I need tweaking those simple formulas. Data in columns F / G / H are populated manually. Following columns will have formulas in them on rows 3 / 4 / 5. Data in columns I and on wards will repeat itself. For example in row 3: (B3-YES , C3-YES , D3-YES) means that data in I3/J3/K3 etc. will repeat itself on every 4th column.
ABCDEFGHIJK
1IDIDIDDAYDAYDAYDAYDAYDAY
2CODE393837123456
31YESYESYES373938=F3=G3=H3
42YESNOYES3938=E4=F4=G4
53NOYESNO373737=H5=I5=J5

Below is actual data:
IJK
DAYDAYDAY
456
373938
39
373737



I am populating this simple sheet by selecting column K and dragging formulas to column L and so on and so forth. This will give me below cell values.
LMNOPQR
DAYDAYDAYDAYDAYDAYDAY
78910111213
37393837393837
38393839
37373737373737


This works OK for me until there is a change in ID/CODE range (B:D) as any one of the cells could change from YES to NO and vice versa.
I would really need formulas in column L (and following columns) to check range (B:D) and then populate accordingly.



For example if range (B:D) below would change to :
BCD
IDIDID
393837
NONOYES
YESYESYES
YESNOYES



then this would generate below:
LMNOPQR
DAYDAYDAYDAYDAYDAYDAY
78910111213
37373737373737
37383937
39373937393739

Basically speaking every next column starting from L and on wards should contain one of the following ID: (39/38/37) which was not used for the longest time & if it's marked as YES, but ignore if states NO.
Hope this makes any sense?

Thank you.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
97
Try:

Book2
ABCDEFGHIJKLMNOPQ
1IDIDIDDAYDAYDAYDAYDAYDAYDAYDAYDAYDAYDAYDAY
2CODE393837123456789101112
31YESYESYES393837393837393837393837
42YESNOYES39 3739 3739 3739 37
53NOYESNO 38  38  38  38 
Sheet5
Cell Formulas
RangeFormula
F3:Q5F3=IF(INDEX($B3:$D3,MOD(COLUMNS($F3:F3)-1,3)+1)="YES",INDEX($B$2:$D$2,MOD(COLUMNS($F3:F3)-1,3)+1),"")
Hi Eric W,
It works, but the problem is when I change data in B3:D5 I need only need latest/furthest to the right column data to change, but it changes data in all reference cells.
I guess the only way would be to create macro?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,363
Messages
5,595,702
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top