EXCEL: bloccare il formato di una cella ma non il contenuto

[avalu81]

Buongiorno a tutti, ho già provato a leggere le discussioni apparentemente simili alla mia ma non ho trovato risposte utili.
Vi spiego il mio problema.
Sto preparando un file per una gara d'appalto. il file, protetto in tutte le sue parti non modificabili, prevede però che i concorrenti possano inserire dei valori numerici relativi all'offerta economica sui vari punti della gara.
una volta inseriti i singoli valori, in automatico viene calcolata la somma.
Le celle per l'inserimento dei valori hanno formato valuta (ma potrebbe essere anche contabilità) e no sono bloccate in quanto devono essere inseriti i valori.
le celle sono impostate con il simbolo degli euro e due numeri decimali separati da virgola.
il problema è questo: il valore della somma viene comunque restituito, indipendentemente da come vengono inseriti i parziali, salvo non conteggiare quei valori che non corrispondono al formato che io ho dato alla cella.

1) situazione normale in cui i concorrenti non fanno casini
cella 1: 1000,50 euro
cella 2: 1200,30 euro
cella 3: 1300,00 euro
somma: 3500,80 euro

2) situazione in cui il concorrente sbaglia ad inserire i valori
cella 1: 1000,50 euro
cella 2: 1200.30 euro (separatore decimale punto invece dalla virgola)
cella 3: 1300,qq euro (lettere al posto dei numeri)
somma: 1000,50 euro

Se la mia somma automatica è un trascinamento su tot celle, la somma viene eseguita, senza alcun avviso sulle sole celle giuste.
Se la mia somma automatica è invece una selezione puntuale di caselle allora mi viene restituito "#VALORE". già questo sarebbe un passo avanti per chi poi deve controllare l'offerta, perché avrebbe subito evidenza dell'errore. Il problema però è che ciò avviene solo se seleziono una per una le caselle degli addendi e solo se uno di questi ha delle lettere al posto dei numeri.
se invece il numero è scritto con il punto invece che con la virgola, il formato cella viene trasformato da valuta a data e rimanendo comunque un numero, non viene conteggiato, ma non restituisce "#VALORE" sulla somma finale.

Capite che chi poi dovrà andare a controllare l'offerta, in caso di 100 celle da sommare, magari ne salta solamente una, ma se è non ci sono lettere rischia di non accorgersene, con tutti i problemi del caso.

Esiste la possibilità di imporre ad Excel di accettare nella cella il solo valore numerico in formato valuta e qualsiasi altra cosa restituisce un errore?
o più semplicemente si può agire sulla casella della somma automatica con qualche formula "SE..." particolare?

Grazie a tutti

[marino51]

puoi usare la formattazione condizionale, per esempio, se la casella è vuota o contiene un valore non numerico, "si tinge di rosso" ovvero imposti il riempimento in rosso,
questa mi sembra la soluzione più semplice

se non vuoi agire sulle singole celle, puoi avere anche una colonna(nascosta) che contiene il risultato del controllo già detto, la colonna potrebbe contenere valori 0 se le celle sono ok e 1 se le celle sono errate quindi poi impostare una cella "semaforo", sempre con formattazione condizionale, verde / rossa in funzione della somma = 0 / >0

terza possibilità, puoi scrivere una breve macro che viene attivata con la modifica dei valori, in questo caso puoi inviare anche un messaggio di alert e riposizionare "l'attività" sulla cella sbagliata
allo stesso modo puoi fare dei controlli quando viene salvato il file, in modo da avvertire che ci sono incongruenze

[avalu81]

provo a studiare la formattazione condizionale: è la prima volta che ne sento parlare, perché fino ad oggi non avevo mai avuto necessità di impostare un file talmente complesso e talmente sensibile.
Posto che non ho idea di come si possa scrivere la regola "se non è un numero, coloralo", nel caso in cui si scrivesse un numero con il punto al posto della virgola, il valore viene trasformato in data, che viene considerata ancora un numero, quindi rimarrebbe non evidenziata....

[marino51]

la formattazione condizionale è "banale" troverai la soluzione facilmente

per quanto riguarda la data ...
idate_1.PNG
per controllare se una cella contiene una data, puoi creare una nuova funzione che rimane nel tuo "workbook", ovvero quando apri "quel" file excel, la funzione sarà presente

come fare è semplice, ti indico passo-passo con la versione 2007

. con il tuo file excel aperto, premi i tasti alt-f11, ti si apre la finestra "visual basic"
idate_2.PNG

. nella parte in alto a sinistra trovi "VBAProject(NomeCartella)", il nome è ovviamente quello che hai assegnato tu

. cliccalo con il tasto destro del mouse -> inserisci -> modulo

. viene creato un "modulo1"

. nella parte testo inserisci la funzione senza modificare i nomi presenti, anche se non ti piacciono

Function idate(rng As Range) As Boolean
 idate = isdate(rng)
 End Function

. chiudi la finestra "VB" anche senza salvare, verrà salvato tutto quando salverai il file

ora puoi usare la nuova funzione "idate" nei tuoi fogli

[marino51]

idate_5.PNG
formattazione condizionale su una cella,
crea la formula adeguata, meglio se il risultato è VERO/FALSO, provandola su una cella del foglio stesso e poi la inserisci nello spazio della formattazione
idate_3.PNG
idate_4.PNG

[avalu81]

Grazie mille marino, domattina provo a capire cosa mi hai scritto ...hihihi....purtroppo pensavo di aver già migliorato notevolmente la mia conoscenza di Excel e invece.....
ho provato anche in un altro modo, con la funzione "convalida dati", indicando che nelle celle che interessano a me si possono inserire solo numeri decimali. se uno sbaglia e scrive un testo, viene fuori un messaggio d'errore quindi non te lo fa scrivere. e questo va bene.
ma se uno sbaglia e scrive un numero con un separatore decimale ".", ancora una volta, il numero viene trasformato in data e ciò nonostante, pur non trattandosi di un decimale, viene accettato come inserimento ma non ai fini della somma.....

ti viene in mente qualcosa per bypassare questo problema?

altrimenti potrei fare un mix delle due cose:
- convalida dati per il testo;
- formattazione condizionale per la data (se riuscirò a capire cosa mi hai insegnato....)....

per ora ti ringrazio per la disponibilità

[avalu81]

Marino,
ho provato ad utilizzare la tua formula. Sono riuscito a crearla e nel foglio Excel me la trova come funzione, ma non ho capito come applicarla.
Cioè, io seleziono la casella in cui dovrebbe essere inserito il valore decimale, gli applico la formula IDATE...e poi cosa dovrei fare?
Ho visto che tu indichi IDATE indirizzandolo sulla cella A1, ma non ho capito cosa voglia dire. scusa ma sono ignorante.

In ogni caso, vorrei chiederti un altra cosa:
quello che ti dicevo sulla CONVALIDA DATI, mi crea i problemi di cui ti parlavo solo sul pc dell'ufficio.
Provando il file a casa, su un altro pc, con un'altra versione di Excel, i numeri scritti col punto rimangono tali (non vengono trasformati in data) quindi avendogli impostato su tutte le celle una convalida dati solo per numeri decimali, ogni qualvolta si scriva qualcosa di diverso, mi viene generato un messaggio di errore. Questo è quello che cerco, ma a questo punto mi chiedo se c'è qualche variabile di sistema che è impostata sul pc dell'ufficio che genera il problema relativo alla trasformazione di un numero in data.

Riesci a darmi una mano?
Grazie

[avalu81]

Marino, ho provato a fare quello che dice, ma non ci sono riuscito.
al momento ho capito solamente che se il SO ha nelle impostazioni un separatore tra ore e minuti ":" il file Excel viene compilato correttamente e in caso di errori (compilazione con separatore decimale ".") viene subito segnalato e non fa compilare la cella.
Se invece il SO è impostato con separatore ore/minuti su ".", la compilazione del file Excel potrebbe creare problemi, in quanto i numeri con il punto verrebbero comunque inseriti, ma non sarebbero calcolati nella somma complessiva.
Ora, dal momento che il file viene consegnato ai concorrenti, ed ognuno potrebbe avere il SO impostato in maniera diversa, c'è la possibilità di scrivere una macro che in automatico, se uno ci mette un "." lo converte in "," senza cambiare il valore inserito?
un qualcosa di molto semplice e che funzioni per il solo file, non come impostazione generale di Excel.

ah, per quanto riguarda la creazione di una macro, mi mancano proprio le basi, quindi se fosse possibile un discorso terra terra....
grazie ugualmente

[marino51]

idate_6.PNG
ho scritto la tabellina che vedi, e vorrei che facessi dei test per capire se la formula è sufficiente
nella colonna
A ci sono i casi da verificare
B la formula =VAL.NUMERO(A2) ovviamente cambia il numero di riga
C la formula =idate(A2)
D la formula =SE(NUM(A2)<1;FALSO;VERO)
E la formula "somma" delle precedenti =SE(VAL.NUMERO(A2)=VERO;SE(idate(A2)=VERO;FALSO;SE (NUM(A2)<1;FALSO;VERO));FALSO)
se è sufficiente vediamo come applicarla

ps la formula dovrebbe andare bene con qualunque impostazione del SO