Two Sheets in One Report using same column
I have two sheets referenced in a report. Both sheets have column "A" which is a date column. Since Sheet #1 is the source, I would like to only pull column "A" from Sheet #1. Sheet #2 references(using formula to index match) Sheet #1's column "A" just for information.
It seems when I use only Sheet #1 in the report, I can change the date in column "A". when i use Sheet #2, it blocks revising the date in the report because it has a formula.
how can i only use Sheet #1's column "A" in order to have the capability to revise the date in the report?
Best Answer
-
It sounds like you want to be able to change the date in Sheet 2's column formula, and specifically to do it inside a report. It sounds like you could create on your sheet 2 a "Manual Override" column, and then call that "A" (so it matches the Sheet 1 column name). And then somewhere on your Sheet 2, you can add your Index/Match to pull the date over, but add a clause to consider the manual override data:
=IF(ISBLANK([Manual Override]@row, INDEX/MATCH FORMULA TO PULL YOUR DATE, [Manual Override]@row)
That will make it possible for your date columns on both sheets to be manual entry. What other reports, automations, etc. you may have produced on that existing column "A" on Sheet 2 might need some retooling though.
Good luck!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Answers
-
It sounds like you want to be able to change the date in Sheet 2's column formula, and specifically to do it inside a report. It sounds like you could create on your sheet 2 a "Manual Override" column, and then call that "A" (so it matches the Sheet 1 column name). And then somewhere on your Sheet 2, you can add your Index/Match to pull the date over, but add a clause to consider the manual override data:
=IF(ISBLANK([Manual Override]@row, INDEX/MATCH FORMULA TO PULL YOUR DATE, [Manual Override]@row)
That will make it possible for your date columns on both sheets to be manual entry. What other reports, automations, etc. you may have produced on that existing column "A" on Sheet 2 might need some retooling though.
Good luck!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
thank you. I will try it and let you know how it goes.
