Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Can I function according to Row?

0

Can I do function like

=Minus(H(row), G(row))

and the Row come become number where the function locate?

Answer
Discuss

Answers

0

Hi Thugy and welcome to the Forum.

You can use the INDIRECT and ROW functions to do that. To subtract G5 from H5 say, put this formula in any cell of row 5:

=INDIRECT("H" & ROW())-INDIRECT("G"&ROW())

Alternatively you can use relative addressing with R1C1 reference addresses (but you would need to select that in Excel settings and the +/- column  differences depend on the cell containing the formula). To do that, go menu File/Options/Formulas then click/check R1C1 reference style (under "Working with Formulas"). Then enter this in cell I5 say:

=RC[-1] - RC[-2]

which means take the cell no rows and  -1 columns to the right of I5 (i.e. 1 to the left) and subtract what's in that two to the left (those numbers in bold above). That's much shorter but the trouble is(or maybe) that R1C1 addressing will be applied to all simple formulae (like =H5-G5) and can be confusing.

You can still use that addressing while in (normal) A1 mode, by using:

=INDIRECT("RC[-1]",FALSE)-INDIRECT("RC[-2]",FALSE)

(where the FALSE argument means INDIRECT uses R1C1 addresses instead) but I think the first formula will make most sense to most users..

Hope this helps. If so, please remember to mark this Answer as Selected. 

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login