Would like cell data to be more editable and work with formula

The Microsoft Excel spreadsheet that I am using has three fields of
data in each cell in column b.
A sample cell looks like this:

Year: 2008

Once you click it, you see more information:
Year: 2008
Model: 1234
Quantity: 5

Each cell in cell B mentioned above, has to match up with a code in
column a.
For example, the cell above would match this value in column a:
basic

The way they have each cell in column B, you have to click on the cell
to get all the data. It
is difficult  to get to the "quantity" amount (you can't use the arrow
keys).  Maybe the cell has been merged
to get all the information in.

Another group types in their quantity available in column J.

1) I would like to compare the quantity requested in column B to match
up with the quantity available in column J.  Something like "if
quantityb2=j2. . ." in column K
2) I would like to make the value in column B more user-friendly, so I
don't have to click on cell B2 to see all of the values, especially
the "quantity".
3) Not every cell in the B column has the "year, model, quantity"
format.  For example, some need to be listed as "out of production",
so I can't have all the cells formatted this way.

Please advise.





0
4/16/2011 1:14:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
496 Views

Similar Articles

[PageSpeed] 10

My advice.................... before you go any further.........break up the
single cells' data into individual cells.

Much easier to work with when comparing data like quantities.


Gord Dibben     MS Excel MVP

On Sat, 16 Apr 2011 06:14:02 -0700 (PDT), Tony O <tonyrulesyall@yahoo.com>
wrote:

>The Microsoft Excel spreadsheet that I am using has three fields of
>data in each cell in column b.
>A sample cell looks like this:
>
>Year: 2008
>
>Once you click it, you see more information:
>Year: 2008
>Model: 1234
>Quantity: 5
>
>Each cell in cell B mentioned above, has to match up with a code in
>column a.
>For example, the cell above would match this value in column a:
>basic
>
>The way they have each cell in column B, you have to click on the cell
>to get all the data. It
>is difficult  to get to the "quantity" amount (you can't use the arrow
>keys).  Maybe the cell has been merged
>to get all the information in.
>
>Another group types in their quantity available in column J.
>
>1) I would like to compare the quantity requested in column B to match
>up with the quantity available in column J.  Something like "if
>quantityb2=j2. . ." in column K
>2) I would like to make the value in column B more user-friendly, so I
>don't have to click on cell B2 to see all of the values, especially
>the "quantity".
>3) Not every cell in the B column has the "year, model, quantity"
>format.  For example, some need to be listed as "out of production",
>so I can't have all the cells formatted this way.
>
>Please advise.
>
>
>
>
0
phnorton (279)
4/16/2011 3:04:00 PM
Yowzers.  I'm guessing either merged cells, a multi-line cell with a 
fixed row height (and word wrap off) or a long value in a cell with a 
fixed row height (and word wrap on). In any case, the setup seems to be 
quite deliberate, as in my (limited) experience Excel would not default 
to such a format.

Is this a workbook created by someone else that you must work with? Is 
it possible, as Gord suggested, to change the structure of the 
worksheet?

Hmm.  I just attempted to re-create the behavior you describe (xl2010) 
and I'm not getting there. Is there macro code running behind this 
worksheet?

I think we need more information to be able to provide useful advice.

Clif

"Tony O" <tonyrulesyall@yahoo.com> wrote in message 
news:4ed3dd5c-08fc-48f2-970d-78b377be27ef@l6g2000vbn.googlegroups.com...
> The Microsoft Excel spreadsheet that I am using has three fields of
> data in each cell in column b.
> A sample cell looks like this:
>
> Year: 2008
>
> Once you click it, you see more information:
> Year: 2008
> Model: 1234
> Quantity: 5
>
> Each cell in cell B mentioned above, has to match up with a code in
> column a.
> For example, the cell above would match this value in column a:
> basic
>
> The way they have each cell in column B, you have to click on the cell
> to get all the data. It
> is difficult  to get to the "quantity" amount (you can't use the arrow
> keys).  Maybe the cell has been merged
> to get all the information in.
>
> Another group types in their quantity available in column J.
>
> 1) I would like to compare the quantity requested in column B to match
> up with the quantity available in column J.  Something like "if
> quantityb2=j2. . ." in column K


> 2) I would like to make the value in column B more user-friendly, so I
> don't have to click on cell B2 to see all of the values, especially
> the "quantity".
> 3) Not every cell in the B column has the "year, model, quantity"
> format.  For example, some need to be listed as "out of production",
> so I can't have all the cells formatted this way.
>
> Please advise.
>
>
>
>
>



-- 
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


0
4/16/2011 3:53:58 PM
Reply:

Similar Artilces:

Data table with inputs from a differente worksheet
How can i create a data table in excel 2007, with inputs, formulas and calculation from a diferent worksheet? If the cell A1 of Sheet2 has the value 10, then the formula =Sheet2!A1 will return the value 10 Let's say I want to make the a formula on Sheet1 to sum a range D1:D5 on Sheet2: Type =SUM(; open Sheet2, and select the range D1:D5, type ); click the green check mark in Formula Bar or press Enter key to commit formula Not that if the sheet's name has spaces in it I will get a formulas like =SUM('Jan Sales'!D1:D5 - observe the single quotes Does this answer...

working with dates #2
HI I have a list o people and there dates of birth, I have added a formulae to add up their ages however I now want to simply analyse the dates as in how many 16 - 19 year olds 20 - 35 year olds etc etc Can anyone sugest a simple way to acomplish this ?? Thanks for help in advance Thanks i will give it a try philkelly67 "Phil kelly" <philkelly67@hotmail.com> wrote in message news:OZnc95kdFHA.2960@TK2MSFTNGP10.phx.gbl... > HI I have a list o people and there dates of birth, I have added a > formulae to add up their ages however I now want to simply analyse th...

protecting formulas
I am trying to find a way to protect formulas on a spreadsheet whil still allowing users to put in new data. Is there a way to do this? am using excel 200 -- Message posted from http://www.ExcelForum.com Cherilyn unlock the cells where you want input and then protect the sheet with a password. Format | Cells... | Protection tab | Locked = false (not ticked) Regards Trevor "Cherilyn >" <<Cherilyn.16krpe@excelforum-nospam.com> wrote in message news:Cherilyn.16krpe@excelforum-nospam.com... > I am trying to find a way to protect formulas on a spreadsheet while ...

formula for updating 1 spreadhseet from another
I have two spreadsheets one for "current accounts" and one for "past due accounts". The "current" spreadsheet list all our receivables from this year even if they are still outstanding, the "past due" spreadsheet list only those that are 30 days or more past due. What I currently have to do is if a vendor pays an invoice update the "current" spreadsheet and then if its a past due go an update the "past due spreadhseet". My question: Is there away to automaticly have the "past due" spreadhseet update itself if I enter a payme...

Too many different cell formats
Recently, while working with a large Excel 2000 workbook, I encountered an error. The error read "Too many different cell formats." After clicking "OK" I saved and closed the workbook. Unfortunately, now, my workbook will not open!!! Microsoft's fix posted on their website is to reduce the file formats in the workbook. However, once you've closed the workbook, it is impossible to open it again. Does anyone know of a fix for this problem. My last backup file is over two weeks old!!! Thanks, Mike It may be possible to deal with this but I would nee...

Copy data from closed workbooks
I am trying to copy a lot of data from a closed Excel file to current Excel file using a piece of VBA code. I tried "copy and paste",got the following code from Macro recording: Sub Dataacquire() Dim Row As Integer Workbooks.Open Filename:="C:\temp\test.xls" Row = Application.Count("A:A") 'find the total rows of non-empty data Range("A1:A1000").Select 'I want to apply the "Row" value here but don't know how Selection.Copy Workbooks("test.xls").Close Windows("Book2.xls").Acti...

mail merge multiple data sources
I am trying to do a mail merge on a 4 x 6 postcard 2 up in Publisher. Since there are 2 on a page, I am trying to use two different data sources for the merge but I can't figure out how to do it. Any ideas? Eric wrote: > I am trying to do a mail merge on a 4 x 6 postcard 2 up in Publisher. Since > there are 2 on a page, I am trying to use two different data sources for the > merge but I can't figure out how to do it. Any ideas? That's not how it's done (unless I'm misinterpreting). Design with one card on the screen, and set the page options to print 2...

moving cell contents (automatically)
I have a spread sheet that contains one column of information. That column contains client addresses. Each line of the address is in a seperate cell (Name in A1, Street in A2, City,State,Zip in A3). This pattern continues through about 1000 contacts. I need to move the contents out from a single colum to three colums where column A would be Name, B would be Street, and C would be City,State,Zip. Is there a way to have excel do this automatically. Say move every 2nd cell to colum B and every 3rd cell to column C. By the way, I am using Office XP, but have 2003 at home if needed. ...

Taborder with unlocked cells in protected worksheets
Excel 2003, sp2 Hi, When you lock cells in a worksheet you can jump between the unlocked cells using the TAB key. Is there any way to influence the tab order (without having to use a lot of code)? Regards Pete When using the unlocked cells method your order can only be left to right and top to bottom. There are other methods...........one of which is using a named range with no protection. See Bob Phillips' site for this method. http://www.xldynamic.com/source/xld.xlFAQ0008.html Other methods would use VBA code. Gord Dibben MS Excel MVP On Wed, 23 Jan 2008 16:46:34 +0100, ...

Cell Date / Currency Convert
Hello All, I have a worksheet that in one cell I want either a price or date shown - at present I have a formula that looks within a spreadsheet 2 from either 2 columns - one column is a date formatted and the other column is price formatted - is there a way to have a formula that will change its view automatically to either price or date depending upon which column it looks at. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16787 View this thread: http://www.excelforum.co...

Macro to automatically save document with cell reference as filena
I have tried to create a workbook that automatically grabs the filename and saves the document with some problems Both methods appear to work, but Excel always crashes. There should be a way to accomplish this without a crash. Method 1: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value &...

DIV/0 ERROR
Hi, Normally, I use the formula (EXAMPLE): =IF(ISERROR(G12/G11*1000),"",G12/G11*1000) In this way I prevent the error to appear. The problem is that somebody sent me a very big Excel file with plenty of DIV Errors, therefore I am asking how I can apply my formula to all of them in one go. Certainly, I cannot do this cell by cell and I am sure there is a "batch" process I can use. Thanks for your help Alex See the answer posted to your question in the programming newsgroup. Doria/Warris wrote: > Hi, > Normally, I use the formula (EXAMPLE): > =IF(ISERROR(G12/G...

Printing
We are still using Dynamics 6.0. All of a sudden when we print our checks there are a couple of fields that are not showing on the stub and another appears out of order. What is strange is when I choose to print to the screen first the check looks fine. First we thought it might be the printer or the printer driver but we used a newer driver first, then just switch printers entirely and the checks were still messed up the same way. I went into the report customization screen to check the "Check with Stub on Top and Bottom - Graphical" report and it is in the modified column...

import data for user define entity
hi guys How i can import data for user define entity? Regards Hi, The Data Migration Framework only allows to import to the system defined entities. You could either create your own import application based on the SDK (which is fairly simple if you have programming skills), or you could use a third party like Scribe Software. Kind regards, -- Ronald Lemmen - MSCRM MVP Avanade Netherlands http://ronaldlemmen.blogspot.com/ "abboushi" wrote: > hi guys > How i can import data for user define entity? > Regards > ...

using excel to analyze time series data
I have two columns of data - column A has dates in chronological order and column B has corresponding closing prices. I'd like to programmically (or with a formula), find the first date of each month and add the corresponding closing prices and then divide by the number of closing prices that meet the criteria. Is this something that I can do in excel? Should I learn VBA? Or should I look elsewhere? Just want to also note that by the first date of each month, I meant the earliest date of each month, which in my data isn't always the 1st. On Jun 9, 9:32=A0am, eggman2001 <sod...@gm...

Displaying worksheet name in cell
Is there any way to display a worksheet name in a cell (without using macros). I have tried =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) but if I use this on multiple sheets in the same workbook, they all end up displaying the same name! Include a reference to each sheet: =MID(CELL("filename",a1),FIND("]",CELL("filename",a1))+1,255) Adrian wrote: > > Is there any way to display a worksheet name in a cell (without using macros). > > I have tried =MID(CELL("filename"),FIND("]",CELL("...

VBA Formula Problem
I am using the code below and get an error with the formula statement. I need help. TIA Sub Salary() Range("Salary").ClearContents Selection.QueryTable.Refresh BackgroundQuery:=False For Each c In Range("LName") c.Formula = Upper(Left("D2", Find(",", "D2", 1) - 1)) Next End Sub Greg the formula needs to be a string. D2 should not be in quotes either: c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))" Note that this will set the entire range to read from D2. If you want it instead to read from column...

Sheet names used in formulas
I have a question about using sheet names in a formula. I would like the sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! Hi! Here's one way: =INDIRECT("S"&ROW(A40)&"!$W$5") Which evaluates to: =S40!$W$5 As you drag copy down: =S40!...

Linking data from cells into word doc in 2003
When I open a word document, that has links to the excel app I am opening it from, it prompts me with a question if I wish to update links. I have the option already check marked in Word so that it will update links automatically upon opening, and I did the same thing in excel, but it still prompts for an answer. Has anyone else had this problem and if so, what do you do to fix it. This is destroying our automation at work where the hyperlink is triggered to open the word doc, the word doc updates, prints, saves and closes before going on to the next hyperlink. ...

Importing data without losing calulations or formate
Sorry if reposted, didn't seem to go the first time. Hi, I am wonder if it is possible to have a worksheet setup so that an calculations and formatting are maintained if new or additional data i imported. IE Name Cost x 1 y 2 TOTAL 3 (Sum formula) But if added another row the sum updates. Name Cost x 1 y 2 z 1 TOTAL 4 (Sum formula) I would be cool with doing it via VBA if neccessary. TIA -- Message posted from http://www.ExcelForum.com Hi, in nearly all cases you won't need VBA. For your example...

search form not to load all data up to start with
I have a form based on a query that i use to search for product in my database, on the form i have text box to type in, then a button to requery the query has the following Like "*" & [forms]![frmProductList]! [txtProductSearch] & "*" This query works fine but when i load up the form is shows all the products we stock which is a lot and seems to take a lot longer to load now, Is there a way for the form not to load up any data until o press the search button Try putting a default in [forms]![frmProductList]![txtProductSearch] that would not pro...

copies of an Access database does not include latest data
Hi, I have had an Access 2003 application for years that travels between different people's PCs during the course of a year, with those persons having responsibility for updating the database. No problems transferring between PCs until this past week, when the time came to transfer the database from a person running Vista rather than XP. At the end of the day, the database in its original location has all the current data, BUT ... when I copy that database anywhere, it brings up older data that subsequently has been changed. I moved to "share" the folder und...

Match like cells and return the latest date
FIRST scenario: I have three columns of data. Column A and B are similar with two exceptions (i)column A is preceeded by two more identifying characters than in column B (ii) Column B is a partial listing of column A. Column C has a complete listing of Full names that needs to be matched with the partial list in column B. Example: Column A Column B Column C CLMoton Moton Cleveland objective is to match Moton to Cleveland based on column A. My thought was to search by a "LIKE" function or macro. is there such a thing? ...

Changing just one cell each time I print
I am using Excel 2000. I need to print one sheet multiple times and change one cell on that sheet each time it prints. Example: I need to print my sheet 50 times, cells J1.J50 contain a list of numbers 001,002,003,123,456 etc. (This column does not print) I need to copy cell J1 into cell A1 - then print my worksheet, Then I need to copy cell J2 into cell A1 and print the worksheet, Then I need to copy cell J3 into cell A1 and print the worksheet You get the idea. Each time I print the worksheet I need a different number in cell A1 because I am using VLOOKUP to fill in cells in the workshe...

[help please!] Extract selected data from html files using keywords?
Hi, I would like to keep some selected data in an Excel cell that is available between two keywords in an internet html page (to be downloaded by excel). For instance : Inside the html file, there is the following text : --- Number : 1258622 Sector : electric Net : 8.2 --- So I would like Excel to search between the keyword "Number" and "Sector" and keep the data "1258622" in a cell. Many thanks in advance for any help, ;-) Tid These lines are all in one cell? If yes, then this worked ok for me (assumes A1 contains the text): =--REPLACE(REPLACE(A1,SEARC...