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

[Chuck1960]

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:

Box

app.box.com

 

[Chuck1960]

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

 

[Peter_SSs]

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]

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.

 

[Peter_SSs]

Duh.. I had one letter wrong on a page name. Thanks

Easy enough to do but glad you found the problem. Thanks for letting us know.

 

[Chuck1960]

Easy enough to do but glad you found the problem. Thanks for letting us know.

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. ​

​

 

[Peter_SSs]

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

 

[Chuck1960]

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]

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?