Conditional Formatting based on data source
Our take
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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Conditional formatting based on range of values in different column?https://preview.redd.it/l3guc8q9wfog1.png?width=1324&format=png&auto=webp&s=ec9fba2147eebc33af9e108241f82cb9163cf512 I've tried several different ways to do this but clearly I'm missing something. I have spreadsheet that shows the hours scheduled on all the machines at our facility. The dates in row 2 auto populate based on today's date. I want to make a rule that changes the formatting of rows 3 - 12 if any of the dates in row 2 are company holiday. For example, if B2 matches any of the dates in R3 through R12, I want B3 through B12 to fill in black with red text, so that way it shows that date is blacked out because we're closed, but if someone accidentally scheduled an order that day the machine hours will show in red so they know to reschedule it. On the original version of this spreadsheet I made a few years ago I had the same issue and end up just using =B$2=$R$3, so for each day I had to make 10 different rules for every holiday in column R. I knew that definitely was not the best way to do it but I was tired of messing with it and just needed it to work. I am updating this spreadsheet and it now shows the next 60 days instead of the next 14 days and I know there's got to be a better solution than creating 10 different rules for each day. I know =B$2=$R$3:$R$12 does NOT work and I think I'm starting to smell burnt toast. submitted by /u/BluestockingLife [link] [comments]
- Can I reference tabs using input cells?Hello, I am trying to create a fancy budget sheet and am uploading spreadsheet data from my bank to calculate with. My goal is to calculate items on a month-to-month basis. All of my data tabs are labeled as such: April - Checking, March - Checking, etc. I might just combine all the data into one tab for simplicity and INDEX MATCH by date, but I was wondering if it would be possible to have one input cell for my month of choice (April, March), and then have a formula referring to that input cell that changes which tab it pulls data from. Here's the monstrosity I tried lol which obviously didn't work but I'm not sure how I would fix it. I wanted the input cell to feed the month into the formula to pull from a specific tab. Base Inputs!B4 is the month (March), and March 26 EXP - Credit Card is the tab I'm trying to pull from, but I have another tab for April that I was hoping to switch between for the data source by changing the "input cell month". Column H returns Balances, Column A returns dates, and A32 is the match for dates. =INDEX('"*"&'Base Inputs'!$B$4&"*" 26 EXP - Credit Card'!$H:$H, MATCH(A32, '"*"&'Base Inputs'!$B$4&"*" 26 EXP - Credit Card'!$A:$A, 0)) Is what I'm asking even possible? Let me know! submitted by /u/Complex-Database6750 [link] [comments]
- Set conditional formatting formula to change cell colors after conditions are met in a separate tabI'm doing r/Fantasy's reading Bingo challenge this year, and thought I'd set up a spreadsheet to help me track my progress. The conditional formatting formulas have been causing me some trouble, though: In Tab 1, I have the Bingo squares arranged in the usual 5x5 grid, and in Tab 2, I have all the squares & their reading rules, hard modes, and completion statuses laid out. I'd love to set it up so that if, in Tab 2, I mark "x square" as completed in "EASY" mode, then the equivalent "x square" cell in the 5x5 grid will automatically change color in Tab 1. Ideally the 5x5 grid won't need any manual color formatting, and I'll only need to edit Tab 2. But I haven't been able to figure out how to make this work across the two separate tabs. In case I'm not describing it well, I'm including an editable Google Sheets version of the spreadsheet for reference: https://docs.google.com/spreadsheets/d/1S6EtJFLLSoysxIp-HHZT_tjVznucJuDbhqpDhbdPZX0/edit?usp=sharing Thanks so much in advance! submitted by /u/defenestratedduck [link] [comments]