CODEWITHSUNDEEP
exceliterationcircular-reference
Violet
Violet

Reputation: 27

Use iteration in MS Excel to populate column

I would like to set up an iteration in excel with three interdependencies

Structure of Input

The objective is to have an output with columns:

Key ColumnOut

that will use the input and fill the gaps dependent on some rules. possible output My idea is that every cell in ColumnOut will first look in ColumnIn for a value, failing that will look at the cell above and check a lookup for a value based on that and failing that will take the value from Plans!B1:

=IFS(
INDIRECT("Input!"&"R"&ROW()&"C"&COLUMN(),FALSE)<>0,
INDIRECT("Input!"&"R"&ROW()&"C"&COLUMN(),FALSE),
INDEX('lookup next'!$B:$B,MATCH(INDIRECT("R"&ROW()-1,FALSE),'lookup next'!$A:$A),0)<>0,
INDEX('lookup next'!$B:$B,MATCH(INDIRECT("R"&ROW()-1,FALSE),'lookup next'!$A:$A),0),
TRUE,'Plans'!$B$1)

However the contents of Plans!B1 is dependent on the size of the gap: it randomly selects only things that will fit in the gap. So I have a sheet called Gaps that has a cell (F2) that displays the size of the first gap needing filled. Here Gaps!F2=6. As Output is populated this number will obviously change. Options available to Plans!B1 change accordingly.

As you can see Output is dependent on Plans, Plans!B1 is dependent on Gaps!F2 and Gaps!F2 is dependent on output.

Excel doesn't allow circular references, but if I switch calculation options to manual, can I get some VB code to get it to step through iteratively and populate the output? Or is there another approach I should be using? I want to use Excel because it's what the users can do. I'm not allowed Python but could potentially use R if there's no other way. Grateful for any advice?

Upvotes: 0

Views: 80

Answers (0)

Related Questions