String together Offset

[Confuddled75]

Is there any way I can string together offsets?

For example, starting at A1, I want to go down 4 (to Ben Johnson), none across, then return 5 down and 10 across
so that would be
=offset(A1,4,0,5,10)
but what if I wanted to return details for rows 14 to 15 and 18 to 21 too? is there some way to string them all together?
I'm really new to this so excuse my ignorance & many thank!
Claire

Data.jpg

[xlnitwit]

Hi,

How are you planning to use the result?

[Confuddled75]

Just to show the same data as an array. Nothing fancy like a sum etc. I'm using it rather the pasting the same data, that way, if anything changes in the original sheet it'll update in the new sheet automatically.
Hope that makes sense.
Thanks

[xlnitwit]

I don't know if you can make OFFSET work, but you could use INDEX like this
=INDEX(A1:J32,{5;6;7;8;9;14;15;18;19;20;21},{1,2,3,4,5,6,7,8,9,10})
array-entered into the appropriate number of cells.

[JohnTopley]

Are you wanting to extract data for (variable) list of Salesmen or is it just one salesman at a time.?

Please attach a file (not image) so that respondents (a) do not have to type in data and (b) have data with which test any formulas. Ensure the data is representative of your actual "real world" data.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the (remove confidential) data.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Confuddled75]

Thanks xlnitwit, wonderful stuff! That'll would work perfectly for this, although a little long if I've got a hundred columns... can I shorten this somehow? So, if it went from A1 right across to BH for example could I use a 1;200?!?!

[xlnitwit]

Try replacing {1,2,3,4,5,6,7,8,9,10} at the end with COLUMN($A:$BH)

[Confuddled75]

Hi, I just tried that, it came back with all the data I wanted except in the first column... there were a few #value errors. Hmph!
Why would it work for 99% of the cells?!?!
This is what I used just to test it:
=INDEX(Installations!A1:J32,{4;6;9},($A:$J))

Thanks, you have the patience of a saint!
Appreciated

[xlnitwit]

You missed the COLUMN function
=INDEX(Installations!A1:J32,{4;6;9},COLUMN($A:$J))

[Confuddled75]

Oh dear! It's as simple as that! & it works perfectly! You diamond! thank you very much! :D

[xlnitwit]

You're welcome.

Please remember to mark the thread Solved.