Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-10-2019, 09:37 AM
Izzii0x Izzii0x is offline Tell me which place the vlookup was successful (within a nested vlookup) Windows 10 Tell me which place the vlookup was successful (within a nested vlookup) Office 2016
Novice
Tell me which place the vlookup was successful (within a nested vlookup)
 
Join Date: May 2019
Posts: 13
Izzii0x is on a distinguished road
Default Tell me which place the vlookup was successful (within a nested vlookup)

Hi guys,



So I have a few sheets which contain data that has been delegated amongst various staff, and staff may be using this data on different workbooks and making notes.

I wanted to create a spreadsheet for searching for the data - i.e. someone can go in and type customer name John Doe and it would return "In document A" and then perhaps another column would bring back the notes from that customer.

I understand how to do the nested vlookups to see info from whichever document the data is in - but how can I create a formula which tells me where the vlookup has been successful? I found this on the web but I can only get it to work for 1 place and when I try to create "if false then..." scenarios it doesn't seem to work.

Code:
=IF(ISNA(VLOOKUP(A2,$E$2:$E$185,1,FALSE)),"False","TRUE")
Any help would be greatly appreciated! I don't have any tester data now as so far just testing it out with writing in 4 columns "A", "B", "C", "D" and trying to use the formula above to tell me which column the letter I type into the box is - with no luck!

Thanks in advance!
Reply With Quote
  #2  
Old 07-11-2019, 01:50 AM
ArviLaanemets ArviLaanemets is offline Tell me which place the vlookup was successful (within a nested vlookup) Windows 8 Tell me which place the vlookup was successful (within a nested vlookup) Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Am I right assuming, you have several users, everyone of them working with his/her own workbook with some standard design. And you have some manager working with some main workbook, where he is setting tasks for users? And this manager needs a worksheet, where he/she can see the status of all tasks?


When yes, then I have done something like this before.


All workbooks must be available for all people involved and stored in local network resource(s);
It will be best to use defined Tables in all workbooks;
To exchange data between main and users workbooks, when the total number of tasks is some hundred, you may go with links to read data, otherwise you have to use SQL queries;
In main workbook must be a table where all tasks are entered, with a field for some ID of users, tasks are assigned to. All tasks must also have some unique ID;
In user workbooks must be along with task table a sheet with a table which reads all assigned tasks for particular user from main workbook (main info table). This table must have a column which indicates, is the task present in tasks table or not. And in case links are used, this table must have enough empty rows prepared to easily last for any possible number of task for this user in workbooks lifetime;
In tasks table, a validation list must be used to select task ID-s from main info table, and there must be some indicator (e.g. Conditional Formatting) which indicates any double entries of task id, so user can avoid them;
In main workbook must be hidden sheets for every user workbook, where info from task tables of users workbooks is read. Again, when links are used, there must be enough rows prepared for every user;
Then in main workbook another hidden sheet is needed, which reads info from user tasks hidden sheets (summary sheet). Maybe you can use UNION query for this - I haven't tried this. Or you must have enough predefined rows with formulas (like you have 3 user sheets with 100 predefined rows for every one, then in this sheet 1st 100 rows are reading data from 1st user sheet, next 100 rows are reading data from 2nd user sheet, etc. And to avoid rows having no data displayed, you have a column where row number is calculated. And user id must be there for non-empty rows too;
The last step is add columns into tasks table of main workbook, which are reading data from summary sheet.


Now manager can use autofilter to get whatever info he/she wants to see. When there is a need for some summary info for made selections, you can place some fields with SUBTOTAL() formulas at top of page (leave an empty row between table header and summary fields) and freeze headers and all above them.


Edit: Both links and queries read data from saved file on disk somewhere. When user works with file, he/she is working with virtual copy of file in his/her computer's memory. The changes made in this virtual copy are written into file only when saved. When there is need for operative info to be read at another file, then the user working with file has to save frequently!

Last edited by ArviLaanemets; 07-11-2019 at 02:35 AM. Reason: Additiona advice
Reply With Quote
  #3  
Old 07-18-2019, 10:29 PM
Izzii0x Izzii0x is offline Tell me which place the vlookup was successful (within a nested vlookup) Windows 10 Tell me which place the vlookup was successful (within a nested vlookup) Office 2016
Novice
Tell me which place the vlookup was successful (within a nested vlookup)
 
Join Date: May 2019
Posts: 13
Izzii0x is on a distinguished road
Default

Thanks so much for your reply, apologies for my late response.. I think I may have also made it sound more complex than it needs to be.

As there shouldn't be any duplicates between workbooks (e.g. lets say we have 100 companies and theyre equally split between 4 workbooks, they may have 25 each or could be 30, 30, 30 and 10 but never totalling over 100). So maybe when I paste them into their workbook for them to use I could create a column called location and write the name of the person using that workbook, that way I can just do nested vlookups and bring back that column?

Think that could work.... will have to test and see
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Tell me which place the vlookup was successful (within a nested vlookup) vlookup nested formula paxon Excel 1 09-14-2018 12:09 AM
Tell me which place the vlookup was successful (within a nested vlookup) VLookup and Nested IFs or Index Function SavGDK Excel 10 04-08-2016 10:06 PM
Tell me which place the vlookup was successful (within a nested vlookup) using if and nested vlookup wentworth16238 Excel 5 06-14-2015 12:59 PM
Nested Vlookup Help asluder2000 Excel 2 11-14-2014 11:00 PM
Nested vlookup with varable tables! Dave Jones Excel 0 08-30-2012 09:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:12 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft