Graph To Automatically Change Format Depending Upon Lookup Information

Mar 9, 2012

I have a graph which displays information from a vlookup.. The vlookup can bring either numbers which are in percentages format or ordinary number format.

The question I have is is it possible for the graph to automatically change format depending upon the information who is looked up..

If so how can this be done..

View 1 Replies


ADVERTISEMENT

LOOKUP:: Pull Information From Elsewhere In The Sheet Automatically

Apr 18, 2008

I have created a spread sheet that uses the LOOKUP function to pull information from elsewhere in the sheet and automatically fill in a cell so that I can save time.

The spreadsheet has several columns with a single word or number but the column used for the lookup contains 2 words, 1 number and a “#” sign. I have used the “CONCATENATE” function to create a column that has all the words, the number and symbol and then my formula says to LOOKUP the value in the created column and return the number associated with that LOOKUP value. The problem is it will not give me the correct value. All cells with the formula return a value from the same row in the LOOKUP table no matter what the LOOKUP value..

I have used this exact same formula (copied and pasted it) in another place in the table with the LOOKUP information added by hand, not generated with the “CONCATENATE” function, and it works fine.

View 9 Replies View Related

Change Format Depending On Another Cell

Feb 18, 2010

I want to be able to do the following: I have data in column a and I want the text to change to strikethrough in, for instance, cell a1 when data is entered in b1.

View 9 Replies View Related

Change Cell Format Depending On Number

Apr 16, 2014

I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?

View 1 Replies View Related

Format Certain Cells To Change Colors Depending On Date

Feb 27, 2013

I am trying to format certain cells to change colors depending on the date and the content of the next field to show due dates.

cell label
due date
date completed

c5
au5
be5

c6
au6
be6

c7
au7
be7

c8
au8
be8

What I need is for the contents of au5 to be green up until 10 days before the due date, yellow up until 5 days, and red with less than 5 days until the due date. If the date completed is entered into be5, then the contents of au5 should be black.

Is this possible, and if it is, how do I set this up? I tried to use

=$AU5-TODAY()>=10

And choose green formatting (something I found on yahoo answers), but when I enter the date the text is still the original black font.

View 5 Replies View Related

Texts Automatically Change The Currency Depending On The Choice Made In The Previous Userform

Jul 3, 2008

I have options buttons in a userform, first is "$" the other is "€"

When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.

The formula below was is example

=""&Data!$A$2&" / m³"

=""&Data!$A$2&" / kWh"

View 9 Replies View Related

Change Color Of Cells Depending On Data (format) Present In It?

Jan 11, 2013

I need change the color of the cell depending on the following conditions. (By the way the column will have names list)

1. single punctuations are allowed anywhere in name apart from first position.-->color should not change
2. If any name got consecutive punctuations --> color should change to RED.
3. Any punctuations apart from Dot(.), hyphen(-), apostrophe('), Space( ) are not allowed-->color should change to RED.

Example-
ShivakumaraNo Change
Shiva kumaraNo Change
Shiva'kumaraNo Change
shiva.kumaraNo Change
shiva kumarared
shiva''kumarared
shiva' kumarared
shiva. Kumarared
shiva.'kumarared
shiva:kumarared

View 4 Replies View Related

Automatically Change Currency Format

Jul 13, 2006

AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".

USD 100
EURO 100

Is it possible to automatically make it:


USD $100
EURO €100

View 9 Replies View Related

Calculating Dates Depending On Information

May 18, 2006

I would like to calculate the date but depends on how the information is scattered. In the first coloumn I have a set of dates, in the second coloumn I have a list of letters. I would like a macro that reads the the letters, and as they disappear calculate the time interval. For instance,

01/01/2002 B,C
02/01/2002 A,B,C
03/01/2002 C

So it firsts starts off and sees that the letters are B and C are there to begin with, then it moves on to the second row and then sees that the letter A has been added. Then to the third row and sees that there is only C so A lasted for 1 day (was there on 02/01/2002 and disappeared on 03/02/2002) and B lasted for 2 days (was there on 01/01/2002 and disappeared on 03/01/2002).

View 6 Replies View Related

Extract Data Depending On Cell Information

Sep 11, 2006

i have 20 different materials which have codes assigned to them

wh = white
ivory = ivory
db = duck egg blue

etc etc

when i create an order some of the codes will be called up
i want to be able to put them codes plus the description into the worksheet Ideally into the header. if not call a1

View 9 Replies View Related

Information Dissapears From Graph When Section Of Other Sheet Is In 'group'

Sep 29, 2009

i dont know if there is a way around this, i have a sheet, it has one large sheet with hundreds of rows under 3 headings, and i have a group on each of these so people can go in and expand their section and add details, i have a second sheet that contains some graphs simplifying the data collected in the 3 sections of the other sheet..

but i have just noticed when a section of the sheet is grouped its details are removed from the graph.

is there any way around this? i need the groups on as the sheet is so big and people do not want to be scrolling through other area's data when they can just expand their own.

View 4 Replies View Related

Vlookup Function: Re-format The Information Into A Horizontal Format By Week

Feb 3, 2007

I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.

View 2 Replies View Related

Add Row If Value Value In Column Change And Paste Specific Value Depending On The Change

Mar 5, 2010

I have used the below code to insert a new row when the value in coulmn A change. I now need to evolve it so that the new row will contain a specific value depending on the changing value:

Before:
Column A Column B
one test
one test
two test
two test
three test
three test

After:

Column A Column B
one test
one test
Coz two............................

View 9 Replies View Related

Automatically Update Graph/Chart

Mar 17, 2008

I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that? The code for extracting from the workbook is the following:

Sub Extract()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
ActiveWorkbook.Sheets("CURRENCIES").Select
ActiveWindow.SelectedSheets.Delete
myFileName = Application. GetOpenFilename("Excel files,*.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If................

View 4 Replies View Related

Graph Automatically Keeping Most Recent 18 Months

Jun 30, 2009

I have a word document that has all kinds of graphs embedded in it. When I double click the graphs, an excel spreadsheet comes up. The spreadsheet contains quite a few years worth of information. I, however, need it to automatically refresh to show only the most recent 18 months of data on the graph. The documents are in MSOffice 2007.

View 9 Replies View Related

Automatically Check A Checkbox Depending On Source

May 18, 2009

It might be very simple, but all I need is if I have data on A1, and i have 3 check boxes labeled: NEW, EXISTING, and ARCHIVED. I want it to automatically check the corresponding boxes. So if "NEW" is typed under A1, it'll check "NEW" box. "EXISTING" will check "EXISTING" check box and so on.

View 4 Replies View Related

Dropdown List-lookup-graph Query

Dec 8, 2009

Here's the skinny: From the front page you select a 'heading' from drop down list

example of data:

Heading 1
data a
data b
data c

Heading 2
data a2
data b2
data c2

The problem i'm having is that the resulting graph only plots the bottom line of data (i.e. 'data c') instead of all 3. Ani ideas why/work arounds?

View 9 Replies View Related

Lookup Date In Range Depending On A Number

May 5, 2009

I have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!

View 2 Replies View Related

Lookup Results Depending Upon Cell Values

Aug 25, 2008

Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.

View 3 Replies View Related

Automatically Pull Value From Assigned Cell Into Text Box In Graph

Jul 23, 2014

I am creating a series of charts that all include a text box containing "(N=[value])", with different values in each instance, but with each N value listed as a total frequency in each table. I am creating hundreds of these, so if there is a way to automatically pull the correct N value from a cell into the text box, it would make this faster. I have written simple functions in excel tables but have no experience with VBA/macros.

View 5 Replies View Related

Insert Row Into Column Of Data And Automatically Update Graph

Oct 16, 2011

A colleague recorded this macro to insert a row into a column of data which a graph is plotted from to automatically update the graph each time more data is added. I can't figure out how to simplify the VBA and stop it adding a row on row 57 rather than the bottom of the graph.

Code:
Sub Button1_Click()
'
' Button1_Click Macro
'
'
Rows("56:56").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B57:F57").Select

[Code] ........

View 3 Replies View Related

Automatically Size Textbox Vertically Depending On Content

Sep 13, 2005

Is there a way to control the vertical size of a textbox, so that we could type in a List of Instructions to our operator, and the textbox would resize depending on the numbers of instructions in the box. Also the items in the cells beneath the textbox would need to move down, to allow for the resized textbox.

View 3 Replies View Related

Lookup Manufacturing Details Depending On Product Type

Dec 15, 2009

I have created a drop down list of products that we manufacture on a tab in excel. I want to use this list so we can create production/manufacturing paperwork that can be used in our factory.

On other tabs I have created the manufacturing specifications which are a list of specifications and cutting sizes that vary depending on the 1st column /size selected. The rest of the data for follows across.

However for the three products the manufacturing specifications are different so what I want to know is can I create a formula that says if I select product A then look at product A's specification tab, if I select product B select product B's specification tab or if I choose product C then look at product C's specification tab?

I can create a vlookup formula that works for one product and drops the necessary information into a sheet I have designed but I don't know to get it to distinguish the correct cutting info depending on the product type selected.

View 13 Replies View Related

Cell Lookup - Calculation Depending On Criteria Column?

Jun 29, 2013

Criteria
Column B

Constant
Variable0
Variable1
Variable2
Variable3

2
[Variable2/Constant]

5
123
43
45
76

[Code] ..

This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?

View 9 Replies View Related

Creating Button Out Of Text (student Name) That Will Automatically Insert Graph

Jan 24, 2014

I am trying to do the following

Unit Percentage Score Unit 1 Percent ScoreJohny SmithBetty Donovan
Unit 1 Operations & Algebraic Thinking2100.00%50.00%
Unit 1 Measurement & Data3100.00%66.67%
Unit 1 Geometry0#DIV/0!#DIV/0!
Unit 1 Number & Operations Base Ten16100.00%93.75%

1-I would like to create a type of link or button that would exist in the cell "Johny Smith" so that when a teacher clicks on that name excel would generate a graph or pie chart for the specific data associated with Johny and when a teacher clicks on Betty a different graph would pop up. I know teachers can just highlight the data and insert charts, but many of them don't know how to do that. I have been asked to make the process a one click function.

View 1 Replies View Related

Userform To Automatically Create A Graph From The Data That Is Shown In The Worksheet

Jul 4, 2006

I want to do is use a userform to automatically create a graph from the data that is shown in the worksheet. The data in the work sheet will grow and shrink all the time. Am i right in thinking I am meant to create a dynamic range? I know how to do it but am not too sure what I am meant to be naming. And secondly I am not too sure how I set up the button to produce the graph.

View 4 Replies View Related

Evaluate The Information And Lookup

Jul 9, 2008

Im looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website"

I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom.

I have attached a test file which should clear things up

View 9 Replies View Related

Cell Format Changes Depending On Formula

Jul 18, 2013

My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.

I want the answer displayed as 2.43 of 3. Here is the formula.

=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.

The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.

If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.

I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.

View 10 Replies View Related

Automatically Copying Rows From One Sheet To Another Depending On A Cells Input

Jun 2, 2009

i have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...

View 9 Replies View Related

Automatically Fill Data Depending On Choice From Drop-down Menu

Dec 28, 2007

I have three lists in the first sheet 'Food', 'Quantity', 'Points Value'
Each food (i.e. tuna, pasta, anchovies) has a quantity (i.e. 40g) and a points value (i.e. '50'). The first list ('Food') has been copied as a drop down list in the second sheet. When selecting a food, I would like the cells 'quantity' and 'points value' to pre-fill - taking the data from the first sheet.

i.e. if say Anchovies has the quantity value '30g' and Points Value '2' on the first sheet, then in the second sheet if anchovies was selected from the drop-down menu, these values would add themselves. how to do this? I have attached a sample file shwoing what I want to do.

View 2 Replies View Related







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