Lookup & IF help..

[sammarshall]

Hey guys,

I have a spreadsheet that has list of premises names in column A and the date they were last inspected in column C. There are 17 sheets (separated into areas).

What i would like to do is pull through any premises onto an index sheet if there is no date in column C or the date is older than 12 months.

Any advice/help would be greatly appreciated!!

[nevi]

I can think of a way to do this, but I can't think of how to explain it shy of typing a huge novel here... Attach a sample of your sheet here and I'll fix it up and re post it for you.

[sammarshall]

Hey Nevi,

Thanks alot, i've attached a spreadsheet for you to look at.

Cheers

Sam

[NBVC]

See attached for a non vba option...

First in any free column of the Index sheet, list the names of the 17 sheets.

Then select those names and go to Insert|Name|Define and name the range: TabNames.

Then select Sheet2 and hold the Shift key down and select the Last sheet.. so that all but the Index sheet are selected.

In A3 enter:

Please Login or Register  to view this content.

and copy down as far as you want to ensure all items in all pages are accounted for and some extra.

Right click any tab and select Ungroup Sheets.

Then go to Sheet 3 tab and in A1 enter:

Please Login or Register  to view this content.

Do this for each tab except the Index tab and the first tab of Premise data...

Note for each A1 you are referencing the prior sheet's column A.

Then in a free cell of Index tab, say D3, enter formula:

Please Login or Register  to view this content.

where Sheet4 represents last of the 17 sheets.

Then finally, in A2 of Index tab enter:

Please Login or Register  to view this content.

adjust the A20 and D20 in the formula to reference the last row that you think will be occupied in any sheet.

Then hold the CTRL and SHIFT key down and press enter.

In B2 enter:

Please Login or Register  to view this content.

and adjust similarly and confirm again with the CSE keys.

Then select both cells and copy the formulas down as far as you want.

Select then column B of Index sheet and custom format as: mm/dd/yy;-0;;@ to get rid of 0's and format remainder as dates.

[sammarshall]

Top job!

Thanks very much