# Use The Results Of A Formula As Column/row Numbers In Another Formula

Oct 1, 2009
I have two cells. The first cell has the formula:

=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))

which results in a col and row number (such as D65).

The second cell has the following formula:

=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2)

^^

I wish to replace the 'D1" in the Match function with the results of the first cell's formula.

I assume Indirect would work, but I don't know how to code the formula to use it.

View 4 Replies
ADVERTISEMENT
Apr 24, 2014

I need to show sum and not concatenated values have a userform w 60 fields for numbers as follows:

a) UserForm1.TextBox34 to UserForm1.TextBox41: 8 fields-CAD currency

b) UserForm1.TextBox42 to UserForm1.TextBox49: 8 fields-USD currency

c) UserForm1.TextBox50 to UserForm1.TextBox57: 8 fields-exchange rate

d) UserForm1.TextBox58 to UserForm1.TextBox65: 8 fields-Total CAD currency => the formula here works across the board

UserForm1.TextBox58.value = Format(UserForm1.TextBox34.value+UserForm1.TextBox42.value*UserForm1.TextBox50.value, "###,##0.00")

e) UserForm1.TextBox66 to UserForm1.TextBox73: 8 fields-local taxes in CAD currency

f) UserForm1.TextBox74 to UserForm1.TextBox81: 8 fields-federal taxes in CAD currency

g) UserForm1.TextBox82 to UserForm1.TextBox89: 8 fields-total for first column in CAD => the formulas here works as well across the board

UserForm1.TextBox82.value = Format(UserForm1.TextBox34.value + UserForm1.TextBox42.value * UserForm1.TextBox50.value + UserForm1.TextBox66.value + UserForm1.TextBox74.value, "###,##0.00")

have a grand total formula pulling in all values, and this one works as well; so i have 4 simple addition formulas, and the local & fed tax formulas do not.

however, the formulas for the sub-total for the local taxes and the federal taxes do not work; the results show a concatenation of the 8 values rather thn the sum of the 8 values; the formula for the local tax is as follows:

UserForm1.TextBox110.Value = Format(UserForm1.TextBox66.Value + UserForm1.TextBox67.Value + UserForm1.TextBox68.Value + UserForm1.TextBox69.Value + UserForm1.TextBox70.Value + UserForm1.TextBox71.Value + UserForm1.TextBox72.Value + UserForm1.TextBox73.Value, "0.00")

I have rewritten the formulas and still these two tax formulas result in concatenated numbers rather than the sum.

I would have u/l worksheet but it's roughly 4MB now.

View 3 Replies
View Related
May 9, 2006

I have a range that has formulas that are based on other fcells outside the range.

What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.

There could be a marco to run when this process is needed.

example attached

View 9 Replies
View Related
Jun 13, 2014

I am attempting to copy the results of a formula in a column to a row. The data in the column is in a different worksheet. Although pasting the data using the transpose option in the drop down menu works, I really want to have the results of the formula returned. The reason for this is the results are variable based on the formula.

View 9 Replies
View Related
Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:

D5: Middle Name:

D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsxâ€Ž

View 3 Replies
View Related
May 29, 2009

To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?

Example of formula:

=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))

View 5 Replies
View Related
Dec 27, 2008

I would like a formula for cells B1, B2, & B3 that will give me the sum of the numbers in Column B which are next to their corresponding plan in Column A. The attachment is a sample of a sheet that will be having approx 500 rows to work with. I have tried using the "Sumif" funtion, but I can't seem to get it to work correctly.

View 4 Replies
View Related
Jul 27, 2006

I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.

View 2 Replies
View Related
Nov 3, 2008

I have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??

View 2 Replies
View Related
Feb 23, 2007

I would like to be able to search through a column of numbers (say A1=2, A2=4, A3=1, A4=7, A5=5, A6=2, A7=3) and for the formula to list all the possible combinations of cells that add up to a particular number, in this case we will say 7 (i.e. A4 … A1+A2+A3 … A2+A3+A6 … A2+A7 … etc.).

View 9 Replies
View Related
Feb 14, 2003

I have three columns of values that are in no particular order that need to be sorted in ascending order in another column.

View 9 Replies
View Related
Jan 25, 2009

Need a formula to calculate a absolute total from a column of totals.

I thought this was simple, but the formula I made didn't work.

View 3 Replies
View Related
Jun 24, 2008

I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.

View 3 Replies
View Related
Dec 19, 2005

When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.

Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the

formula doesn't work? This happens on some sheets in my workbook, but not all

sheets.

Also, another wierd thing: on some cells doing the above reults in the

formula showing as text in the cell.

View 9 Replies
View Related
Jan 28, 2009

I would like a formula that looks for Y and results Y. See two examples below.

Example

Cell B2 = Y

Cell D2 = N

Cell E2 = Y

Cell F2 = Y

Example

Cell B3 = N

Cell D3 = N

Cell E3 = N

Cell F3 = N

View 4 Replies
View Related
Jan 9, 2007

what was the anser to a simple question earlier has now got me thinking...

I have one mega formula (see below) that based on a set of criteria returns a result from a range in an accompanying workbook:

=INDEX('input plus'!D940:D979,MATCH(1,('input plus'!$DJ$940:$DJ$979>=2)*('input plus'!$DG$940:$DG$979=1),0))

(the range 940:979 is a filled with investment funds data all within a particular sector, this formula for example will return the name of the number 1 rated fund, ie "mega star cash")

However, the above assumes that the ranges specified wont change, but they do, frequently and there are over 300 of them, so I earlier got help to create the following formula which can tell me what range a certain set of data lies within:

=CONCATENATE(ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,0)+0,4,4,1),":",ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,1)+0,4,4,1))

(so I tell this formula to look at a specified sector name 'cash funds' and by tweaking the number in bold it will return the range of data that i want to feed into the first formula, ie it will return D940:D979, DJ940:DJ979 and DG940:DG979)

If the data ranges change then these cells will dynamicaly update, either the range will shrink or grow - which is perfect - what i want to know is if i can integrate this into the first formula, so if you like it becomes completely dynamic.

View 9 Replies
View Related
Dec 31, 2008

I have created a simple SUM formula to add cell values across six sheets in the same workbook. I see the result when I use the formula button but the result (number) does not appear in the cell. All I get is $ -.

View 9 Replies
View Related
Feb 21, 2014

I am using this formula. How can I get it to pull 'Sales Materials'!$F17:$F69' and display along with 'Sales Materials'!$B$17:$B$69 when my logical statement is true.

=INDEX('Sales Materials'!$B$17:$F$69,SMALL(IF('Sales Materials'!$E$17:$E$69="YES",ROW('Sales Materials'!$B$17:$B$69)-16),ROW(A1)),1)

I've tried using * but I think something is going on with "-16" which I was told to you use to account for headers.

View 2 Replies
View Related
Dec 23, 2008

I have a spreadsheet which matches Reference numbers in Column A against Column B and vice versa; and assigns unique or duplicate values. I would like to be able to count the number of duplicates and uniques giving a total of how many appear in the spreadsheet. I've attached the spreadsheet to give you an idea of what its like.

View 3 Replies
View Related
Dec 19, 2009

Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?

View 3 Replies
View Related
May 7, 2009

Is I have a VLOOKUP formula which results in a defined name- great! but when I reference that result in another formula I get a #REF error, if I type the defined name directly in to the formula (index btw) all works but I need the formula to recognise the referenced cell.

Example

=VLOOKUP(B8,Cable.Table,2) this results in a defined name of a table (D1A) in cell B30

The cell B30 is named "table". I then have another formula in B33 which is:

=index(B30,B47,C12) or =index(table,B47,C12) Which gives me the #REF error

If I input =index(D1A,B47,C12) the function works but it needs to be the initial reference from the result of the other formula and recognise that it is a defined name.

View 3 Replies
View Related
Oct 7, 2008

In A1 I have this: $9.50.

In A2 I want to display the result of multiplying A1 with 10% and subtracting the result from A1.

I've tried this in A2: =SUM(A1*10%)-SUM(A1)

But it displays a negative number: -$8.55

I need it to display: $8.55

View 3 Replies
View Related
Nov 11, 2009

The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.

******** ******************** ************************************************************************>Microsoft Excel - PLAY 4 EVE MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1F2G2F3G3F4G4F5G5F6G6=ABCDEFG111/10/0966666666Q211/09/0977667766DD311/10/0977767776T411/11/0972467246S511/12/0977667766DD611/13/0978667866DSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Oct 26, 2006

Is there any way that I can apply the roundup feature to an entire workbook without having to do each cell individually?

View 9 Replies
View Related
Sep 4, 2007

I have 4 source workbooks and a master workbook which is linked to the source workbooks, if the source workbooks have no value in specfic cells the master workbook which is linked to it shows a 0 (not a problem). Now if I want to copy only cells which have a value other than the 0 to the end of used rows on another sheet on the Master workbook using VB I run into a problem because it sees 0 as a value and copies as well.

The issue is how do I stop the 0 showing and not being seen as a value. This 0 shows regardless of format.

View 9 Replies
View Related
Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates

Column C & DNumerical data not used in any calculations

Column EThe numbers to multiply together

Cell A1Start date to be used in the PRODUCTformula

Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

View 2 Replies
View Related
Jun 16, 2014

I have a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).

Read & Sign Tracking-061614.xlsxâ€Ž

View 2 Replies
View Related
Jan 22, 2014

I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.

As a example i have attached the sheet.

View 1 Replies
View Related
Feb 25, 2014

Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).

Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates

I created the following condition for each product code under each month in SHEET1

=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")

The formula only works for two product codes (A and B), and then yields blank cells.

View 5 Replies
View Related
Jan 25, 2009

how to take the result of a formula calculated in one cell and show the results in an other cell

View 4 Replies
View Related