VLOOKUP? Not sure If I need VLOOKUP or a Macro..

Chuck1960

New Member
Joined
Aug 14, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
I am trying to put together a workbook with multiple pages where I can search for a date on my Summary page and return all instances of the date with the corresponding name and other info on the same row. I am using a lookup sheet with the page names but it only seems to return the first page that encounters the date over and over. I thinking I need a macro but I don't know anything about them.

An help would be appreciated

Chuck

Here is the formula Ive tried for the page name. The other cells use just the column number for the info on that row.
{VLOOKUP($B$3,INDIRECT("'"&INDEX(Lookup_Sheets,MATCH(1, --(COUNTIF(INDIRECT("'"&Lookup_Sheets&"'!$I$2:$I$300"),$B$3)>0),0))&"'!$I$2:$M$300"),4,FALSE)}


Here the example file:
 

Chuck1960

New Member
Joined
Aug 14, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Peter,

My script has suddenly stopped working. I have made no changes to the VBA, just added pages to the workbook and to the Lookup_sheets. Here are some screen shots. Let me know what I should do. Is it possible to delete the VBA from this workbook and re add it? It seems that I have 2 folders now. I Can toggle folders and see two distinct folders.

Chuck
 

Attachments

  • debug.PNG
    debug.PNG
    77.3 KB · Views: 2
  • Error.PNG
    Error.PNG
    48.5 KB · Views: 2
  • toggle folder.PNG
    toggle folder.PNG
    90.1 KB · Views: 2

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
That error message would make me think that column A of 'Lookup_Sheets' contains at least one cell that is either blank or contains text that does not match one of the the sheet names in your workbook. When you get the error and click Debug, hover over the text 'cell.Value' in that yellow line and the pop-up message should reveal the problem value.
 

Chuck1960

New Member
Joined
Aug 14, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Peter,

My script has suddenly stopped working. I have made no changes to the VBA, just added pages to the workbook and to the Lookup_sheets. Here are some screen shots. Let me know what I should do. Is it possible to delete the VBA from this workbook and re add it? It seems that I have 2 folders now. I Can toggle folders and see two distinct folders.

Chuck
That error message would make me think that column A of 'Lookup_Sheets' contains at least one cell that is either blank or contains text that does not match one of the the sheet names in your workbook. When you get the error and click Debug, hover over the text 'cell.Value' in that yellow line and the pop-up message should reveal the problem value.
Duh.. I had one letter wrong on a page name. Thanks.
 

Chuck1960

New Member
Joined
Aug 14, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Easy enough to do but glad you found the problem. Thanks for letting us know. (y)
Hi Peter,
For some reason, every morning when I open the script, to add more info,
it comes up with a compile error. Ths is before I add info.
The script works when I save it in the evening, but when I reopen it the next day, it doesn't. I have been having to open a previous version and redoing any work from that time forward. An suggestions.
 

Attachments

  • error.PNG
    error.PNG
    29.2 KB · Views: 6
  • debug.PNG
    debug.PNG
    73.9 KB · Views: 4

Chuck1960

New Member
Joined
Aug 14, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
I'm not familiar with that error or what may be causing it. Might be worth trying something along these lines: "Invalid forward reference, or reference to uncompiled type." error when referencing several sheets
It doesnt stop on the same page every time. I seems t be a counting issue. It works for so many lookups then stops. I can replace the code and it works again for a number of lookups then quits. Im on a roaming profile so the spreadsheet is saved onm the server. IM wondering if that may have something to do with it - Chuck
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I'm not sure that I can add anything further. Did you try recreating the sheet(s) as suggested in the link I provided in case any/all of your sheets have become corrupted in some way?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,724
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top