Index pulls value but not conditioning of original cell

Luke Pickerill

I have two sheets I'm working with and in sheet A there is conditional formatting for rows that is dependent on a helper column for the formatting.

On a second sheet (B) I have one row where each cell is a date pulled from a different row in sheet A. Index pulls the contents of the referenced cell but not the formatting.

Is there a way to have the individual cells grab the formatting from the referenced "parent" cell?

Paul Newcome

No. The INDEX function only pulls data. Not formatting. You would need to apply the same conditional formatting rules to the second sheet.

Luke Pickerill

I appreciate the input. I deduced that INDEX only pulls data and accept that there isn't a function that pulls formatting directly.

I haven't gotten it to work yet but a possible solution I have in my head to create a conditional formatting rule that says "if a cell contains 'complete' then put a strike through the cell". To make it dynamic I'm thinking of changing my formula from:

=INDEX({date column from reference sheet},MATCH([task name in helper row]$1,{column of tasks from reference sheet},0))

to:

=IF({helper column from reference sheet}MATCH(see above to pull row number) = "complete", JOIN(<INDEX MATCH combo from above>,[helper cell in sheet saying "complete"],""),<index match combo from above>)

but i don't think JOIN works like that and I haven't figured out how to get IF to dynamically reference another sheet.

Paul Newcome

That's still not going to work. The JOIN function operates the same way in that it pulls data and not formatting.

Genevieve P.

Hey @Luke Pickerill

Jumping in here to help clarify & confirm: @Paul Newcome is correct. Formulas can grab the display value of a text but not the formatting/colouring.

However, it sounds like you're looking to apply formatting based on the value of the row above the data you're bringing over, is that correct? Could you perhaps post screen captures of your two sheets, explaining what colours/formatting you're looking to include, but block out sensitive data?

Thanks!

Genevieve

Luke Pickerill

@Paul Newcome you're right, but JOIN can combine my main table with a helper table to create a table that has text included, with the dates, that would trigger a conditional format in my sheet.

@Genevieve P. Close but not quite, I'll include a couple of screen shots. The hope is that when sheet A is updated with a task that is marked "complete" that it'd update the cell on sheet B which contains an overview. To get the desired result I plan on creating three summary tables, one with that pulls the dates, one that writes "c" if the task is complete, and a third table that is the other two joined together. Then I'll just hide two of the tables.

Proof of concept/solution

I'll have to tweak this a bit though. Thank you guys for helping me reach a solution. Please let me know if there is an easier way to do this.

Genevieve P.

Hey @Luke Pickerill

Thanks for the screen captures! Can I ask if you've tried a Report as an alternative?

Reports will show the formatting of the underlying sheet so you wouldn't need to set up any additional formatting or extra sheets.