This is a simple klip which shows the mont - year and a value for that month year. How do i make the formula/value represent the values (month and $) of the column 1 right when the next month comes and is added to this google sheet?
Date
Votes
1 comment
-
Hi Jake,
Thanks for the post!
This is definitely possible, but given the setup of your data, requires a good deal more work on the formula side than it would if this data was pivoted so that the months were listed as a column, with each value also being laid out in columns rather than rows. With that in mind, if possible, I'd suggest setting up this data (perhaps in a different sheet in your Google sheets book) with a column-based layout rather than a row based one. If that's set up, you can use LOOKUP in this general format:
LOOKUP(DATEVALUE(TODAY(),"MMM yyyy"),*Your Date Column, *Your Data Column)
^ this formula will return the data value for the current month
With all that being said, if you can't change your data, here's what I recommend you do:
1) Create a 'Horizontal Lookup Tool' data source into your account. This is a data source in which the only row of data repeats the letter of the column it's in. See this screenshot below for an example (We have this data source in support that we can share with you if you email support@klipfolio.com) :
2) Set up the following formula:
DATASOURCE ( "Your datasource ID" , CONCAT(LOOKUP(DATEVALUE(TODAY(), "MMM yyyy") , *Your Date Row , *Row 1 of the Horizontal LOOKUP tool) , NUMBERFORMAT(LOOKUP("Your Data's Row Title",*Column A of your data, ARRAY(CUMULATIVE(REPEAT(1,COUNTALL(*Column A of your data)))))
This formula essentially pieces together the cell reference for your particular cell by finding the column and row locations using LOOKUPS, then concatenating them together and using the DATASOURCE function to call for that particular cell in your data. As mentioned before, this is a much more complicated method, but it will get you the values you're looking for.
I hope this helps! If you require additional support on this, please reach out to support@klipfolio.com and we'd be happy to help!
Parker
Please sign in to leave a comment.