Cell reference columns-rows?

Attach a sample workbook. 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 data.

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

For something like this, I tend to prefer the INDEX() function over the INDIRECT() function. I also tend to prefer to include a helper row and column for this.

INDEX() function help file: https://support.office.com/en-us/art...2-b56b061328bd
1) I would insert a row at the top of sheet2 and a column at the left of sheet 2.
2) Recognizing that the "Jack"s values are always in row 1, Mate's values are always in row 3, and Kate's values are always in row 6, I would put these row numbers in that top row in sheet2. These will go into the row_number argument of the INDEX() function.
3) The first record in sheet2 comes from column B (column 2), and each successive record is 3 columns across from the previous (unless your picture is not representative of the entire data set). In the helper column I inserted in step 1, I would put 2 in A2, the put =A2+3 into A3 and copy that formula down as far as needed.
4) Then the table fills in just fine with a simple INDEX() function =INDEX(sheet1!$A$1:$AA$6,B$1,$A2) copied down and across. Note the mix of relative and absolute references to make copying simple.

That's how I'd do it. Others would use the ROW()/ROWS() and/or COLUMN()/COLUMNS() and/or other lookup/counting functions to come up with the row and column numbers. You can explore those options if you like.

i like OFFSET.

In shhet 2, a2, coped across and down:

=OFFSET(INDEX(Sheet1!$A$1:$A$20,MATCH(A$1,Sheet1!$A$1:$A$20,0)),,1+3*(ROWS($1:1)-1),,)

see sheet.

Great job, mr.Glenn! As always!
Now, what about MrShorty post#3 solution, actually, i could add top row and first column with numbers for rows/columns count. I already have TODAY() inside workbook, so is it volatile or not isn't realy important.


Ps. Great problem, i again &again&again forgetting to specify what i actually need My sheet1 tables will be "dynamic". Macro will insert first 3 columns and copy to a1b1 NEW table, so the whole tables will move to right. So your formula probably will not work

Run it in your real sheet. I think mine will work. You might need to adjust the ranges of the INDEX-MATCH. But since I'm using I-M to locate the first "Jack", I'm reasonably confident that it'll be OK.

It might not make any difference, but the formula should be anchored on the columns too:

=OFFSET(INDEX(Sheet1!$A$1:$A$20,MATCH(A$1,Sheet1!$A$1:$A$20,0)),,1+3*(ROWS($1:1)-1),,)

One last shot:

=OFFSET(INDEX(INDIRECT("Sheet1!A1:A20"),MATCH(B1,INDIRECT("Sheet1!A1:A20"),0)),,1+3*(ROWS($1:1)-1),,)

@B.W.B.

Not an anwer to your question, but

You add the data to a new sheet.

The columns are related to dates?

If so that information is not important, since you did not pull that information over?

The reason I ask you that is since you have VBA in your file, you maybe could use VBA to re-arange the data.

After that a pivot table could analyse the date.

I'm on the move for most of the day. I'll take a look later on.

OK. Back again. I couldn't get anything to work.

Option 1. Change your macro to add columns on to the RIGHT of previous data.

Option 2. Learn to love volatile functions.

I don;t immediately see the pattern here. Is this still subject to the "add 3 columns at the left" thing??

if not, see sheet.

If yes: your life (and mine!!) would be simpler if the new data were always added left to right

A non-editable picture is useless. I can't really see what's going on.

I will not work with a non-editable picture. Post a sheet.

in List2, A2, copied down:

=OFFSET(SPEC_pričuva!I$4,52*INT((ROWS($1:1)-1)/3),,,)

In List 2, B2, copied down:
=OFFSET(SPEC_pričuva!A$26,MOD(ROWS($1:1)-1,3)+52*INT((ROWS($1:1)-1)/3),,,)

In List 2, C2, copied across and down:
=OFFSET(SPEC_pričuva!C$26,MOD(ROWS($1:1)-1,3)+52*INT((ROWS($1:1)-1)/3),,,)

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Scrap that. use this instead.

A2
=INDEX(SPEC_pričuva!I$4:I$10000,1+52*INT((ROWS($1:1)-1)/3))

B2
=INDEX(SPEC_pričuva!A$26:A$10000,1+MOD(ROWS($1:1)-1,3)+52*INT((ROWS($1:1)-1)/3))

C2, across and down:
=INDEX(SPEC_pričuva!C$26:C$10000,1+MOD(ROWS($1:1)-1,3)+52*INT((ROWS($1:1)-1)/3))

It's HOT, nearly done, but, there will not always be 3 rows(The, Last, Row), In Sheet1(List1) there are 18 rows(blank) or 3 populated for that day and 15 blank. How to amend blank and cell referenced only populated based on A column Kupac(Buyer, customer)?

Glad you got sorted.... but. Please read rule 8:

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.