+ Reply to Thread
Results 1 to 5 of 5

How to change a number to increase in size as the table expands

  1. #1
    Registered User
    Join Date
    06-02-2018
    Location
    Skövde
    MS-Off Ver
    MS office 2011
    Posts
    3

    How to change a number to increase in size as the table expands

    Hi,

    I'm sorry for a poor title, I find it hard to describe my issue in a condensed way. So my issue is this:

    I have a table that gets its information from several other tabs and pivottables. The formulas look like this:

    =GETPIVOTDATA("[Measures].[Call]";'Data CD1'!$B$7;"[Call Day].[Call Month]";"[Call Day].[Call Month].[Call Month].&[2018-03]";"[Call Type].[Call Type]";"[Call Type].[Call Type].[Call Type].&[Answered]")

    At the moment I need to go in to each formula and manually change the date (highlighted in red) for each new month. There has to be a better way so as I add a new row to the table the month automatically increases by 1. I would appreciate any help with this.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,366

    Re: How to change a number to increase in size as the table expands

    Your thread title is fine - I wish everyone's titles were as clear as yours!

    I'm moving this thread to the correct section of the forum for you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to change a number to increase in size as the table expands

    Hello,

    I'm not sure I completely understand your requirement. Do you want the red number to increase by one when the formula is copied down? What about the year number, once you move past December?

    The easiest might be to have a helper column in your table, where you enter the desired date, then refer to that helper column in the GetPivotData function. Somethi

    Please Login or Register  to view this content.
    cheers, teylyn

  4. #4
    Registered User
    Join Date
    06-02-2018
    Location
    Skövde
    MS-Off Ver
    MS office 2011
    Posts
    3

    Re: How to change a number to increase in size as the table expands

    Thank you for your answer. The year isn't that important as each year is documented in a separate excel-file so there will always be some manual work. I was just hoping to avoid it on a monthly basis

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to change a number to increase in size as the table expands

    Well, in that case, you could work with the Row() function, which returns a number. =Row(A2) returns a 2, copied down it returns a 3, 4, etc.

    =GETPIVOTDATA("[Measures].[Call]";'Data CD1'!$B$7;"[Call Day].[Call Month]";"[Call Day].[Call Month].[Call Month].&[2018-"&text(row(a2),"00")&"]";"[Call Type].[Call Type]";"[Call Type].[Call Type].[Call Type].&[Answered]")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formulas not added as table expands
    By mjcarman01 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-27-2017, 02:27 PM
  2. Increase / decrease font size dependant upon number of decimal places
    By spoursy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2012, 03:35 PM
  3. Replies: 8
    Last Post: 01-17-2012, 04:14 PM
  4. How to increase table size based on quanity of an item
    By jsoutham in forum Excel General
    Replies: 1
    Last Post: 01-03-2012, 05:10 PM
  5. Control Command Button Font Size Expands
    By timp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-19-2006, 11:10 AM
  6. on click change cells color and increase a number..
    By Daniel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2005, 08:05 PM
  7. How to increase the size of a Pivot Table Graph
    By cdavidson in forum Excel General
    Replies: 1
    Last Post: 03-16-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1