greetings everyone, im having an issue with excel as usual i need to copy a part of a content that has information of sheet 1 to what is needed in sheet 2. its not easy to explain by typing, its explained all in the attachment. hopefully it wont confuse u here. the example there will make it clear. thanks.Book1.xlsx
Basic formula ...
=IFERROR(INDEX('Sheet 1'!$D$2:$J$2,SMALL(IF(('Sheet 1'!$D$4:$J$4<>0)*(MATCH($A$4,'Sheet 1'!$A$4,0)),COLUMN('Sheet 1'!$D$2:$J$2)-COLUMN('Sheet 1'!$D$2)+1,""),ROWS($1:1))),"")
Enter with Ctrl+Shift+Enter
Copy down
Change BOLD ranges to reflect rows 3 and 4
See attached
ive checked what u did and i thank u for that, but 1st i tried adding rows to the 1st sheet bcz we will have more measurement numbers and style numbers and i typed new measurement number and style number in it. in sheet 2, i changed the measurement number 5 to the new measurement number from a new row in sheet1, but the info didnt pop up. plz help me with this issue. thanks.
Last edited by karim.zheng; 10-31-2015 at 02:30 AM.
Add highlighted code to ALL formulae;
=IFERROR(INDEX('Sheet 1'!$D$2:$J$2,SMALL(IF(('Sheet 1'!$D$4:$J$4<>0)*(MATCH($A$4,'Sheet 1'!$A$4,0) *(MATCH($B$4,'Sheet 1'!$B$4,0)) ),COLUMN('Sheet 1'!$D$2:$J$2)-COLUMN('Sheet 1'!$D$2)+1,""),ROWS($1:1))),"")
However this will need amending (or changed completely) to handle multiple entries on Sheet1 so please post a file with more than one entry on sheet1.
VBA might be the solution as the layout does not make it easy to extract the data from multiple entries..
Last edited by JohnTopley; 10-31-2015 at 07:15 AM.
ok ill send u the excel see sheet 3 its exactly like that. but theres a catch i dont kno if itll effect anything, theres the measurement number and style number some times we would change them and the numbers would change with them too. thank u and sorry for using ur time
Last edited by karim.zheng; 10-31-2015 at 08:51 AM.
This is completely different to your original posting.
And I assume there are a number of "Measurement numbers" on sheet3?
If so, I need to know how sheet 3 is formatted with more than one measurement number.
c4=IFERROR(INDEX('Sheet 3'!$3:$3,SMALL(INDEX(((LEFT('Sheet 3'!$H$3:$QR$3,1)<>LEFT(C$2,1))+('Sheet 3'!$H$5:$QR$5=0))*10^10+COLUMN('Sheet 3'!$H$3:$QR$3),0),ROWS(C$4:C4))),"")
d4=IFERROR(INDEX('Sheet 3'!$4:$4,SMALL(INDEX(((LEFT('Sheet 3'!$H$3:$QR$3,1)<>LEFT(C$2,1))+('Sheet 3'!$H$5:$QR$5=0))*10^10+COLUMN('Sheet 3'!$H$3:$QR$3),0),ROWS(C$4:C4))),"")
e4=IFERROR(INDEX('Sheet 3'!$5:$5,SMALL(INDEX(((LEFT('Sheet 3'!$H$3:$QR$3,1)<>LEFT(C$2,1))+('Sheet 3'!$H$5:$QR$5=0))*10^10+COLUMN('Sheet 3'!$H$3:$QR$3),0),ROWS(C$4:C4))),"")
Try this and copy across
see attached file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
sry abt that there was a misunderstanding, anyways this table has an index match formula. there would be only one row for the measurement number and style number. when we write 5 on the measurement number, the style number and other number to the right will pop up. but when we write for example 4, the things to the right will change. the style number and others will change. theyre taken from another refference. so basically we can say that we have only 1 measurement number.
OK: nflSsles has provided the solution you require.
yh but anyways i thank u both for the help and yh this was a headache. i really appreciate the help.
Thanks for the feedback. Please marked as SOLVED ("Thread Tools" at top of thread).
And credit to nflsaless for solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks