YTLE#98: Excel ADDRESS function

I was wondering if anybody could tell me how to do the following:

I want to replace the cell reference (i.e. the $B$5) portion of the following worksheet formula

=RIGHT('Summary_sheet'!$B$5,1)

with the following equivalent formula:

=ADDRESS(5+(ROW()-2-MOD(ROW()-2,10))/10,2)

and have it return the desired information (which would be the last letter of the target cell on the "Summary_sheet" worksheet.

I've tried various combinations of INDIRECT and concatenating the pieces as text (i.e., "=RIGHT('Summary_sheet'!" & ADDRESS(5+(ROW()-2-MOD(ROW()-2,10))/10,2) & ",1)" to no avail.

Can somebody please point me in the right direction? Thanks!

MSG

Hi there,

I'm using VBA in excel and want to do the following as I would have done in a worksheet:
I have simplified my example tremendously, but I think this is adequate.

suppose cell A1 has the number 2
suppose the cell B1 has the number 3

in cell A2 in excel I would type "=sum(A1,B1)"

in vba I want to do the same, BUT I don't know the actual cell address and I want the ability to copy to other cells using relative addressing.
So far I am able to code:
variable_x="=" & Cells(ProductLineRow(1), r1USD).Address & "+" & Cells(ProductLineRow(1), r2USD).Address

(The range(cells) just hasn't been working for me. Not sure why.)

So my equation is great, except the result is not relative. It gives me back =$A$1+$B$1 in my cell. I want to see =A1+B1.
I tried doing .address.indirect but vba doesn't like that either. Not sure of correct syntax.

Any thoughts how to do this?

Thanks.

Hi Guys,

I want to extract the class C address from an IP address via a formula in Excel. For example the IP address 10.10.10.10 should return a class C address 10.10.10.0 and the IP address 10.171.55.253 should return a class C address 10.171.55.0. Is this possible ?

Floep

Hi,

The following formula:

{=IF(LEN($C$10)=0,"",IF(ROWS(A$2:A3)

Hello,

I am creating a system where I need Google Docs version of Excel to find the maximum based on two criteria, tell me the address of the cell the formula found this number and then convert to the column header. Ideally, I'd be able to see that a student scored a 100 on learning goal on a quiz and then have the formula automatically report out what this student got the 100 on based on the column header and conversely if a student got a 0 on a learning goal, I'd be able to report out what was the minimum value of the student score. This way, I could generate a mail merge saying:

"John needs to work on ADDING FRACTIONS (minimum value), but is doing well on SUBTRACTING DECIMALS (maximum value)."

Right now, this is what I have:

=ARRAYFORMULA(INDEX(W11:GD11,MAX(IF($W$6:$GD$6&$W$7:$GD$7=P$8&P$9,$W11:$GD11,""))))

This is because student grades are in columns W(ROW):GD(ROW) and the subject is in row 6, test name in row 7. I type in the subject and test I want in P8 and P9. Based on this formula, I want the spreadsheet to find the maximum value within W11:GD11 based on the criteria found in P8 and P9 and then tell me the address of where this cell is. Finally, based on the address, I'd want the cell to report back the criteria heading in W7:GD7 (for example: ADDING FRACTIONS and SUBTRACTING DECIMALS).

Any help is appreciated!

Hi,

I have some formula which works at the moment:

=AVERAGE(OFFSET(D1680,-10,0,5,1))

I want to replace the D1680 with the formula:

ADDRESS(MATCH(M1692,A:A,0),4,4)

Both pieces for formula work on their own but when I try to put them together I get an unspecified error:

=AVERAGE(OFFSET(ADDRESS(MATCH(M1692,A:A,0),4,4),-10,0,5,1))


Does anyone have any ideas how I can overcome this problem?

Many thanks!

Phil

Hi,

My Name is Kashif,

I am using Address function within countif function but it doesn't work

=COUNTIF(CONCATENATE(ADDRESS(2,1,1),":",ADDRESS(10,1,4)),ADDRESS(2,1,1))

Please help it's very urgent.

Thanks,
Kashif.

I've used the following formula to get the column location of a given piece of data (in cell B1) in another sheet.

=ADDRESS(35,MATCH(B1,'Sheet2'!A4:AE4,0),4)

So L35 is displayed in the cell (lets say it's cell B3)

Now I want to use L35 in the sum function, and I'm stuck.

I need the result of =SUM(Sheet2!B35:L35), but I need to pull the L35 parameter from cell B3 where it is displayed, or using the formula above. The sum will always start with B35, but where the sum function ends is dependent on the Address formula above.

Can anyone help me. I feel like this should be easy, but I can't get it to work.

Hi, i am having trouble with the ADDRESS function i am trying to link cells from from sheet 1 to sheet 3 which it is doing but in sheet 3 there are exclamation marks, dollar signs & cell & column references which i cant seem to get rid of.
Can anybody give me any advice on this or recoommend a different function to use to avoid this

Many Thanks

Chris

Is there any way to get use the address returned by the address function? I want to use the cell referenced by the ADDRESS function as my starting point in an OFFSET function.