Results 1 to 3 of 3

Thread: Autopopulating a formula with incrementing cell values/Formula's outputting values.

  1. #1

    Autopopulating a formula with incrementing cell values/Formula's outputting values.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I should mention in advance that my knowledge is scattered at best and I don't actually have access to any form of excel as I type this to recall/test, so I apologize if i'm at all unclear/use awkward terminology.

    I'm exporting a large amount of information into an excel document(DocA i'll now call it). It's VERY hard to quickly parse in this format, so i figured i'd make another excel file(DocB) that would pull data from specific cells on DocA and display it in a much easier to manipulate format. DocB would be a completely separate file because i may have different DocA's in the future which would have the same formatting, but different data and i'd like to just be able to save over the old one and have DocB still grab all the relevant data(NONE of the cells will change position. Only value)

    For example, on DocA I want data from the following cells-

    H9
    H40
    H71
    etc. Incrementing by 31's on until X(could be only a few, could possibly be in the thousands).

    And i'd like to display in DocB's
    A1
    B1
    C1
    etc.

    The method i tried to use was:
    copying DocA:H9, pasting it with link to DocB:A1
    copying DocA:H40, pasting it with link to DocB:B1
    Selecting DocB cells A1 and B1
    Grabbing the box in the lower right hand corner and dragging it out as far as needed.

    I'd hoped it would just continue the formula increments, but instead it just repeats(C1=H9, D1=H40, etc). Google somewhat lead me in circles so I thought i'd ask here for help before i continue. I can think of a couple of awkward brute force methods, but I'm hoping there's a quicker/easier/more elegant way as I don't want to spend a day linking 600 cells. I know this can be done with increments of 1, but the fact that it's jumping by 31's and referencing another document completely is giving me trouble.

    Further to kill two birds with one stone-
    How can I get a formula to actually replace itself with the value it pulls?
    Example-
    C1: =A1&" "&B1
    Usually i actually have to select cell C1 and hit F9, or copy the cell and paste values somewhere else so i can actually manipulate the data, not the formula. Is there a way to have a formula set to do this automatically?

  2. #2
    Good morning,

    Please see the attached file. I've used two sheets instead of multiple workbooks for an example. Just expand the pathname to go find what you're after.

    Hope this helps,
    Attached Files Attached Files

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Apologies if this in answered already, haven't read brother's solution.

    Given you want flexibility and ease in future documents . Would suggest using.
    1: the name of the worksheet to import in a cell e.g. A1
    2: either the maths you want to do in col A e.g. A2 =1 A3 = A2 + 31 etc or
    Use the row function = row() * 31 - 30
    3 use the indirect function = indirect($a$1 & "!b" & $a2)

    Could also use offset but more complicated

Posting Permissions

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