Excel help! (transpose not working?) View previous topic :: View next topic  


NFOHump.com Forum Index - Programmers Corner
Page 1 of 1
Horrordee
Soderator



Posts: 8849
Location: England
PostPosted: Thu, 24th Feb 2011 16:35    Post subject: Excel help! (transpose not working?)
I have this formula in cell A1:

=HLD!F$1&"="&VLOOKUP(HLD!F$1,HIDDEN_LOOKUP!$A$2:$C$307,2,FALSE)&INDEX(HLD!F:F,$C$3)&VLOOKUP(HLD!F$1,HIDDEN_LOOKUP!$A$2:$C$307,3,FALSE)&";"

Which works fine. I can drag it to the right and cell A2 looks like this:

=HLD!G$1&"="&VLOOKUP(HLD!G$1,HIDDEN_LOOKUP!$A$2:$C$307,2,FALSE)&INDEX(HLD!G:G,$C$3)&VLOOKUP(HLD!G$1,HIDDEN_LOOKUP!$A$2:$C$307,3,FALSE)&";"

However, if I copy those two cells, and paste them into B1 with "tranpose" ticked, the code in the INDEX function breaks! Cell B1 code is:

=HLD!F$1&"="&VLOOKUP(HLD!F$1,HIDDEN_LOOKUP!$A$2:$C$307,2,FALSE)&INDEX(HLD!#REF!,$C$3)&VLOOKUP(HLD!F$1,HIDDEN_LOOKUP!$A$2:$C$307,3,FALSE)&";"

And cell C1 code is:

=HLD!G$1&"="&VLOOKUP(HLD!G$1,HIDDEN_LOOKUP!$A$2:$C$307,2,FALSE)&INDEX(HLD!#REF!,$C$3)&VLOOKUP(HLD!G$1,HIDDEN_LOOKUP!$A$2:$C$307,3,FALSE)&";"


Can anyone see why?!?

If I manually cut the cells, and paste they work. It would seem transposing stuff within an INDEX function breaks it?

It's also weird because the code in green isn't effected, and pastes as you'd expect (aka doesn't change).

Space for rent. Contact me for rates!
Back to top
Hfric




Posts: 12017
PostPosted: Thu, 24th Feb 2011 16:45    Post subject:
yeah i see your problem G:G means all numbers in the Row G , but if you copy it to row lets say G1 or H5 you get #REF a error , you need to add $ to G to hold it like this G$:G$

http://spreadsheets.about.com/od/formulatips/qt/REF_error.htm
Back to top
Horrordee
Soderator



Posts: 8849
Location: England
PostPosted: Thu, 24th Feb 2011 16:49    Post subject:
That doesn't work within the function INDEX - i get an error...

Does that mean I'm fucked?

Space for rent. Contact me for rates!
Back to top
Hfric




Posts: 12017
PostPosted: Thu, 24th Feb 2011 17:01    Post subject:
hmm why not try something different
http://www.mrexcel.com/articles/excel-vlookup-index-match.php
Back to top
Horrordee
Soderator



Posts: 8849
Location: England
PostPosted: Thu, 24th Feb 2011 17:17    Post subject:
That wouldn't help.

I'm using index for a good reason... let me show you...

In my other sheet, I could just reference a cell as follows:

HLD!F$5

However, the 5 part of that I would like to change, depending on the value of another cell (in my example above, it's C3).

So I am using index to replace the 5 from HLD!F$5 with the contents of C3, hense:

INDEX(HLD!F:F,$C$3)

This now works when I drag it to the right, but if I transpose, it breaks Sad

Space for rent. Contact me for rates!
Back to top
Page 1 of 1 All times are GMT + 1 Hour
NFOHump.com Forum Index - Programmers Corner
Signature/Avatar nuking: none (can be changed in your profile)  


Display posts from previous:   

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB 2.0.8 © 2001, 2002 phpBB Group