Forum Discussion
A lookup field within a line of text
Hello everyone...I know this will be child's play for most but I'm trying to figure out a simple way to auto-populate a sheet I'm making where I want to place the year or quarter into a text string without having to edit my entire spreadsheet every year. Sounds real simple and when I see the solution....I know I'm going to feel silly and stupid for even asking. But here goes....
I have a worksheet that is broken down into specific quarters for each year...I would like to enter the year into a cell...say A1=2021 and have it populate all fields that point to A1 with the values of 2021. But that value "2021" lives embedded in a string of text in multiple places on my sheet.
eg. Cost of Goods - For Period 2021-07-01 to 2021-09-30
Now this occurs in literally 50 places on this worksheet with perhaps a little different wording to the specific cell text...like might say "Total COG's for the period 2021-07-01 to 2021-09-30" and then I have various other places like title bars where it will say "2021-Q1 Reports"...again occurring in multiple places....I hope I'm explaining this correctly...make sense? It's been a real pain in the butt having to edit this every year after our year-end to make it proper for the upcoming year.
I know this is basic stuff for most but would be a real-time saver for me if I could just make it do this one thing. Thanks for any help someone may be able to offer up....
You may replace all the Strings which contains the date or year info in them with the formulas and once it is set up, they will adopt the change in the year entered in cell A1.
e.g. you may enter the following formulas in the worksheet and they will reflect the year entered in cell A1.
="Cost of Goods - For Period "&TEXT(DATE($A$1,7,1),"yyyy-mm-dd")&" to "&TEXT(DATE($A$1,9,30),"yyyy-mm-dd")="Total COG's for the period "&TEXT(DATE($A$1,7,1),"yyyy-mm-dd")&" to "&TEXT(DATE($A$1,9,30),"yyyy-mm-dd")=$A$1&"-Q1 Reports"Similarly, you can construct other formulas.
3 Replies
You may replace all the Strings which contains the date or year info in them with the formulas and once it is set up, they will adopt the change in the year entered in cell A1.
e.g. you may enter the following formulas in the worksheet and they will reflect the year entered in cell A1.
="Cost of Goods - For Period "&TEXT(DATE($A$1,7,1),"yyyy-mm-dd")&" to "&TEXT(DATE($A$1,9,30),"yyyy-mm-dd")="Total COG's for the period "&TEXT(DATE($A$1,7,1),"yyyy-mm-dd")&" to "&TEXT(DATE($A$1,9,30),"yyyy-mm-dd")=$A$1&"-Q1 Reports"Similarly, you can construct other formulas.
Thank you so much....you are a champ. This works exactly as I had hoped and I've put it to use already. You, my friend, have saved me countless hours of future work and the accuracy of my specialized worksheets. Thanks again...I truly appreciate your reply.
Dale
You're welcome Dale_Rivers@! Glad it worked as desired.
Please take a minute to accept the post with the proposed answer as a Best Response to mark your question as Solved.
