Topic Options
#68622 - 09/16/22 03:30 PM Financial Reporter - Dynamic .ColSpec?
Hugh Offline
Adagio Maven

Registered: 09/25/13
Posts: 191
Loc: Victoria, BC Canada
(184.71.30.114)
Hi All!
I'm building a report to skip over Covid so I can compare current months to corresponding pre-covid months... In some instances I'm looking for the month that's two years ago {2} and sometimes it's three {3}...
Is there a way to toggle back and forth using a numeric input somehow so I'm typing 3 into a cell instead of navigating to the formula bar, highlighting the 2 and then typing 3?
Alternately, there seems to be a bit of a pull down list on the .ColSpec line, but I don't know how to populate it... if it had {2} and {3} that would be great, but it has some other random selections instead...
Thanks for any thoughts!


Edited by Hugh (09/16/22 03:30 PM)

Top
#68624 - 09/17/22 01:50 PM Re: Financial Reporter - Dynamic .ColSpec? [Re: Hugh]
Retired_Guy Offline
Adagio Master

Registered: 03/16/99
Posts: 10504
Loc: Canada
(108.172.21.155)
You can include a reference to another Cell in the specification formula. Just make sure that you put '2 or '3 in the cell so that the spreadsheet "knows" you mean the text string "2" and not the number 2. The "&" operator combines bits of text (concatenates strings) or you can use the STRCAT() function.
_________________________
Andrew Bates

Top
#68626 - 09/19/22 02:05 PM Re: Financial Reporter - Dynamic .ColSpec? [Re: Retired_Guy]
Hugh Offline
Adagio Maven

Registered: 09/25/13
Posts: 191
Loc: Victoria, BC Canada
(184.71.30.114)
I'm obviously syntax challenged... if I put {3}M in the colspec line I get a report. If I put '3 in cell G4 and {G4}M in the colspec line I get a thousand errors... *sigh*

Top
#68627 - 09/19/22 02:06 PM Re: Financial Reporter - Dynamic .ColSpec? [Re: Hugh]
Hugh Offline
Adagio Maven

Registered: 09/25/13
Posts: 191
Loc: Victoria, BC Canada
(184.71.30.114)
wait, what? RETIRED GUY???? Congratulations Andrew!

Top
#68628 - 09/19/22 06:52 PM Re: Financial Reporter - Dynamic .ColSpec? [Re: Hugh]
Bruce Gardner Offline
Adagio Wizard

Registered: 06/15/04
Posts: 3646
Loc: Toronto ON, Canada
(142.198.59.77)
Hi Hugh:

The Retired Guy loves to answer the Financial Reporter challenges, but in case you're in a hurry:

If G4 contains '3 (note the leading single quote, just like in Excel)

Then your Colspec cell would be : ="{"&G4&"}"&"M" (The brackets are brace brackets)
_________________________
Bruce Gardner
ARX Business Solutions Inc.

Top
#68629 - 09/20/22 10:21 AM Re: Financial Reporter - Dynamic .ColSpec? [Re: Bruce Gardner]
Hugh Offline
Adagio Maven

Registered: 09/25/13
Posts: 191
Loc: Victoria, BC Canada
(184.71.30.114)
Copy that Bruce! Thanks!

Top


Moderator:  Softrak Support 
Who's Online
0 registered (), 149 Guests and 0 Spiders online.
Key: Admin, Global Mod, Mod
Forum Stats
1873 Members
5 Forums
14552 Topics
71029 Posts

Max Online: 432 @ 01/20/25 10:17 PM
October
Su M Tu W Th F Sa
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31