+ Reply to Thread
Results 1 to 5 of 5

copy to one cell from any one of 12 cells

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    copy to one cell from any one of 12 cells

    The enclosed worksheet shows my attempts to create a worthwhile dashboard.
    The range marked in yellow range(Q30:AD32) shows the summary I am stuck on. This changes every month when the next column is calculated.
    What I want to do is to put in R30;R32 the number that is in the corresponding row in the range where the next result will be -100.
    I can do this in vba but it will be much easier to deal with as a formula in R30;R32, and I have had no success in devising one.
    Can anybody help please?
    John
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: copy to one cell from any one of 12 cells

    Hi,

    Does this help?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    TO get this to work, I've had to modify your formula in Row 32, there appears to be some rounding error causing a mismatch.

    eg
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I hope that you can live with 10 decimal places
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: copy to one cell from any one of 12 cells

    Not saying I'll answer this but I think you need to explain more as I can't see what you're trying to get and this has already had 14 views.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: copy to one cell from any one of 12 cells

    Thanks for your interest in my problem. Sweeps INDEX,MATCH idea does the job and I wish I had thought of it. The -1 escaped me. Many thanks
    Although I had removed formatting and made sure all the figures in the range were numbers before I used Sweeps method I am still getting a wrong result on row 32. The MATCH function is saying that the first match is no 8 in the row S32:AD32 when it is actually no4, like the rows above. If you have any thoughts about this I would like to see them.
    Special K, I am sorry that my request puzzled you. I did find it difficult to explain and hoped the attachment would help.
    Sweeps INDEX,MATCH method does make it clear though.
    Ive just realised there is still a problem with Sweeps method, that is what will happen in November when there is no -100?
    John

  5. #5
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: copy to one cell from any one of 12 cells

    Inelegant I know, but.....

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. [SOLVED] Marco to Insert & shift cells down at active cell and copy cell range
    By jyarbrough in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2015, 06:18 AM
  2. Replies: 3
    Last Post: 09-02-2015, 01:21 PM
  3. copy every 12 cells skip a cell (column) and copy
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2015, 04:30 PM
  4. Replies: 8
    Last Post: 07-08-2013, 06:03 AM
  5. Replies: 4
    Last Post: 04-18-2013, 03:07 PM
  6. Replies: 0
    Last Post: 09-22-2012, 02:20 AM
  7. [SOLVED] Copy cells into range of cells until cell change
    By mdeanda in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2005, 04: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