Populate Formula Using User-filled Cell References

Jul 9, 2012

So I have a table where column B contains a text entry, and column C returns a certain other text entry based on if certain keywords appear in the B entry. Say, if B8 contains "Fresh Apple Pie", column C8 could return "Tastes Delicious" by searching for "Apple" in B8. I've got it work just fine using a ridiculously long If(Isnumber(Search())) function, but the problem with this is that if I want to adjust the output, or add a new input rule I have to go into the formula and that just gets messy. Instead, I'd like to start an extra sheet, or a table to the side, where the user can write in column D what he's looking for, and then in E what the formula should return. Ie., if I wanted to change "Tastes Delicious" to "Getting Sick of It", I'd just have to change the E8 entry to that text, but leave D8 as "Apple". I've been playing with array formula's, but I can't get the formula to search past the first row of criteria.

Basically, I want something like this, where columns D and E are open ended for any user to add new search criteria or change the existing (for example, I could put "Gummy" in C4 and "Candy" in D4, and B3 would then return "Candy"):

Item
Output

Search For
Classify as

Candy Apple
Fruit

[Code] .....

I've been using this formula:

{=IF(ISNUMBER(SEARCH(C3:C4,B12)),D3:D4,"")}

(the cell references are different, but you can see the formula)

And it half works. If C3 is found in B12, it'll return D3, but if C3 isn't found I need it to look for C4, which it doesn't do. Naturally, the end formula will be a much larger range than two cells though.

View 6 Replies


ADVERTISEMENT

VBA Code To Populate A Cell Automatically Whenever Another Cell Is Filled In Manually?

Mar 29, 2014

I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.

When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.

I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.

View 8 Replies View Related

Populate Combobox In VBA With Only Filled Cells From A Range

Nov 2, 2013

I have a combobox on a spreadsheet it is populated on a sheet called teams :

The Format control has an input range of: Teams!$B:$B

Now this has a few thousand empty cells in the range but I would need it to only populate with the cells that are not empty.

View 3 Replies View Related

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula

Dec 22, 2009

Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

View 9 Replies View Related

Replace Character Used In Formula Where Formula References Cell

Feb 23, 2008

I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire

View 9 Replies View Related

Get Cell References From Formula

Aug 8, 2007

I am trying to code the following in VBA. Excel Cells have formulae like: '=+BZ165-BZ163-BZ162-BZ160-BZ159-BZ157-BZ153'. Now I want to write code in VBA which will be able to give me the individual cells referenced in this formula: BZ165, BZ163 etc. These names can be stored in individual variables or arrays.
I will then use these for further processing. Note in cases where formula refers to data in another sheet like: '=Projects!P49' or data in another excel like '='C:Documents and SettingshoskopDesktop[Annual.xls]Quarterly'!BA$502': I would like to store the path , file name and sheetname in variables/arrays as well.
Any pointers as to how I should approach this problem?

View 7 Replies View Related

A Way To Get The Cell References In A Formula Change

Sep 6, 2009

Is there a way of getting the cell references in a formula change following a change in the content of a cell in the worksheet?

View 9 Replies View Related

Variable Cell References Within Formula

May 18, 2009

I have a number of Cell names LR0Cost, EX0Cost, IM0cost that hold %

formula might be

View 4 Replies View Related

Multiple IF Formula With Cell References?

Mar 12, 2012

can Excel do a double if formula by looking at cells and not fixed type info.

EG: I have a % achieved and $ bonus to pay: however the % achieved & the $ bonus to pay information in the cell will change each month

So... I want the formula to say
=IF (look for cell A1 (which has %achieved) and if found use A2 ($bonus) to give me an answer... otherwise false

The second dillema I have is that A1 has 3 ranges from 100-150, 150-200 & 200-300 to calculate with a set % in A2

View 8 Replies View Related

Identifying Cell References In Formula ADV

Mar 26, 2013

I am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?

View 1 Replies View Related

Formula That References A Cell To Deterimine A Tab Name

Jul 2, 2008

I have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference.

For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6"

Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun).

I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.

View 9 Replies View Related

IF Formula That References A Cell And Returns A Different Result Dependant On The Number In The Cell Being Referenced

Oct 2, 2009

I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.

For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.

What I wrote as my formula is as follows-

=IF(A1=3,"TEST")+IF(A1=4,"RESULT")

It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.

View 2 Replies View Related

Multiple Text Box References From A User Form

Jun 12, 2008

I recently created a userform. I want the data to be placed into the leftheader, centerheader, and rightheader of the document after the user fills in the required fields. I have made the userform, and I can make the code for it to place one item on my userform in the left, center, or right headers. However, when I try to place more than one item in any of the headers excel deletes the first item and overwrites it with the item that is listed last in the VBA code for that respective header. I am new to VBA (this is actually my first program) so I do not know how to make the first value stay there and simply have the next value placed on the line below it.

View 13 Replies View Related

View Formula Values Instead Of Cell References?

Apr 27, 2014

Is it possible when copying and pasting a formula, to see the values from each cell rather than the cell reference?

View 12 Replies View Related

How To Copy A Formula Without Changing Cell References

Feb 18, 2014

I want to copy =d8*k10 into several cells, but the references keep changing. I've tried several things that I've found on the internet, but nothing seems to work and the I can't seem to copy to a columnof cells.

View 3 Replies View Related

Putting Formula In A Cell Which References Other Sheets

Mar 7, 2009

I am writing a VBA macro in excel. I have several sheets in this one workbook........one sheet for each day in a given month. Sheets for each day are labled as 3_1, 3_2 for march 1st and march 2nd respectively. I also have 4 sheets for the 4 weeks in a given month. The weekly sheets are labeled week1, week2 etc....

What i am trying to do is this:

In the week1 sheet i am trying to "put" a formula in say cell 9,12 which sums up the same cell in the first 7 days of the month. But i am having issues. Does anyone have a good way to do this via VBA?

David

View 9 Replies View Related

Displaying Sheet Name Before All Cell References In Formula

Oct 11, 2013

Any way to always include the sheet name before the cell address in a formula? For example, in my "Summary" sheet, I have a formula:

Code:
=A2 + B4/B5

However, without typing anything else, I'd like the formula to populate the sheet name since I will be extracting the formula as text and need the full reference:

Code:
=Summary!A2 + Summary!B4/Summary!B5

A way to get around this is to enter the equal sign, then temporarily select another sheet, then go back to the "Summary" sheet where all cell references will include the sheet name, but if there is a quicker way to do it using some kind of property or event in VBA, that would be ideal.

View 1 Replies View Related

Formula That References A Cell Where Workbook Is A Variable

May 24, 2007

Say cell C5 contains the name of a project (workbook). eg

C5 = "[Project1]"

In my current workbook, I want to find the value in cell B8 of Sheet1 of the project shown in C5. If I wrote this directly, it would be "='[Project]Sheet1'!B8"
But the name of the project is a variable shown in cell C5 (as explained above). So I need a formula that will find the value in cell B8 of sheet1 of the project shown in C5.

View 7 Replies View Related

Color Code Cell Formula References

Dec 3, 2007

If I have a cell containing a formula say c1= sum(a1..a10) and the output appears in a50. how do I colour code it so I know the formula in c1 is linked to the output in a50?

View 5 Replies View Related

Structured References And With Cell References I Get A Column Of Zeros

Nov 25, 2007

It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.

I have tried to do it with structured references and with cell references I get a column of zeros!

View 9 Replies View Related

Mixing Range And Cell References When Coding FORMULA

May 20, 2013

This works

ws2.Range("C5").Formula = "=sum(D5:ZZ5)"

guide me on the syntax when the final column reference is a variable

ws2.Range("C5").Formula = "=sum(D5:" & Cells(5, l_LastCol) & ")"

doesn't work

View 3 Replies View Related

Copy And Paste Formula Without Changing Cell References

Mar 5, 2014

I have one sheet with all the formulas for the entire workbook and would like to copy and paste the formulas from Sheet 1 to Sheet 2...Sheet 1 to Sheet 3 etc. without changing the cell referening in the original formula. I am not too sure how .formula works.

Sub CopyAndPaste()
' To copy formulas from Summary sheet to their respective sheets

With Worksheets("Summary")
.Range("R3").Copy Worksheets("2").Range("X3").Formula = Worksheets("Summary").Range("R3")

End With
End Sub

View 7 Replies View Related

A Formula That References A Table Based On Contents Of Another Cell

May 20, 2008

Here is what I am trying to achieve:

I have a workbook with two tabs, on the first one there is one column and the top of the column(A1) is a dropdown containing the options 'Boy' or 'Girl'.
One the second worksheet I have two named tables (Boy and Girl) - these tables each contain a single column array with 5 names in each (so we have a table of 5 boys names and a table of 5 girls names)

What I want to happen is when a user selects an option from the dropdown in A1 the cell below(A2) is populated randomly with a name from the corresponding list.

I have achieved this initially using the following formula:
=IF(A1="Boy", INDEX(Boy,RandInt(1,10)), INDEX(Girl,RandInt(1,10)))

This works fine, but I will need to expand this for several dropdown options, so my new workbook will have a dropdown of car makes, and each make will have a list of the models associatied with the car make (e.g. Ford: Fiesta, Escort, Mondeo...)
But I do not want to have a ridiculously long nested IF statement for every car Make, so what I want to do is something along the lines of the following:

=INDEX([CONTENTS OF A1],RandInt(1,10))

So I use the text selected from the dropdown in A1 as the TableName in my formula - however, I cannot retrieve that to use in the formula without it coming back as a text with the "" included and my formula errors!

View 9 Replies View Related

Copy Non Absolute Cell References In Conditional Formatting Formula?

Dec 3, 2012

I have this fairly simple formula which decides whether to shade a cell or not

=AND($X$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.

View 12 Replies View Related

Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

View 2 Replies View Related

Macro To Copy Formula While Keeping Original Cell References

Feb 17, 2009

I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.

E.g.

If the formula on Sheet1 is:

= sum(A1:B6)

then the copied formula on Sheet2 would read

=sum(Sheet1!A1:Sheet1!B6)

You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.

I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.

View 9 Replies View Related

Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells

Jul 18, 2006

Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View 5 Replies View Related

User Form-Easy Selection Of Data To Be Filled In The Form

Jun 3, 2006

find the attached workbook

I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:

When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.

Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.

View 7 Replies View Related

Dynamic Named Range :: Does Not Copy The Formula That References A Cell From Another Line

May 29, 2007

I am having a few problems with dynamic named range in excel 2000.

When adding new data to the range, excel extends the range correctly, but only copies some of the formula correctly. It does not copy the formula that references a cell from another line.

I am trying to create a excel spreadsheet and have a formula =e10-e9, which does not copy down.

View 10 Replies View Related

Populate User Form

Jan 16, 2007

I have created a user form for which I would like to do the following:

1)Select Last name using a combo box and with this all other fields should be automaticallu populated

2) If I make a change on the user form, it should update the relevent cell as well

All data is in the sheet 'Staff'

View 7 Replies View Related







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