# Take Part Information From Cell To Create Formula?

Jul 2, 2014I need to pull information from a cell that is full of text and numbers and get it to creat a formular.

More information in the spreadsheet example

I need to pull information from a cell that is full of text and numbers and get it to creat a formular.

More information in the spreadsheet example

ADVERTISEMENT

Can I use part of the data in a column of cells to sum data in a column.

E.g. I want to sum valves in Column V that match the Data in Column T by using the following cells CB 1A1x, CB 1A2x, CB 1a3x CB 1A5, etc etc CB 1B3x...... to obtain the totals

For CB 1A1, CB 1A2, CB 1A3 etc etc.

Capture.JPG

I am trying to take information from one sheet(1) and place part of it on another sheet(2). I have a list of data ID numbers that are in a list of 7 and I want to pull the first in the list to place on sheet 2 and create a formula that will pick up every 7th ID number (or name) and place it on my sheet 2.

10221451022145102214510221451022145102214510221451100798110079811007981100798110079811007981100798

This is what I am working with, and just want the first number of the new sequence to show up on sheet two.

I have the following formula in my workbook;

=SUMPRODUCT((A2:A8702"")/COUNTIF(A2:A8702,A2:A8702&""))

The data in the workbook is imported automatically and the range 'A8702' is different every time the data is imported, although is always in column A.

Can I use the value of a cell to determine the range?

So I have used this to get the ast used row number;

=MAX(ROW(A:A)*(A:A""))

I need a cell formula (gets put in by macro) that returns part of a workbook name.

The worksheet name will always be different, but will contain either

SD1, SD4, BDO, BD (not neccessarily in Caps)

and it is this key code that i want returned in the cell (G2).

so if the worksheet name is

NW SD1 bobs your uncle. when macro runs G2 returns SD1

or

BDO xyz. when macro runs G2 returns BDO

I have 2 sheets, Sheet1 and Sheet2. I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change. So if i = 5, I'd like the cell to be populated with the formula =Sheet1!B5

View 4 Replies View RelatedI have a formula that combines the the text of various cells.

Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.

See formula below:

I have a formula in cell J2:

='H:Folder[CI10001G.xlsm]Sheet'!$C$5

I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:

='H:Folder[CI10004D.xlsm]Sheet'!$C$5

And then this formula will then successfully look in the newly directly file path.

I was given this Formula to use in a cell to calculate the total hours from 2 different cells, from inputting date and time from and to dates..as an example from 4/1/14 0:00 thru 4/1/14 12:00.....The problem I am encountering is that when the spreadsheet is 1st opened "+1" is visible in the cell where this formula is. I am asking how the formula can be altered to have the "+1" not visible in the cell before any date and time is entered.

= INT(F13-D13)*24+(((F13-D13)-INT(F13-D13))/0.04166666)+1

Here is the code I'm working on:

I want this code to replace the value of s with the value of a cell.

sub Macro2()

Dim s As String

s= value of (cell F2)

Range("I8").Select

ActiveCell.FormulaR1C1 = "='[(s).xlsm]Payroll Computation '!R8C11"

End Sub

i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

what i am trying to do is concatenate part of a file name and a cell value which contans a date

=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)

I need to copy information in one cell to only part of another cell. For example:

C1:Office Name N1:Path

C2:Houston Office N2:OU=Houston Office,DC=contoso,DC=com

Trying to copy from anything in cell C2 to after the = sign before the comma in cell N2.

I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.

PART

QTY

CONSOLIDATED PARTS

CONSOLIDATED

QUANTITIES

Currently where I work we print out registers to keep track of student attendances. This is done by merging an excel document that contains all the details of the names, days, dates , times, student groups and rooms of each lesson (each row contains the details for one lesson) with a word document containing the appropriate list of students in each specific group along with blank mail merge fields that relate to the excel document.

There are often 100 lessons a week meaning it takes an age to merge all the documents to create the registers.

Instead of this method I'd like the tutor to print off their own register by opening the excel document and clicking a button at the end of the appropriate row that contains the details for the lesson they are teaching. This would then take the details from that row and generate the appropriate register on another worksheet. Importantly it would have to list the correct group of students for each lesson - the names of these groups would be listed on other sheets.

Im guessing macros would be the right way to do this, but I have no real knowledge of them so alternatives would be helpful. Can anyone point me in the right direction? I've included a sample of how it would look with just one lesson in the excel doc.

I'm trying to create an autoupdating price list using a part number and a website. I've tried Excel's data import wizard.

Website: WebFLIS - Public Search

Sample data Category

Chaplain Kits

Item

Chaplain's Kit, Consumable

NIIN

9925-01-326-2855

Price

$276.94

Description

Also called the ReSuppply Kit

VBA Code that allows me to automatically open the page.

Dim IE As Object

Sub submitFeedback3()

Application.ScreenUpdating = False

Set IE = CreateObject("InternetExplorer.Application")

[Code] ......

Ideally, I'd like to click a button that says "Update Prices" and it will search WebFlis for the NIIN listed and update the price. I have 717 items on my list so updating would be by click only (I think I can write that portion).

If that is not an option, I'd like to be able to click on each item row (think hyperlink) and be able to see the results for that individual item.

I want to create a user defined function to search a column of data for a part number.

If it exists I want to have a the UDf returna "fail" otherwise "pass"

Here is the code I was trying to use

Function firstpass(SN As String) As String

ws = Worksheets("Defects")

c = ""

With ws.Range("a1:a9999")

Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)

End With

If Not c Is Nothing Then

firstpass = "Pass"

Else

firstpass = "Fail"

End If

End Function

This function only returns a "#value" and I don't quite know how to troubleshoot it.

I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.

I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.

I'm hoping the example will make this clearer.

On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.

To make this more complex, I can't use vba on this one.

the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.

something like this.

(edit changing 1:1 to $1:$1)

=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))

It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!

I have a main spreadsheet that I am consistenty adding information to. The columns are : Company name, Contact, Territory, and Status. The main spreadsheet is titled "Main". I want 5 additional spreadsheets in the same workbook that are automatically pulling information from the "Main" spreadsheet, and populating the appropriate spreadsheet . For example...I have 500 entries of different companies in "Main". All of these companies are either categorized as "North, South, East, West, Offshore" in the Territory column. So, I would like my workbook to have 6 tabs...one "Main, North, South, East, West, Offshore." As of right now, I am Sorting the column, then copy and pasting into correct spreadsheet manually.

View 14 Replies View RelatedI have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen

I want to be able to enter variable search functions as follows:

Employee Number; shows all information on employee

Division: shows all employees in division (possible from a list of all divisions)

Appraisal Eligibility : Applicable shows all the applicable employees

Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)

I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.

How to create an area in excel where by if a customer enters an account number all of their account details would be automatically entered into the address fields?

Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.

I tried this but it's not working:

=IF(C3="0","",C3-C2)

I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.

Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items

How can i use the text in those cells to put in a formula to call that as a range?

If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell

I am trying to create an "if,then" formula that shows:

if cell E8 is greater than 171, then i'd like cell E9 to show the value of cell in E8.

I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.

Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.

I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.

I'm creating a basic form or templateusing Excel 2010 where the user will fill out information (See attached screenshot) I'd like to incorporate two a check boxes, that when ticked, change the display of the second table, either adding, removing or splitting of of the current columns in to two?

Is this, or any variation of, possible using Excel 2010.

I want to find the last used cell within a column. Then I want to write a formula to find the Min from the cells above (Row 1 is a Header, so I do not want to include it).

Like this:

Column A has data in an undetermined amount of rows below.

A1 is a header and I do not wish to include it in the formula.

A2:A15 has numerical data.

I want A16 to have the formula =MIN(A2:A15)

Next time there may only be data in A2:A10 or maybe A2:25...

I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.

Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)

What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6

When I enter data in the cells A1 and B1, C1 needs to calculate the result. (not copy and paste)

For examle: I have formula C1=(A1+B1)/2

C column for formula, but shows result only when there is a data in A and B

I'm using the VBA code below for a piece of code.

[Code] .....

However, I want to use this same function in another place, without the limit set on the cells that will be cleared.

What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.

Something like:

[Code] .....

I am trying to create an active cell formula based of variables from given cells.

With ActiveCell.FormulaR1C1 = "=CONCATENATE(""RU"",RC[-12],RC[-11],RC[-10],RC[-9])-CONCATENATE(""MU"",RC[-12],RC[-11],RC[-10],RC[-9])"

ActiveCell.Formula = ActiveCell.Value

But This does nothing.

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