Using Value In Single Cell To Determine Multiple Data Validations From Separate Work

Apr 10, 2014

I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.

Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer).
I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.

I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.

E.g.
IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc
IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...

I have managed to do this with the following formula, but only if the data is stored in the same sheet.
Cell B1
=IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1))
Cell B2
=IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))

[NOTE]
=meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored.
=magicArmour2 represents the range of cells where the Magic Body Armour is stored.

The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)

View 5 Replies


ADVERTISEMENT

Multiple Data Validations With List And Cell Entry

Jul 2, 2013

I have a scenario where a cell has to be restricted based on the user input of another cell, also it should allow only Yes or No values.

ex:column A
column B

pending response
submitted

User should be allowed to enter a value in column B as "yes" or "No" only when column A has a value "Submitted"

I referred the thread to restrict entering the data [URL]...

However not able to add list as well.

View 2 Replies View Related

VLookup Work With Multiple Numbers Within A Single Cell

Aug 15, 2009

I built an Excel Macro to pull information from my schedule and compile it within another Worksheet. The Macro below searches for an instance of "Unavailable" and then populates my Data Worksheet with the WeekDays (ie: M-W-F or ALL Week), the Details (ie: Vacation or Training) and the Name of the employee. I struggled with a way to populate my employee name as "unavailable" isn't always offset by the same amount of characters to employee name (as it is with WeekDays and Details). Instead, I collected the row in which "Unavailable" was found.

I used the Vlookup function to populate the number of Absences and it worked swell.

=VLOOKUP(C2,O:P,2,FALSE)

C2 is populated with Weekdays (ie: ALL week or M-W-F)
O contains a list of possible day combinations
P contains the number of days

I hoped to use a similar function to populate my employee names. This didn't work well a 'tall.

=VLOOKUP(A2,L:M,2,FALSE)....................

View 9 Replies View Related

For A Column Of Cells Separate Text In Single Cell Across Multiple Columns

Nov 29, 2013

New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:

(NOTE: does not contain actual names or info) Book1.xlsx

This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?

While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.

View 6 Replies View Related

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

Sep 23, 2013

Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.

View 5 Replies View Related

Using Formula To Separate Data In Single Cell?

Oct 15, 2012

formula to separate information in a single cell into multiple columns. I have hundreds of rows of data, so a formula to quickly separate the information into individual columns would be good. Below are a few lines of data.

ALMADEN STATION* 900 227 168 182 176
OAKRIDGE STATION* 900 276 332 362 349
OHLONE‐CHYNOWETH STATION* 900 469 480 522 504
ALUM ROCK STATION 901 947 925 1007 972
BAYPOINTE STATION 901 181 196 213 205
BONAVENTURA STATION 901 137 140 152 147
CAPITOL STATION 901 650 680 741 715
CONVENTION CENTER STATION 901 788 814 886 856
CURTNER STATION 901 569 571 621 600
ORCHARD STATION 901 87 94 91

View 6 Replies View Related

Multiple Validations To A Cell

Mar 6, 2007

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a date and Col T is a text(with list validation) I have validation that if if T = "As is" and P <> I or K (which ever one has a value) then Error. Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as is"",(IF(P2=(IF(K2<>"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But they could put any value in that field. I Need to validate that P is a DATE also
I was trying to do a validation Greater than > 01/01/2007 but I can not do multiple validations on the same cell. How can I validate P is a date also

View 3 Replies View Related

Multiple Data Validations (List And Preventing Duplicate Selection)

Jun 30, 2014

I have created a Data Validation list in one of my spreadsheets which works fine. The list itself has no duplicates which is fine but when I select my entries in the other spreadsheet, I want to make sure they cannot select the same value twice.

I tried selecting "List" in data validation and using an AND with COUNTIF but it doesn't work.

=AND(Employees, countif($A$1:$A$20,A1)=1)

View 2 Replies View Related

Excel 2003 :: Two Date Data Validations For One Cell?

Jun 8, 2013

I'm using Microsoft Office 365 Home Premium. Although this file is saved with Excel 97-2003 - because the computers at work only use 2003.

OK - Here is my dilemma. I've been creating a spread(work)sheet for work. One of the fields that needs to be entered by the manager doing the papwerwork is 'Date'. I currently have the Data-Validation set as follows because the paperwork being done is for the yesterday:

Code:
=TODAY()-1
I have the Error Alert set to Warning prompting the manager to ensure the date is correct (sometimes a mid-shift audit is being done, so the actual current date has to be used). Now, what I am wanting to do is to also set it that if the manager set's the date for a future date, I would like Excel to also either do a Warning or a Critical displaying a message that future dates cannot be used.

I know this may sound trivial, but with this paperwork, dates are extremely critical and vital in the event of an investigation for cash shortages etc.

I have uploaded the excel sheet to my domain, which I can provide a link to - either here or in a private message - but I do not want to do so unless I know that it's ok to do. If so, I will reply with a link to the location of the Excel file.

View 9 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:

TABLE 1:

Project
Benefit Type
Delivered or Enabled
Benefit

PJ1
Financial
Delivered
Saving of $4M over 24 months.

[code]....

I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.

TABLE 2:

Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled

[code]....

So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

Separate Data From One Cell To Multiple Cells On Different Sheet

Sep 10, 2012

I have a sheet labled Parsed and a sheet labled Prices. The Data on the Parsed sheet is in W2:W1000. the data on the Parsed sheet, can have anywhere from one value to up to ten values in it. (again all sparated with a space.)
the data is separated by a space between each number value. I am trying to find a way to take the values from Parsed!W6 for example and separate the data into the prices sheet in to different columns. Some cells may be blank.

Example:

PARSED SHEET.

W
X

1
Equipment

[Code] .......

View 2 Replies View Related

How To Have Single Userform Put Data Into Two Separate Sheets Within Same Workbook

Feb 25, 2014

Is it possible to have a single userform put data into two separate sheets within the same workbook.

I have a userform set up for entering client data into a quote register,with no problems ( sheet 1)

A1, A2, A3, A4, A5, A6, A7, A8, A9

What I would like is for the data to also go to sheet 2, where I have a form set up to print off for our technicians to take on site.

The problem will be that I would like the data A1, A2, A3, A4, A5, A6, A7, A8, A9 not to store on this sheet after it is printed, so the next set on entries , can be printed etc.etc

View 2 Replies View Related

Formula To Work Where It Will Search A Single Cell

Feb 27, 2009

I am trying to get a formula to work where it will search a single cell (on a sheet called "Calls") to see if a particular code is in there, if so return a value using HLOOKUP from a table on a sheet called "Values". The problem I am having is how to use the wild card etc because everything I have tried returns an error...

The code I am trying (Without wild cards, because they all failed) is...

Code: ....

View 10 Replies View Related

Look Up Data From Multiple Columns And Insert In One Cell Using Comma To Separate?

Jan 31, 2014

I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:

The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.

A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3

November is in A3

If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2

View 14 Replies View Related

Separate A Lot Of Vertical Text From Single Cell?

Jan 29, 2013

I have been sent an export from Outlook in a .csv file. The Body of the Email is all contained in one cell for each row (each email detail) and is in the following vertical format within the cell:

Column Header
blank row
Data
blank row
Column Header
blank row
Data
blank row

....and so on. I've tried to do something using Transpose and Text to Columns, but I've not been able to successfully seperate out the data into a useful format in Excel. The data are responses to a website and contain Account/Contact information which I hope to be able to import into a CRM system as new Leads. way of extracting this information, so that I can get each row into a format that gives me the column header with the related data below, as you'd normally expect to see in Excel?

View 5 Replies View Related

Validations Based On Previous Validations

Nov 12, 2009

On a worksheet I want 4 cells to have validation, when a selection in the first cell is made this limits the choice in the second, when the second is selected then the third is limited by 1 and 2 and the forth will be limited by 1,2, and 3.

In the attached example data , if I choose a Region, I have a choice of Countries. When I choose a Country I am limited to valid Cities and when I have chosen a City then a list of applicable Agents will be available. I have used NamedRanges for a two dimensional lookup but for four and my list will be far bigger than the sample, I do not fancy maintaining numerous NamedRanges.

View 5 Replies View Related

Multiple Data In Single Cell Lookup In Another One Data

Nov 9, 2013

see sample file, i need a formula to do like B column,

I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..

View 7 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Edit Multiple Validations

Apr 24, 2007

I need to edit multiple validations in a worksheet. There are over 990 validation for each month. I want to change the word in the sentence. Example: April to May. Right now I have to edit each one seperatly. I have tried a macro but it changes to whole sentence. Find and replace do not allow editing in a validation.

View 9 Replies View Related

Move Data From Multiple Worksheets To Single Sheet Without Manually Entering Each Cell Id

Feb 5, 2014

I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.

Example =Jan!M6, =Jan!N7, =Jan!O9 etc.

I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.

View 5 Replies View Related

Copy Individual Data Values In One Column To Single Cell Location On Multiple Sheets?

Jan 24, 2014

I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?

In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.

View 14 Replies View Related

Determine Work Days Between 2 Dates

Mar 13, 2008

My workday function doesnt seem to be working correctly, it is including weekends.

My Spreadsheet is like this
A1= 01/01/2008 ( Date)
A2= 30
A3= =WORKDAY(A1+A2,0)

I am expecting the result to be 11/02/2008, but i am getting 31/01/2008.

View 3 Replies View Related

Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format

Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies View Related

Erasing Data Validations In Worksheet

Jan 31, 2012

I've created a worksheet with a number of dropdowns / data validations. Now the thing with these validations is the source range apparently has to be in the same sheet (well it can be another one but much simpler if in the same). I've just realized I created them in the wrong place, so need to erase them. Pressing the delete key isnt working though, they're still there. I could right-click delete but that then requires one to move cells up/right etc and that could mess with my formulas.

View 7 Replies View Related

Data Validations And Filtercopy Macro

Jan 28, 2007

I am using a modified version of Debra Dalgleish's filtercopy macro in which 3 data validations are being used (she used 1 in her downloadable example ProductList. zip) and it works just fine when the validations are used in order as they should be. However, in looking over the data I am trying to present in my report I think it would be very valuable to use the first and third validations at times (skipping the 2nd) to discern information in that way. Long story short, I can't seem to get this to work and don't know if it's even possible because I'm not a VBA expert.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 3 And Target.Column = 4 Then
Worksheets("StoreData").Range("O2").Calculate
Worksheets("StoreData").Range("Database") _
. AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("StoreData").Range("O1:O2"), _
CopyToRange:=Range("A6:K6"), Unique:=False
End If
If Target.Row = 3 And Target.Column = 6 Then...............................

View 4 Replies View Related

VBA To Do 2 Validations On A Cell

Apr 13, 2009

On a tab, i have one column where information is entered. what should be entered is based on a dropdown list (which i have setup on a validation). the other thing that should be allowed to be entered is a an 8 digit number. when something else is entered, i need to have a popup message appear to tell the user to fix the error.

I have no idea how to effectively enter in the restriction in VBA for this.

Below is the VBA i have started:

1. The problem is that if one of the drop-downs is chosen, then i get this error message (because the dropdown list has text that is more than 8 characters in length).

2. The one other problem is that if the cell is left blank, i still receive the popup message. how do i avoid this from showing up.

3. I am also looking to do something like this for a range of cells (from F5:F100).

Private Sub Worksheet_Change()

Dim stue As String
stue = "f5"

Application.EnableEvents = True
If Len(Range("f5")) 8 Then
MsgBox "there is an error in cell " & stue & " "
End If
End Sub

View 9 Replies View Related

Loosing Data Validations When Reopening Spreadsheet

Jan 29, 2013

I'm loosing the validations that I set up such as having a dropdown list and so on when I save and reopen the file. First I thought I didn't save it, but notice it happening consistently.

View 3 Replies View Related

Data Validations - Multipale Lists Depending On Each Other

Jan 6, 2009

I have search the site for a possible solution for my problem. And that is: how to create multiple lists using validation – depending on each other.

The scenario is. I have a data sheet that contains a list of alien plant species. The species have different methods of treatments (some do have the same). Each treatment might have one or more herbicide products that can be used and with each herbicide a certain application mixture.
I have used validation to create the pick list of the plants, the next pick list should be the treatment for the selected plant species and the 3rd list the product and then last list the mixture.
All of these lists should only list the information that is relevant to the selected plant specie, in the first list.

View 9 Replies View Related

Separate Multiple Lines In Cell To Multiple Rows?

Jul 10, 2014

I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).

So my data is as follows - (I have attached teh sample file)

"Pari bom - GP and CFO 02547895452 pari@gmail.com

papa bom - GP and CFO 23365789541 papa@gmail.com

mama bom - CFO 2587412589 pari@gmail.com

nana bom - CFO 2587412589 nana@gmail.com

masi bom - CFO 2587412589 masi@gmail.com

"

I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.

View 4 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved