Description:
I'm using the excelize module to populate data into an Excel file and am subsequently calling the UpdateLinkedValue function to trigger recalculations for all formulas based on the newly populated data.
The issue arises with a specific formula that uses LOOKUP and TRANSPOSE to retrieve an entire row of data and convert it into a column. The formula looks like this:
TRANSPOSE( INDEX( INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))), LOOKUP(2,1/(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000))<>""),COLUMN(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000)))) ) ), SEQUENCE(1,LOOKUP(2,1/(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000))<>""), COLUMN(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000))))-1 )))
I have applied this formula to one sheet where 5-6 columns are updated based on it. The workflow I'm using is:
1) I populate data in the Excel file using excelize.
2) I call UpdateLinkedValue to trigger recalculations.
3) I open the Excel file to check the output.
However, after running it through excelize, the cells using this formula show an "N/A" error. If I manually reapply the formula in the Excel UI, it recalculates correctly, but for some reason, the recalculations don't work automatically after the UpdateLinkedValue function is applied.
Expected Behavior:
Formulas should recalculate based on the populated data when UpdateLinkedValue is called, without needing to manually reapply them.
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
Description: I'm using the excelize module to populate data into an Excel file and am subsequently calling the UpdateLinkedValue function to trigger recalculations for all formulas based on the newly populated data.
The issue arises with a specific formula that uses LOOKUP and TRANSPOSE to retrieve an entire row of data and convert it into a column. The formula looks like this:
TRANSPOSE( INDEX( INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))), LOOKUP(2,1/(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000))<>""),COLUMN(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000)))) ) ), SEQUENCE(1,LOOKUP(2,1/(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000))<>""), COLUMN(INDIRECT("'"&SUBSTITUTE(MID(FORMULATEXT(A2),2,FIND("!",FORMULATEXT(A2))-2),"'","")&"'!"&MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2)))&":"& ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A2),FIND("!",FORMULATEXT(A2))+1,LEN(FORMULATEXT(A2))))),1000))))-1 )))I have applied this formula to one sheet where 5-6 columns are updated based on it. The workflow I'm using is:
1) I populate data in the Excel file using excelize. 2) I call UpdateLinkedValue to trigger recalculations. 3) I open the Excel file to check the output. However, after running it through excelize, the cells using this formula show an "N/A" error. If I manually reapply the formula in the Excel UI, it recalculates correctly, but for some reason, the recalculations don't work automatically after the UpdateLinkedValue function is applied.
Expected Behavior: Formulas should recalculate based on the populated data when UpdateLinkedValue is called, without needing to manually reapply them. <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
Date | Vectors | Vectors | | | | -- | -- | -- | -- | -- | -- | -- Avaliação Econômica Financeira - Projeto | (=) Gross Revenue | (-) Production Cost | A. O&M Cost | Operating Cash Flow (FCO) | Investing Cash Flow (CFI) | Accumulated NPV Avaliação Econômica Financeira - Projeto | (=) Gross Revenue | (-) Production Cost | A. O&M Cost | Operating Cash Flow (FCO) | Investing Cash Flow (CFI) | Accumulated NPV 0 | 0 | 0 | 0 | -30.4382 | 0 | -30.4382 0 | 0 | 0 | 0 | -1.64101 | -269 | -282.812 0 | 0 | 0 | 0 | -1.64101 | -109 | -372.528 0 | 0 | 0 | 0 | -1.64101 | 0 | -373.685 0 | 0 | 0 | 0 | -1.64101 | 0 | -374.691 2024 | 0 | -30.43823022 | -19.841 | -1.64101 | 0 | -375.566 2025 | 0 | -1.641008 | -1.64101 | -1.64101 | 0 | -376.327 2026 | 0 | -1.641008 | -1.64101 | -1.64101 | 0 | -376.989