+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 15 of 36

INDIRECT function - need help

  1. #1
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    INDIRECT function - need help

    Still toiling away in Excel 2007.

    Trying to massage an INDIRECT function to do what I want it to, and having problems. Someone on this forum had given me this formula years ago, and it has worked perfectly. Now, I'm trying to adapt it to sort some other fields, and cannot figure for the life of me how to get it right.

    The formula I have been using is: =INDIRECT("Sales!M"&ROW()*3+4), but will need changing.

    The values I am using are being taken from an internet site, and for the new formula, I want to stay on the same worksheet.

    I am pasting them as text in column A, so changed the first part of the formula to =INDIRECT("A"&ROW()

    The second part of the formula has me stumped, and I've tried as many variations of numbers there as I can, but I simply do not understand the logic behind the choice of numbers in the first place, so that is what is keeping me from succeeding.

    The info I'm pasting has the needed values repeating every 14 rows.

    I've attached a sample worksheet, but I'll explain a little.
    A1 is a reminder to my foggy memory. A3 is where the data from the internet will be pasted as text. As you can see, the data repeats the 14th row after the first instance, with a space (blank row) in-between.
    Column C is where the values for A4, A18 and A32 and so on need to wind up.
    Column E is where the values for A6, A20 and A34 and so on will go.
    And you can see the pattern. I've filled C3, C4, C5, etc just to show which value needs to go where. What you see is a representation of where the value for that field should be coming from. I.e. value from A4 needs to show up in C3, value from A18 in C4, etc.

    If you can help with a formula, that'll be great. Even better though, would be if you also will explain the logic behind the 2nd part of the formula so I can understand it, and see how to do this myself in the future.

    I hope the sample spreadsheet is attached. The way this forum does it seems a little funky to me.

    Thanks for any help.

  2. #2
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    Re: INDIRECT function - need help

    Now I think I've got the sample spreadsheet attached.
    Attached Files Attached Files

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    Quote Originally Posted by dansparts View Post
    Column C is where the values for A4, A18 and A32 and so on need to wind up.
    Column E is where the values for A6, A20 and A34 and so on will go.
    ...from A4 needs to show up in C3, value from A18 in C4, etc.
    No attachment?
    Anyway, with some basic info, I tried:
    In A6:
    Please Login or Register  to view this content.
    Copy up to A4:A5 and down to A7 to as far as you want

    If it does not help, try to attach a sample file.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Untitled.png

    P/S:have seen attachment now. Working on this.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,382

    Re: INDIRECT function - need help

    Hi,

    Does the attached help

    It uses formulae like this in C3 with row 2 containing a column offset number

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    OK now I got it.

    Start with C3:
    =OFFSET($A$4,MOD(ROW(1:1)-1,14),)
    Copy to E3 and edit A4 to A6
    =OFFSET($A$6,MOD(ROW(1:1)-1,14),)
    Similar copy and edit accross to O3
    Copy C3:O3 down

  6. #6
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    Re: INDIRECT function - need help

    @bebo

    Using that formula, copied all the way down Column C, give me the value from A4 into C3, but then the value from A5 into C4, value from A6 into C5, etc.

    I need value from A18 in C4, not value from A5.

    @Richard -

    I really don't want to have to "clutter" things up with something like the column offset number.

    Can the INDIRECT function not be used?

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    Quote Originally Posted by dansparts View Post
    @bebo

    Using that formula, copied all the way down Column C, give me the value from A4 into C3, but then the value from A5 into C4, value from A6 into C5, etc.

    I need value from A18 in C4, not value from A5.
    Opp, sorry for my mistake. I forgot to time it to 14:

    =OFFSET($A$4,MOD(ROW(1:1)-1,14)*14,)

    Or simple like this:
    =OFFSET($A$4,(ROW(1:1)-1)*14,)

  8. #8
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    Re: INDIRECT function - need help

    @bebo

    That worked.

    I need to understand how/why this works, since I will need to be adapting this formula to several other worksheets, with different amounts of rows & columns of the pasted text.

    What is the difference of the MOD in the formula or not?

    I see that the (1:1) changes as it is pasted down the column to (2:2), (3:3), etc. And the -1) part?

    I assume the *14 will need to be changed to whatever spacing of rows apart the data repeats on.

    Why use this OFFSET function, instead of INDIRECT?

    Also, is not OFFSET for a static range? The text data I will be pasting in will vary in length almost always. In other words, sometimes I paste information from 3 records, sometimes from 25 records, sometime from 150 records. Will that affect things?

    Sorry for questions that might seem elementary, but while I use Excel all the time, I'm not at all well-versed in complicated formulas like this. Most of my "expertise" (cough, cough) is simple things like =SUM.
    Last edited by dansparts; 12-07-2017 at 10:46 PM.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    ROW(1:1) copy down to be 1;2;3;4;...
    ROW(1:1)-1 copy down to be 0;1;2;3;...
    OFFSET($A$4,0) is $A$4 it self
    OFFSET($A$4,1) is start from $A$4, going down 1 row, refer to $A$5

    (ROW(1:1)-1)*14 copy down to be 0;14;28;42,...
    OFFSET($A$4,ROW(1:1)-1)*14) = OFFSET($A$4,0) refer to $A$4 it self
    OFFSET($A$4,ROW(2:2)-1)*14) = OFFSET($A$4,14) refer to $A$18
    ...

    Hope it helps

  10. #10
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    Re: INDIRECT function - need help

    Still trying to understand.

    If I was pasting my text into A2, instead of A3, and I then wanted the value from A3 (not as currently A4) to go to C3, what would change in the formula? Just the $A$4 to $A$3?

    Have one other problem to solve.

    One of my text strings actually looks like this: 2016-04-24 03928900 PTN0327. Is there any easy way to split these 3 pieces of data apart (they are separated by a space) and put them into 3 different columns? String #1, the date, is always the same number of characters (10). Around 90% of the records I'm using will have the same number of characters (8) in the 2nd string, the part number. The third string will have anywhere from 2 to 10 characters. Previously, I have messed around with =LEFT or =RIGHT and multiple splittings in several columns, then referencing the final result back to the column I want it in. Very cumbersome and messy.

    Is there any easy way to do that, and account for variability in number of characters in strings 2 & 3?

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    Quote Originally Posted by dansparts View Post
    Still trying to understand.
    If I was pasting my text into A2, instead of A3, and I then wanted the value from A3 (not as currently A4) to go to C3, what would change in the formula? Just the $A$4 to $A$3?
    Not very clear for me, you just to give a trial to see what happened and post it again.

    Quote Originally Posted by dansparts View Post
    Is there any easy way to do that, and account for variability in number of characters in strings 2 & 3?
    Assuming string is in A1
    In B1:
    Please Login or Register  to view this content.
    In C1:
    Please Login or Register  to view this content.
    In D1:
    Please Login or Register  to view this content.
    Sharing: Replace any space in string with 100 spaces, then use LEFT(...,100), MID( take 100 chars start from 100) and RIGHT(...,100)
    Last edited by AliGW; 12-10-2017 at 04:19 AM. Reason: Quotation cut down to relevant portion.

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    Quote Originally Posted by bebo021999 View Post
    In B1:
    Please Login or Register  to view this content.
    Note: formula +0 to get real date instead of text

  13. #13
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    Re: INDIRECT function - need help

    That's what I needed!

    I don't want an actual date, but rather the date listed in the text, so what you gave me works fine. The dates in the records are all past dates, nothing current.

    One last item. Can I get rid of leading zeroes in the MID trim? I looked at formatting the column, but didn't see anything. Would I have to do a custom format?

  14. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,390

    Re: INDIRECT function - need help

    Quote Originally Posted by dansparts View Post
    Can I get rid of leading zeroes in the MID trim? I looked at formatting the column, but didn't see anything. Would I have to do a custom format?
    One way to change to real number:
    =formula+0

  15. #15
    Registered User
    Join Date
    12-07-2017
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    16

    Re: INDIRECT function - need help

    Neither of those formulas seems to work correctly.

    The 2nd one got rid of the leading zero in sub-string #2, but re-attached sub-string #3.

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

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