Validation

**STBTC** · 01-06-2016, 08:35 PM

I have included a workbook. The final tab marked "TOTAL" will be for viewing purposes & shouldn't be included in any subsequent formulae that may arise. As far as current data in the workbook, everything is to remain as is; id est, nothing is to be taken away. I shall do all the hard work if someone can show me one example on which I can expand.
I need a validation that will not all any cells to populate until all previous dates' cells are populated. In the attached file, I purposefully left cell B7 in Sheet S1 blank for this. I need it where, if one tries to populate any cells for date "Thursday, January 07, 2016" across all sheets S1:S6, a stop message will alert one to fill all previous dates' cells before one can proceed. It will then apply for "Friday, January 08, 2016" across sheets S1:S6 et cetera. I hope I have explained it well. Again: I just need one example on how to do it, so I can learn it. Thanks in advance, everyone! :D Microbiology Daily Tally 16.xlsx

**FDibbins** · 01-06-2016, 08:58 PM

Data Validation in 2007 is unable to reference other sheets directly, you would need to create range names and reference them - but I think that the volume of ranges needed here would make that impractical. You dais "nothing to be taken away", but howe about adding a helper column?

**FDibbins** · 01-06-2016, 09:19 PM

This is the kind of formula you could use in your helper, I used column K2, down. This counts how many entries there are in that row, across all sheets.
=COUNTA('S1:S6'!B2:J2)

Then the DV would be...
=$K2=$K$1

This compare each row above count with the heading count - if they match, you can enter data, if they dont, you cant

**STBTC** · 01-07-2016, 05:23 PM

Alas, it still doesn't work. My apologies that my employment uses (what seems to be) an obsolete version of Excel. Does anyone have any pointers?

**FDibbins** · 01-07-2016, 05:27 PM

2007 is not obsolete, any more than a 2007 porche is obsolete...there are just later versions out, with more features

I see you have differing number of columns on those sheets - is that the real case, of just something from your sample?

**FDibbins** · 01-07-2016, 05:49 PM

See the attached where I have applied DV to trhe 1st sheet

I adjusted my count (which goes across all of your sheets) to count out to L

I mstakely said to put the COUNTA formula in row 2, it should go into row 1 (M1)

**STBTC** · 01-07-2016, 07:04 PM

You're awesome, FDibbins! I tested it. Is there a way to not allow the person to continue? It seems like it is a way around it, so to speak.

**FDibbins** · 01-07-2016, 08:47 PM

Change the Error Alert (3rd tab in the DV window) change the Style from Warning to Stop

Thanks for the re4p and kind words, always appreciated

**STBTC** · 01-13-2016, 04:41 PM

I have six sheets. I need data validation that will not allow input of data to a row until the previous row across all sheets contain a value.
Exempli gratia: Cell A2 of Row 1 across Sheets 1 through 6 have to contain a value before Row 2 of any sheet can be populated. Subsequently, cell A2 of Row 3 on any sheet cannot be populated until Row 2 across all sheets is populated. As the rows are by date--the dates already fill column A--the validation would extend across the workbook for the whole year. There are more columns involved, but I made it simple as I want to learn how to do it. ANY help is highly appreciated. I have worked on this for weeks to no avail. I have Excel 2016, but this for my work which uses Excel 2013. Thanks!

**LeoTaxi** · 01-13-2016, 05:08 PM

in the version I use is cell A2 always at row 2

Kind regards
Leo

**FDibbins** · 01-13-2016, 05:55 PM

How is this different from your other thread that I helped you on?
http://www.excelforum.com/excel-prog...ml#post4282055
You seemed happy with what I gave you?

**STBTC** · 01-13-2016, 08:11 PM

In elation, I was too quick to respond. I have perused the Internet and found that there is an INDIRECT function that can be used to refer to multiple worksheets or workbooks. My only problem is to create a formula that would adjust with the rows as the dates progressed. I tried to go off what you offered: the IF(COUNTA('Sheet1"!B2,C2)+1 approach, but to no avail (for me anyway). Do you have an idea which I could used to create and facilitate a formula?

Thanks, sir.

**FDibbins** · 01-13-2016, 08:28 PM

OK, seeing as this is the same/continuation as your other thread, I am going to merge these 2

**FDibbins** · 01-13-2016, 08:32 PM

Yes you could use INDIRECT(), but it will still only reference 1 sheet at a time, you will need a list of sheet names for it to work with.

Also not that my formula was this...
=COUNTA('S1:S6'!B2:J2)
not this...
COUNTA('Sheet1"!B2,C2)
see how I am using a sheet "range" reference there? 'S1:S6' not just Sheet1. That is referencing all sheets from S1 to S6, all you need to do is extend that to your last sheet

**STBTC** · 01-14-2016, 07:19 PM

So, if I use =INDIRECT(COUNTA('S1'!B2:K2,'S2'!B2:M2...)) et cetera as my Data Validation formula, it'll reference Sheets one through six? Wait. It would be =INDIRECT(COUNTA('S1'!["helper column"],... Crap. I'm so lost. I have to create a helper column for every sheet (that I would hide), too, right?

**FDibbins** · 01-14-2016, 07:45 PM

Originally Posted by STBTC

So, if I use =INDIRECT(COUNTA('S1'!B2:K2,'S2'!B2:M2...)) et cetera as my Data Validation formula, it'll reference Sheets one through six? I have to create a helper column for every sheet (that I would hide), too, right?

All INDIRECT() does is let you use text in a formula, something excel will normally not let you do.

I did give you a suggestion on how to do this with that version of excel - using a helper column on each sheet to run the count, then use the Custom DV with =$M2=$M$1

What are you having a problem with, and what do we still need to do on this?

**STBTC** · 01-15-2016, 08:01 PM

FDibbins, I have created a helper column in each sheet. How do I use the INDIRECT function in Data Validation that uses all the sheets. I tried to create a formula off what you gave me, but the reference value returns as FALSE in the Error Checking and it displays a #REF.

The formula I used in Data Validation:
=INDIRECT('S1'!$Z1=$Z$1,INDIRECT('S2'!$Z1=$Z$1,...)))))) for all six sheets

**FDibbins** · 01-15-2016, 08:55 PM

My 1st reply on this thread was this...

Originally Posted by FDibbins

Data Validation in 2007 is unable to reference other sheets directly, you would need to create range names and reference them - but I think that the volume of ranges needed here would make that impractical. You dais "nothing to be taken away", but how about adding a helper column?

Using INDIRECT does not change that, you simply cannot reference other sheets with 2007 DV

**FDibbins** · 01-15-2016, 08:56 PM

Based on the file I uploaded in post 6, what is not working?

Validation

LinkBack

Thread Tools

Rate This Thread

Display

Validation

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Data Validation on Six Sheets

Re: Data Validation on Six Sheets

Re: Data Validation on Six Sheets

Re: Data Validation on Six Sheets

Re: Data Validation on Six Sheets

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Re: Validation

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Using Dependant Validation. Show error or blank if Inital Validation Changes

Date and count validation in single cell - Custom Validation

Data Validation - Alternative Methods to View Validation Content

validation rules not working when someone copy paste data on validation cell

Stop Worksheet_Change macro if validation not met in Data Validation

data validation-Can the source of the validation criteria be dfrom a different sheet?

Data validation - when copied & pasted validation overlooked.

Validation Data using Validation Table cell range.....

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions