this is hard to explain, I have two sheets.
SHEET 1:
A B C
1 HM32498 BlaBla1 John Doe
2 HM34254 BlaBla2 Jane Doe
SHEET 2:
A B C D
1 John Doe ????? BlaBla2 2
2 Jane Doe ????? BlaBla1 1
its much bigger than that but this is enough to explain. There is actually 202 rows of data in mine but w/e. I have a formula to look in row C and compare it with row B then output the row number which it is the same (Column D):
(=MATCH(D3,'Module List'!B$5:B$202,0)+4)
Now i need the B column on SHEET 2 to tell me the right code, i already have the row number i need. So in row B i need something like:
='SHEET 1'!A(D1)
so it will simply output the column A from with the row number in D.
did i make that sound harder than it is? sorry
hope you guys can help
thanks
Sam
You should use INDIRECT function
try:
PHP Code:=INDIRECT("'Sheet 1'!A"&D1)
Last edited by DonkeyOte; 06-09-2009 at 07:57 AM. Reason: removed unncessary quote
tigertiger please do not quote whole posts - there is no need for it and it simply clutters the board. thanks
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
#REF! error
in the show calculation it looks ok. No idea whats wrong
If the Sheet name is not variable don't use INDIRECT as it is Volatile, rather use INDEX:
B1: =INDEX(Sheet1!A:A,D1)
copy down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
works perfectly thanks donkey
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks