Previous Topic Next Topic

Excel - Help Please! Drop Down and Data Filling

Solved/Closed
CatherineGPR Posts 2 Registration date Thursday February 19, 2015 Status Member Last seen March 5, 2015 - Mar 4, 2015 at 07:23 PM
 CatherineGPR - Mar 13, 2015 at 01:27 AM
Good morning/afternoon.

I was hoping you would be able to assist me with a query please.

Currently my team is using 2 spreadsheets, one that lists a publication with a figure.

Another sheet we use to report on items we get in a publication and manually fill in the corresponding figure.

Is there a way I can use an excel function to have these 2 sheets speak to one another, firstly by offering drop down options for publications and secondly for the figure to be automatically filled in the next column when the publication name is chosen from the drop down.

Thank you for your assistance.

Catherine





Related:

1 response

Answer 1 / 1
cmarzahn Posts 35 Registration date Wednesday February 18, 2015 Status Member Last seen March 13, 2015 7
Mar 5, 2015 at 08:58 PM
And good afternoon to you...

I think I have an idea what you want. A couple of questions. Can the spreadsheets be in the same workbook? Are there likely to be multiple placements in the same pubs?

Here's a link to a sheet on DropBox that should give you an idea of where you might go...

https://www.dropbox.com/s/7ku36tb2l8lca74/PR%20Book%20Test.xlsx?dl=0

Hope that helps.
0
CatherineGPR Posts 2 Registration date Thursday February 19, 2015 Status Member Last seen March 5, 2015
Mar 5, 2015 at 09:20 PM
Good afternoon cmarzahn,

Thank you very much, this is exactly what I am after, it is very helpful. I was not sure if VLOOKUP was the correct function but it appears it is. :)

How make the drop down items on the Pubs with Lookup tab?

Thank you once again, this will save our team lots of time!

Catherine
0
cmarzahn Posts 35 Registration date Wednesday February 18, 2015 Status Member Last seen March 13, 2015 7
Mar 7, 2015 at 10:57 AM
VLOOKUP is one way to solve it but it does have limitations. If you don't have an exact match or if the value occurs twice may require some additional work.

To add more lookup cells just copy it down the colum. Be careful of the range of data on the first page changing and not updating formulae on the other page. A better way to do it might be to use range names for the List.

I've included a link to "RevA" which illustrates that thought.

https://www.dropbox.com/s/kawrvk4edivpnkm/PR%20Book%20Test%20RevA.xlsx?dl=0

Enjoy!
0
CatherineGPR
Mar 13, 2015 at 01:27 AM
Than you once again! It is such a help
0

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses