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

Report
Posts
8
Registration date
Monday October 19, 2015
Status
Member
Last seen
October 31, 2015
-
Posts
1
Registration date
Friday April 22, 2016
Status
Member
Last seen
April 22, 2016
-
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 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
8
Registration date
Monday October 19, 2015
Status
Member
Last seen
October 31, 2015

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)
Posts
8
Registration date
Monday October 19, 2015
Status
Member
Last seen
October 31, 2015

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
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
8
Registration date
Monday October 19, 2015
Status
Member
Last seen
October 31, 2015

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,..
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
8
Registration date
Monday October 19, 2015
Status
Member
Last seen
October 31, 2015

Thanks
Posts
1
Registration date
Friday April 22, 2016
Status
Member
Last seen
April 22, 2016

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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!