I want to retrieve value from multiple sheets of same workbook [Solved/Closed]

Gokuneo 8 Posts Monday October 19, 2015Registration date October 31, 2015 Last seen - Oct 19, 2015 at 07:50 AM - Latest reply: Mikkelo 1 Posts Friday April 22, 2016Registration date April 22, 2016 Last seen
- Apr 22, 2016 at 07:25 AM
Hello Everybody,.. I have a situation where I request your support,

My active workbook has 4 working sheets (sheet1- Master, sheet2, sheet3, sheet4)

sheet2(Delhi Zone): It contains serial_no, start_price, new_price as
1 669 1234
2 639 7894
3 695 9654
4 586 9875
and so on till 33 enteries(Table is already generated)

sheet3(UP Zone): It contains serial_no, start_price, new_price as
1 568 2134
2 539 9594
3 582 3554
4 594 4375
and so on till 24 enteries(Table is already generated)

sheet4(MP Zone): It contains serial_no, start_price, new_price as
1 138 2314
2 964 6952
3 753 5638
4 395 7854
and so on till 19 enteries(Table is already generated)

sheet1(Master):It asks for user input and requirement is result

Q1: Search for new_price from the workbook old price is known. Ex. if old_price is 753, what is the new_price (Need to enter in sheet1-Master and search from sheet4)

Q2: Find lookup value of new_price of serial_no(3- taken as input) of sheet3(Taken as input), from sheet1 itself

If same can be achieved by any formulas, please assist. Thanks in advance
See more 

9 replies

TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 19, 2015 at 11:44 AM
0
Thank you
Hi Gokuneo,

Formula for Q1, where search value (old price) is in A2:
=VLOOKUP(A2,'MP Zone'!B2:C5,2,0)

Formula for Q2, where search value (serial no) is in A3:
=VLOOKUP(A3,'UP Zone'!A2:C5,3,0)

Best regards,
Trowa
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 22, 2015 at 11:03 AM
0
Thank you
Hi Gokuneo,

Sorry to hear that, but just saying it doesn't work isn't very helpful.

This is how I envisioned your query:
http://ge.tt/5tztAVQ2/v/0?c

What can you add to get us closer to the solution?

Best regards,
Trowa
Gokuneo 8 Posts Monday October 19, 2015Registration date October 31, 2015 Last seen - Oct 23, 2015 at 06:23 AM
Just to re-iterate it better

start_price will be unique for each sheet, but entered value can be repeated in sheet2, or sheet3, or sheet4, so user input from either of sheet (not Master), needs also to be taken as input.

As for as the second query goes, user needs to find new_price of item having serial_no (3) of sheet3. So if user enters items serialno(3)- list shown in master sheet, also enters reference sheet number(sheet3 in this case), ----- 5638 should appear(new_price)
Gokuneo 8 Posts Monday October 19, 2015Registration date October 31, 2015 Last seen - Oct 23, 2015 at 07:24 AM
Solution to first query is working well, thanks a ton!

For second query, if we could take user input of the sheet (sheet3 or UPZOne or any other)from where the search has to be initiated, would be great as the list is long.

Thanks in advance
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 26, 2015 at 01:21 PM
0
Thank you
Hi Gokuneo,

OK, for that,
A7: input serial number
B7: input sheet name
C7: formula: =VLOOKUP(A7,INDIRECT("'" & B7 & "'!"&"A2:C5"),3,0)


Or refer to the file below:
http://ge.tt/7d8QCjQ2/v/0?c

Best regards,
Trowa
Gokuneo 8 Posts Monday October 19, 2015Registration date October 31, 2015 Last seen - Oct 27, 2015 at 12:42 AM
Thanks a ton!! If possible please share approach on mail for how can I make small drop down (retractable) which if I understand content of sheet is taken from sheet list in Master from the same file,...

Glad you could spare time,..
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 27, 2015 at 12:13 PM
0
Thank you
Hi Gokuneo,

Please refer to the link below on how to create drop down lists:
http://www.excel-easy.com/examples/drop-down-list.html

Best regards,
Trowa
Gokuneo 8 Posts Monday October 19, 2015Registration date October 31, 2015 Last seen - Oct 27, 2015 at 02:20 PM
Thanks
Mikkelo 1 Posts Friday April 22, 2016Registration date April 22, 2016 Last seen - Apr 22, 2016 at 07:25 AM
If anyone's stumbling upon this guide and wondering how to do this in Excel 2016, we just published a guide on how to do this right here: http://spreadsheeto.com/drop-down/

It's my experience that with a little of practice, creating a drop-down can (and should) be done in 1 minute or less.