Take horizontal info from one worksheet and display vertical

[rdeters]

I have been able to take data from worksheet 1 and with the help of formulas brought it over to worksheet 2. The information on worksheet 1 is in row #1 and columns A through to G, and displayed on worksheet 2 in column A, Rows 1 though to 7. (ie. The info on worksheet 1 is horizontal and displayed on worksheet 2 vertically.) Now when I copy the 7 rows on worksheet #2 and paste them to the next 7 rows, rows 8 through to 14, the formula should pertain to row #2 on worksheet #1, but it is not. I hope this is clear enough. Please help

 

[Jack in the UK]

transpose function might be worth looking at it will re aling for you

 

[rdeters]

There is formulas involved, and the transpose function throws these all out of wack. Is there any other way?

 

[Chris Davison]

are you positive you've worded this correctly ?

I'm trying to replicate it and it looks nearly impossible

 

[Jack in the UK]

if you anchor they will stay ok, if not they might WACK as you say..

lost transpose dont re function the cells it will re align them as you want..

im lost now!

 

[rdeters]

The grid on worksheet 2 is actually 7 rows by 12 columns and there are numerous formulas in each cell that are referencing to cells in worksheet 1. The formulas include 'search', 'concatenate', 'if' statements, etc. The entire grid on worksheet 2 only pertains to 1 row and 7 columns on worksheet 1. I put a dollar sign in each formula before the letter designation for the column, because these are constant, but I can not put the dollar sign in front of the number for the row designation, because the next 7 by 12 grid will be pertaining to row #2 on worksheet 2. I hope this clears it up a bit.

 

[Chris Davison]

Firstly, the reason you're having this problem is because of the interactions of the layouts of the info you require

but hey, we all start somewhere, or all inherit bad designs from various people

My honest advice in this scenario is to copy and paste your grid down and then manually edit the rows in the new formulae

so where you have a 1, change it to an 8 etc etc in all the cells

Hopefully, this will be the most efficient way if you're only copying it down a few times

if, though, you're having to do this quite a few times (liek 20 or 30) I suspect you're in trouble....

with basic links like this, we can use =INDIRECT and tie it to the rounded up row number divided by 8

however, this would need to be incorperated into the fact that your switching from vertical to horizontal

further to that little twist is the fact that you're not just linking : you have IFs and CONCATENATIONS and SEARCHES in there also
which make a "global" solution based on blind info virtually impossible

Looking on the bright side though : one of the best lessons to learn is the need to look ahead in the initial design process so you don't paint yourself into a corner : a bit like snooker players, they look to see where their cue ball will end up *after* they've played their shot before they actually play the shot

if the manual method isn't viable for you, email me your spreadsheet and I'll take a look at it - although it may be to say "no, I'm not capable of solving this"

novulari@hotmail.com

 

[rdeters]

Thanks, chris. I think you're right, I probably have to re-think how I am going to do this. Thanks for the help though guys!