Writing Formula In Dynamic Number Of Cells?

Feb 13, 2014

I'm trying to create a "master file" that can be used later (a tool other uses can pull up) on with datasets of varying size. I'd like to "data dump" a table of individuals with subsequent columns of various characteristics, then automatically compute various things. So then a user can dump their data into my file and "get the answer" immediately. I've seen some stuff on running one calculation on a dynamic number of cells, but let me illustrate why this is different. I want to perform a dynamic number of calculations, see below:

Each row (record) is a person, with some characteristics (numerical). Ultimately I have a few columns on the end with some computations (Excel formulas) I'd like to do. I'd like to do this for each person (that is, each row) in that data that I dump in. But the thing is, ahead of time we don't know how many people will be dumped in, and that changes (because I'd like this to be a tool...). So how do I tell Excel to compute things if there is a person in that row, but do nothing and leave cells blank if there isn't a person? Ideally I don't have millions of ifs testing for blank cells either. I'd like to do this with Excel functions only and not VBA, if at all possible.

View 1 Replies


ADVERTISEMENT

Writing A Number After The Formula For A Cell

Aug 17, 2007

I have a cell in which I use the following formula:

View 13 Replies View Related

Slow Writing To Large Number Of Cells

Mar 1, 2007

I heard there is an efficient way of writing large amounts of data at once to a worksheet. My program is writing alot of data (e.g. 600+ items in each row) and takes over an hour to run because it writes to each cell one-at-a-time. I seached this forum and couldn't find the answer. Here is an excerpt from an old 1998 article by Dermot Balson and William Mercer that used code to write to cells in large chunks but it doesn't work for me - I get a method failed message.

'to read in
Dim A As Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)

'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..

Here is the link to the article: http://www.avdf.com/apr98/art_ot003.html. A more detailed explanation how this trick works

View 4 Replies View Related

Formula To A Dynamic Number Of Rows

Nov 11, 2008

In a macro, I need to apply a formula to a dynamic number of rows (the lenght of the file will change evryday).

My problem is that when I have recorded the macro, the code tells to apply the formula from row 1 to 3 as today there are 3 rows in my file but tomorrow i might have 10 or 1000.

What code should I use to apply the formula to as many rows as my spreadhsheet contains?

View 9 Replies View Related

Count Number Of Cells In Dynamic Range In VBA?

Jul 23, 2013

I have a function which copy pastes data into a sheet based on a filter criteria. It is also pasted in a specific layout(shown below) starting from Cell A1 in the top left corner. Therefore the destination sheet could have a different number of rows with values each time.

What I would like to do is count the sum of 'Values' in the destination sheet, and add a total below it.

For example, the [=TOTAL] cell is where I want the total to appear. Below the answer would be 26. But say if Peter wasnt in the record, the formula should still work in identifying the Total as 20.

Title:
Sheridan
Owner
Petrov G

[Code]....

Is there a way I can achieve the desired result? I figured I had to somehow count from the first record by Peter, to the last one, whichever that may be.

View 2 Replies View Related

Applying Formula To Dynamic Number Of Rows For Different Files?

Feb 20, 2014

I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.

I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.

For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.

View 8 Replies View Related

Writing Number As Text To A Cell

Jun 16, 2013

Process is that I read in lines from a text file and then based on a filter I then write some those values to a sheet. One of the values I transfer is always 5 characters with a trailing space. The first four may be all numbers (e.g. "0013 ") or a mix (e.g. "013G "). When I write these to the spreadsheet the ones without letters always end up as a number (e.g. "0013 " become 13). How do I force the cell to show all values as text in the cell just like I read it in. In fact, I'd prefer stripping out the trailing space but it's not critical. That I can do (TRIM).

View 4 Replies View Related

Dynamic Formula Based On Cells Value

Jan 26, 2012

I'm trying to get a =GETPIVOTDATA formula to update daily based upon todays date but am struggling.

The data feeds from a SQL DataMart and the formula is:

Code:
=GETPIVOTDATA("[Measures].[Case Completed -Invoiced - Count]",SBVSDATA!$A$6,"[Time].[Day]","[Time].[Day].&[25]","[Lender].[MI Grouping]","[Lender].[MI Grouping].&[Cambridge]")

This is the specific part of it i've been trying to play with, replace the [25] with a cell reference A1 for arguments sake that contains TODAY()-1

Code:
="[Time].[Day]","[Time].[Day].&[25]"]")

View 7 Replies View Related

Writing Formula In Cell Using Vba?

Apr 8, 2014

I have a reference written as a string in cell "AS4", the string is results!A4

I want to write that same reference in cell "as5". I have written the following

[Code] .....

However, cell "as5" returns the string as =results!'A4', with single quotes around A4

Therefore the reference isn't working.

View 2 Replies View Related

Writing A Formula To Cell

Jul 20, 2006

Until now, I have been using the terrifically inefficient way of writing in a formula in a cell and copy pasting the value in my VBA

Easy example (concatenating):

Sheets(Sheet2).Select
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=""Data collected on ""&TEXT(NOW(),""dd/mm/yyyy"")&"" at ""&TEXT(NOW(),""hh:mm"")"
Range("D1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

More complex example (with IF statement):

Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-3],13)=""No list found"",""Data requested for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& "" out of range"",""Data downloaded for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& """")"Range("D2").Select...............

View 4 Replies View Related

VBA Dynamic Range Till First Blank But Cells Contain Formula?

Jul 22, 2014

I've normally just dynamically selected a range using the xldown feature but because this row contains formula it goes always down to the bottom of the formula instead of the last cell which isn't blank.

How would i go about generating this dynamic range that stops at the first cell that contains no value (but has a formula)? Perhaps a do while loop which looped down until it hit the first blank and then assigned the cells it had looped through as the range?

View 3 Replies View Related

Writing In Two Cells One Word?

Apr 22, 2013

I'm trying to write a word or more but i want it to be in different cell.

Like: this sentence "Excel Program" write it in cell a1 but it slip to the cell a2. (so in cell a1 adn a2 this sentence).

View 4 Replies View Related

Slow Writing To Cells

Jul 6, 2007

A macro in workbook 1 creates a report by reading & manipulating data from two workbooks(WB 1 and WB 2).

A new blank report sheet is copied from WB1 to WB2. The data (some text, some numeric) is collected in a 20 element, single dimension array & written to the new sheet in 20 columns using a for/next loop. This is repeated for many rows(can be more than 1000).

The array is "erased" before each row. Screenupdating is turned off, calculation is turned off. The report takes longer & longer each time it is run - Why? With the one-and-only "Write" line "remmed out" the report takes just 2 secs however many times it is re-run - including the copying of the blank master report page. If the first report takes, say, 1 min the second time takes 2 mins & the 3rd - 3 mins etc. Reset is only achieved by exiting excel. The code in question is:- For col = 1 to 20 : cells(ro,col) = d(col) : Next col. (ro = the current row number which is incremented for each pass, d() is the data array)

View 9 Replies View Related

Writing Formula Which Refers To Different Pages In Workbook?

Dec 29, 2013

I'm working with a multi-page workbook I use for work scheduling purposes.

This is how Sheet1 is set-up:

A
B
1
Name

[Code]....

In the cells in Column D I have used the following formula =IF('Sheet1'!B2="Yes","Holiday",""). What I have realized is that this formula is quite limited. For example not all 12 colleagues work everyday and they work different shifts on different days- each day's data is sorted so that the colleagues are organized primarily by Shift start time and then alphabetically by name- consequently if I used this formula across the entire workbook the Sheet1B2 data may not be consistently referencing the same colleague.

I was wondering if there was a way to amend the formula so that if a colleague is marked as 'Yes' in Sheet1, 'Holiday' could be inserted in Column D in the rows corresponding to where the colleague's name appears?

View 3 Replies View Related

#NAME- Error Results When Writing Formula To Spreadsheet From VBA

Apr 25, 2006

When I am writing a formula that includes a lookup, it returns the # NAME? error in the spreadsheet. If I delete the trailing ), retype it, and hit enter then the formula returns the correct value. How do I get the formula to work correctly as it is written to the sheet from VBA!

Here's the line of code that writes the formula to the worksheet:

TBox.Range("A3").Offset(i, 3).FormulaR1C1 = "= ROUNDUP((8-(VLOOKUP(RC[-3],K:L,2,FALSE)/1.2))/RC[-1]*1.2,0)"

View 4 Replies View Related

Writing Sub That Reads All Cells In Matrix?

Apr 11, 2013

How to write a sub that read all cells in the matrix ( a block of cells let's say: A5:F15).in two dimensionals. This is what I've done so far, but i got nothing:

Sub myarray()
Dim myarray() As Variant
myarray = Range("A5:f15").Value
End Sub

View 2 Replies View Related

Use More Than 1 Range Of Cells When Writing A VBA Macro

Nov 18, 2008

Just wanted to know if I could have a range or ranges which are split. The current range i have is N5:AV6. I need to miss out every 6th cell so am looking to do a number of ranges like this - N5:R6, T5:X6, Z5:AD6 etc etc. If this can be done could you show me how I would need to lay it out?

View 2 Replies View Related

Writing The Contents Of Cells Into A File

Apr 12, 2009

I have a bunch of values in column A, and I need to write (export) the entire column into a file called "ColumnA.dat".

create an Excel Macro Script that would accomplish that?

View 9 Replies View Related

Question With Writing A Formula That Can Pick Out Words With 3 Letters

Jan 8, 2009

I have a question with picking out certain word in the column. I am trying to write a formula that would allow me to pick out names that has first 3 alphabetical letters from the columns. The column has a lot of different names, but the one that I would want my automation to pick out is similar to this, C, AR, AA-103, BG-2056, HJE-1100, etc. However, in the same column, I have other words like elevtar, hsvte, lvnrm, etc. that I do not want my formula to pick out.

View 9 Replies View Related

Subscript Out Of Range Writing To Cells In Loop

Oct 4, 2006

i'm trying to get my code to work. Basically my code opens up workbook and extract the relavant data into the main workbook. Previously Its just a single For loop and it works perfectly after changing to double for loop its stuck.

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim rCell As Range
Dim lCount2 As Long
Worksheets("Data").Select
Range("a2:ac65536").Clear
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook..................

View 6 Replies View Related

Writing Formula To Calculate Monthly Returns Based On Annual Growth Rate

Jul 10, 2014

I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.

writing a formula to evaluate over 360 periods.

View 5 Replies View Related

When Dragging Formula Down The Formula Skips X Number Of Cells

Jul 22, 2014

Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:

A1= "1"
A2= "2"
A3= "3"

Then I type in the simple formula in the cell
B1= "=A1"

and then drag down but it skips 3 cells so then

B5= "=A2"

B9= "=A3"

If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"

this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.

Something similar to this B1 "=A1(skip3cells)"

View 4 Replies View Related

Changing Formula In A Number Of Cells

Mar 10, 2013

I've created a user form to enter some values in a few adjacent cells. When the user clicks on a button, I want several other cells to be loaded with special formulas that refer to the new values. This happens on a row by row basis. First, a working formula in that cell which is AB5 is:

Code:
=IFERROR((IF(AK5

View 1 Replies View Related

Formula - Number In One Cell And None In The Other 4 Cells

Nov 4, 2008

I have 5 columns...I would like a formula to say if number in one cell and none in the other 4 cells to say "yes" otherwise say "no"

View 9 Replies View Related

Formula Based Upon Number Of Empty Cells?

Jul 28, 2014

I am creating a fantasy football draft board using excel. To keep it short and sweet, each individual team (one per column) has a $200 budget to draft players. Each team must fill 14 roster positions (one per row), but here's the catch; you must spend at least $1 on all of the 14 players on your roster. So you have a maximum bid amount which is your remaining budget subtracted by $1 for every remaining spot you have left to fill.

I have already created a "remaining budget" field which subtracts each drafted player's inserted dollar amounts from the $200... easy. Beneath this, I'd like to create another cell per team that tells you your "maximum bid," which is essentially 200, -1 for every cell that remains blank. I have played around with the conditional IF formulas but cannot seem to have it account for multiple cells.

As a workaround, I have made columns hidden beneath the board which contain the conditional IF, ISBLANK fields which are -1 if true and 0 if false. Then creating the "remaining budget" field that subtracts these values from 200. This works, but it's not perfect, as when you have multiple players you still need to draft and you've exhausted your budget only saving $1 for each, it overstates your "maximum bid" by $1, or when you have $X left and you need to only fill one more spot, it ill tell you your "maximum bid" is $X-1.

View 5 Replies View Related

COUNTIF Formula: Total Of The Number Of Cells?

May 24, 2006

I have cells C22:C27 with a number that could change each week. How can I get a total of the number of cells that = 0,1,3,4,5,6,7,8,9,10,11,12?

View 3 Replies View Related

Formula To Count Number Of Cells That Meet Criteria?

Dec 18, 2011

I need to formula to count the number of cells that meet the criteria below,

Find the letters 'AT' in some part of the cell and a blank cell next to it...

Doing a different formula for each, doesn't bring out the correct figure.

View 3 Replies View Related

Formula To Pull Lowest Number In Cells I2:L2 And Must Be 0 Or Greater?

Sep 12, 2013

Formula to pull the lowest number that falls in Cells I2:L2 and the number must be 0 or Greater. Some of the numbers might be negatives and I am trying not to pull those numbers.

View 3 Replies View Related

Formula To Calculate Percentage Based On Number And Text In Cells

May 14, 2014

I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.

Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.

So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.

View 5 Replies View Related

Formula For Counting Number Of Occurrences Within Range Of Cells AND Strings

Jan 14, 2014

I'm looking for the easiest way to count the number of occurrences within a cell range.

The formula that I'm currently using is:

=COUNTIF(D$5:D$8,"a*")

This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;

When I want to find multiple values in the range and count them all, I use this formula:

=COUNTIF(D$5:D$8,OR("a*","b*","c*"))

View 9 Replies View Related







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