excel - bloomberg and updating formula if holidays give NA -

we use bdh function closing prices @ end of each trading day, , list of different types of securities , indices @ once.

currently every bdh-of-index in list refers same date in top of sheet, , if 1 index gives na because there no trading on day, manually make if refer cell date.

=bdh($b4&" index","px_last",$i$1,$i$1)   

where b4 refers index, spx etc, , i1 = yesterday's date.

i've written vba routine updates dates @ top, want check if of indices gives na, and, if so, let 1 refer cell date automatically.

can give me advice on how check values in row ranging c4:c20, , change cell formula uses. or should alter formula well?

you use override instead:

=bdh($b4&" index","px_last",$i$1,$i$1,"days=a,fill=p") 

that retrieve last available price of yesterday's close, may day before yesterday's close (or earlier date) if specific instrument did not trade yesterday.