Streamline my Formula
Hello everyone, Im currently using a very tedious process of finding the number that I need from this sheet. It is a pasted excel sheet into a Smartsheet. There are multiple repeating "CC Codes" that are only differentiated by the "111" number in the Primary Column. I need to find the value for each of these items on another sheet. On another sheet, the formula below is being used to return the number I need: INDEX({2022B}, 71, MONTH(TODAY())) * 1000
.
What I want to do is find a formula that will find the number I need WITHOUT me having to change the row number for every value I need on the other sheet. Im open to slightly reconfiguring this sheet (like with Parent /Child ) but I dont want it to be tedious. Any thoughts or suggestions?
Answers
-
So in your INDEX function, your range is the group of months, correct? And you are using the MONTH function to provide a column that matches the month number?
You can create a few helper columns to make this happen. Two helper columns on the original sheet and one helper (+ the final output column) on your second sheet where you want the final value.
FIRST SHEET
In the first helper column (HELP), paste the corresponding "111-x" number into it. So lines 81 through 94 would be 111-235. In the second helper column, HELP2, use the column formula:
=HELP@row + [CC Code]@row
SECOND SHEET
In your other sheet, have a column that contains the "111-x" number + CC Code (it has to be formatted the same as you see in HELP2 from the previous sheet). Call that one HELP3.
Create a new column for your final output column formula (this is the one you requested in your question) and add this column formula:
=INDEX({2022B}, MATCH(HELPER3@row, {HELP2}, 0),MONTH(TODAY()))*1000
Where {2022B} is your original range and {HELP2} is a cross-sheet reference to the HELP2 column created above.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!