Previous Topic Next Topic

I want to retrieve value from multiple sheets of same workbook

Solved/Closed
Gokuneo Posts 8 Registration date Monday October 19, 2015 Status Member Last seen October 31, 2015 - Oct 19, 2015 at 07:50 AM
Mikkelo Posts 1 Registration date Friday April 22, 2016 Status Member Last seen April 22, 2016 - 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

4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 19, 2015 at 11:44 AM
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
0
Answer 2 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 22, 2015 at 11:03 AM
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
0
Gokuneo Posts 8 Registration date Monday October 19, 2015 Status Member Last seen October 31, 2015
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)
0
Gokuneo Posts 8 Registration date Monday October 19, 2015 Status Member Last seen October 31, 2015
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
0
Answer 3 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 26, 2015 at 01:21 PM
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
0
Gokuneo Posts 8 Registration date Monday October 19, 2015 Status Member Last seen October 31, 2015
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,..
0
Answer 4 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 27, 2015 at 12:13 PM
Hi Gokuneo,

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

Best regards,
Trowa
0
Gokuneo Posts 8 Registration date Monday October 19, 2015 Status Member Last seen October 31, 2015
Oct 27, 2015 at 02:20 PM
Thanks
0
Mikkelo Posts 1 Registration date Friday April 22, 2016 Status Member Last seen April 22, 2016
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: https://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.
0

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
How to retrieve deleted messages from Truecaller
Cuttima -
dwebb -

2 responses