# INDIRECT function - need help

1. ## 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. ## Re: INDIRECT function - need help

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

3. ## Re: INDIRECT function - need help

Originally Posted by dansparts
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. ## 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:
`Please Login or Register  to view this content.`

5. ## Re: INDIRECT function - need help

OK now I got it.

=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. ## 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. ## Re: INDIRECT function - need help

Originally Posted by dansparts
@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. ## 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.

9. ## 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. ## 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. ## Re: INDIRECT function - need help

Originally Posted by dansparts
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.

Originally Posted by dansparts
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)

12. ## Re: INDIRECT function - need help

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

13. ## 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. ## Re: INDIRECT function - need help

Originally Posted by dansparts
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. ## 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.

Page 1 of 3 1 2 3 Last

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

#### 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