2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Conditional Formatting based on data source

I'm currently trying to automate cashflow projections at a new job. Haven't ever done anything of this magnitude before - we want to be able to do rolling cashflow projections on a monthly basis. We do have cashflow software that gets us part of the way but for various reasons we need external inputs.

The current process is very manual - dump out of the cashflow software, make pretty, and edit as required. I have set the workbook up to have an override tab for entering changes to the forecast software figures so that we can just dump in the data each month without having to have a template spreadsheet or rework all the formulas every month. The cashflow and override tab is all working as expected - figures are pulled from the override tab if the relevant cell isn't blank, otherwise from the data dump.

What I'm trying to do for clarity is to highlight cells that have pulled from the override tab so we know what has been changed and can check it. Googling seems to indicate that if I need to pull from another tab to conditionally format a cell, I should use "indirect", but this doesn't appear to work with index match. I've never used it before so could be my error.

Variants I've tried (B is the account name column, 2 is the date row):

INDEX('Cashflow Forecast Override'!$1:$1048576,MATCH($B7,'Cashflow Forecast Override'!$B:$B,0),MATCH(D$2,'Cashflow Forecast Override'!$2:$2,0))<>""

ISBLANK(INDEX('Cashflow Forecast Override'!$1:$1048576,MATCH($B7,'Cashflow Forecast Override'!$B:$B,0),MATCH(D$2,'Cashflow Forecast Override'!$2:$2,0))

Is there any way for me to actually do what I'm trying to do, or will I just have to live with it/try to flag overridden figures in another way?

submitted by /u/WTFisthatgamecalled
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#generative AI for data analysis
#natural language processing for spreadsheets
#digital transformation in spreadsheet software
#no-code spreadsheet solutions
#big data management in spreadsheets
#conversational data analysis
#real-time data collaboration
#intelligent data visualization
#financial modeling with spreadsheets
#rows.com
#cloud-based spreadsheet applications
#modern spreadsheet innovations
#machine learning in spreadsheet applications
#enterprise-level spreadsheet solutions
#Excel compatibility
#row zero
#collaborative spreadsheet tools
#AI-driven spreadsheet solutions
#automation in spreadsheet workflows