Comments on: Excel cell reference: how to make and use

As simple as it seems, Excel cell reference confuses many users. How is a cell address defined in Excel? What is an absolute and relative reference and when each should be used? In this tutorial, you will find answers to these and many more questions. Continue reading →

Comments page 2. Total comments: 88

  1. Hey !
    good afternoon.
    I have such a problem
    I have several google forms linked to Excel
    I have a formula that has a total score across several forms. The main problem: when someone fills out these forms, my formula slides down 1 line. For example, if one person answered the form, his answer seems like inserted above, and the cell of the formula that was = B2 becomes C2. I've also tried writing a formula like = $B$2 - but it doesn't help me

  2. Hello i have to increase cell reference by 200, so when you drag down a cell with a reference it will be increased by one (so A1--drag-> A2-->A3..) i have to increase 200( A1-->A201-->A401-->A601-...)

    Sorry the bad english

      1. Very thanks

  3. Hello I need to re-organie some boxes that have different vials. I need to put the coordantes of every cell so I know how it has been re arrange. Meaning that my cell A2(vial1) needs to go to B3(vial 1) with its coordanate and the text.
    Is there any way to put a sub index with the coordante of every cell?

    Thanks

  4. Hi,

    Can I combine two cells to reference a third cell.

    for example - Cell A1 have a C in it cell A2 has a 2 in it.
    Can I combine those two cells to produce an =C2 formula?

  5. Hello,

    Is there a way to use mixed cell references in excel when referring to another sheet?

    For example:

    I have a table I am filling in Sheet 2, I am using a formula that uses the reference 'Sheet1'!B13:B165.
    When I go to flash fill the data in my table in Sheet 2, it changes to 'Sheet1'!B14:B166.
    However, I need it to change just the columns it references in Sheet 1, not the rows.
    I.E. I need to go from saying 'Sheet1'!B13:B165 in the 1st cell of the table to 'Sheet1'!C13:C165 in the 2nd cell in the table, then to 'Sheet1'!D13:D165 in the 3rd cell, etc.

    Thus, is there a way to use a mixed cell reference when it's referring to cells in a different sheet?

    Thank you!

  6. Hi,
    I have to add different cells of sheet 1 to sheet 2 like C1+C3+C5
    For example:
    Row A Row B Row C
    Sl. No. Name pts
    1 A 3
    2 B 0
    3 C 1
    4 D 0
    5 E 3
    So next day when i filter the row c column with largest to smallest, the values should not change.
    How to put the formula for this.

    1. Hello!
      I recommend replacing all formulas with values before sorting. Or make a copy of the table and sort it. Then your calculations will not change.

  7. How do you pick a cell value that is the cross reference of a row and a column dependent on the text within the cell. For example if column A2 down had a list of names (i.e sally, harry, sue,tom etc) and row 1 had a list of fruit picked and the cells that align had the numbers of fruit picked against the name how do you select according to name and fruit? did that make sense?

  8. the result of my funky formula being... =SHEET1!(B17)

  9. I've got 778 work sheets, is there a way of getting the same cell of data from each work sheet in a list on a summary page without clicking through each work book.

    For instance I want B17 on each work sheet in summary I want to columns headed Sheet and Product of B17 on each of the 778 sheets.

    in Sheet 1 I want =B17
    in Sheet 2 I want =B17

    I've tried building formulas such as...

    ="="&"SHEET"&"CELLS WITH 1 to 778"&"!"&"B17"

  10. How do I write this formula in one sentence, "IF D4 is >=40 and F4 is >=40 and H4 is >=40 and J4 is >=40" so that, if the condition it true, the output will be "Pass" using IF(logic_test, [value_if_true] [value_if_false]) or any condition that will satisfy my request.
    Thanks

    1. =if(d4>=40,enter you want or if text use this "enter inside text ",if(f4>=40,enter you want or if text use this "enter inside text ",if(h4>=40,enter you want or if text use this "enter inside text ",if(j4>=40,enter you want or if text use this "enter inside text "))))

  11. Hi, I'm not sure I know all the proper excel lingo, but here goes:
    Can I convert a cell reference (only) within a formula to a value and keep the rest of the formula as is?
    For example the value in A1 is 10, Column B formula is currently: +A1*2.9. I want to know if I can easily convert the formula in column B to: +10*2.9 - I need to keep the formula in column B, otherwise I would convert the formula to a value and be done with it! I have thousands of rows to convert and would rather not do it one by one. Thanks so much.

  12. I want to reference another sheet (sheet 1) based on a number placed in a cell to represent the row on 'sheet 2 and call information based on the row given.
    For example I place 11 in cell on sheet 2 then references various columns in that row.
    11 must be constant for the row but the column data selected can change based on which column the required data is under.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =INDIRECT(ADDRESS(Sheet2!$A$1,COLUMN(),2,1))

      or

      =INDIRECT("Sheet1!"&ADDRESS(Sheet2!$A$1,COLUMN(),2,1))

      Cell Sheet2!$A$ contains 11.
      Copy this formula to cells row by row.

      I hope it’ll be helpful.

  13. can u give me insights on these questions?
    1. Why is it important to use cell reference or range of cells in creating a formula in text/sting functions?
    2. Sate 2 common errors in inputting text function in Excel and their solution? give the error and present the solution step by step.

    Thank you and hoping for a positive response:)

  14. Useful info here.
    My problem is if Sheet1 has no value inputted in (ie) Cell A1 then the value in Sheet2 A1 is a 0. What do I use to make the formula in A1 of sheet2 a blank instead of a zero? My formula is ='Region'!A1
    The entire 2nd sheet has references to the first sheet and they are working perfect except the blank value or no value on sheet1 is becoming zeros on sheet2.
    So with columns A through BK and rows 1 through 110 I have an overload of zeros. Way too busy to focus on the numbers and words needed on the 9 tabs that reference sheet1 or tab1.
    Any help is greatly appreciate.
    Thank you

    1. Hello!
      I believe the following formula will help you solve your task:

      =IF(A1<>"",A1,"")

      Or use custom number format:

      #,##0;-#,##0;

      I hope this will help

  15. I have a large sheet with at least 100 cell Names (that somehow got converted to A1 references). The method you show here is excellent but there doesn't seem to be a way of selecting ALL the names so they can replace ALL occurrences of their A1 references with their respective Names in the selected ramge.

  16. hi, i got 1 master folder(A) contain 2 workbook(1 & 2).workbook 2,worksheet name 'sub' have link cell to workbook 1 worksheet name 'main'.the problem is when i copy this 2 workbook n paste it to new folder(sub 1 folder) the reference cell in workbook 2 stil refer to old reference location(master folder A).how can i make this copy workbook 1 & 2 follow new location (sub 1 folder) automatically after copy n paste?

    1. Hello!
      Excel does not automatically change links to external files. You need to keep track of this yourself.

  17. Oh my! My original message was formatted so things made more since, but all the spaces were taken out and now it looks... confusing? I'll redo the part that is worst...

    formula in A1 =(A107-A100)/A100
    formula in A2 =(B107-B100)/B100
    formula in A3 =(C107-C100)/C100 etc etc

  18. Any way of having a variable in a cell reference? My setup is this... I have columns of numbers, 1 entry each day on each column, and display a 7-day average along the top row. But every day I have to change the formula for the 7-day avg of each column. Can I reference a cell location where I'd put the row# that changes from day to day?

    Below, assume the last data entry is on line 107...
    formula in A1 formula in A2 formula in A3
    =(A107-A100)/A100 =(B107-B100)/B100 =(C107-C100)/C100 etc etc
    The next day I have to change all the formulas, but only the row#'s change (incr by 1).
    So, can the cell reference within the formula be written where the row# points to a cell location, which would contain the actual row# (107 & 100 in this case).
    I tried many things, but they all result in error (assume reference cell is Z1 & Z2)...
    =(A'Z1'-A'Z2')/A'Z2'
    =(A(Z1)-A(Z2))/A(Z2)
    =(A"Z1"-A"Z2")/A"Z2"
    =(A[Z1]-A[Z2])/A[Z2] etc etc
    I've run through all my guesses, and cannot find anything online addressing this. Maybe this can't be done, so I'll just have to add some more columns to my spreadsheet (already up to column DX, so this thing's unwieldy as it is :)
    Thank you very much for taking the time to read this. If you somehow manage to understand what I'm asking, and have an answer, thank you even more!

    1. Hello Pete!
      You can use the INDIRECT function to specify the cell address. Your formula

      =(A107-A100) / A100

      can be written as

      =(INDIRECT ("A" & D10) -INDIRECT ("A" & D11)) / INDIRECT ("A" & D11)

      where cell D10 contains 107, cell D11 contains 100.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  19. Hi, I want to refer multiple cells in one cell how do I do it? Please reply

  20. I have 100 families with 1 to 7 family members and total nos. of families =100 and total members=450. I assigned sl.1 in C2 column for 1st family head(and no sl. number for other family members for the same family in C-column, so, C3&C4=blank if there are 3 family members). Again Sl.2 in C5 for 2nd family and so on. Now, in S-column I wrote ages of each members besides the name of respective members and in T-column, w.r.t. C2 row, I find 'height age' of each family by using formula T2=MAX(S2:S4)manually and T3,T4 kept blank as C3&C4 for 1st family. Here ranges of ages of different families will differ as per family size. Similarly, for 2nd family C5=2, T5=MAX(S5:S9)[C6 to C9 and T6 to T9 =remain blanks].I used following formula and drag-down it to avoid manual selection of each family group:"=IF(AND(C2>0,SUM(C2:C8)=C2),MAX(S2:S8),IF(AND(C2>0,SUM(C2:C7)=C2),MAX(S2:S7),IF(AND(C2>0,SUM(C2:C6)=C2),MAX(S2:S6),IF(AND(C2>0,SUM(C2:C5)=C2),MAX(S2:S5),IF(AND(C2>0,SUM(C2:C4)=C2),MAX(S2:S4),IF(AND(C2>0,SUM(C2:C3)=C2),MAX(S2:S3),IF(AND(C2>0,C3>0),MAX(S2:S2),"")))))))". I think there is more appropriate formula.Please help me out.

    1. Hello Biswajit!
      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  21. Hello! Very useful information, I just cannot figure out how to "update this cell to read Support to make it consistent with the other tables" does anyone have any idea what that means? it says it with a red arrow on the top right corner of the specific cell. I'd appreciate some help!

  22. Hey I have a problem. In cell D4 I put a value "50"and D4 is formatted as "Percentage" so it's showing as "50%". Now in another cell(B4) I want D4 as reference, so I combine text and cell reference as "="Less: Depreciation @"&D4"(first double quote not in formula) and it giving the result in B4 is "Less: Depreciation @0.5" but I want it should be "Less: Depreciation @50%" and also D4 should be "50%". Any suggestion would be very appreciated. Thanks in advance.

  23. I have 3 sheets.
    In sheet 1 column A may hold a value, it can be empty too.
    In sheet 2 column A references column A of sheet 1. If column A in sheet 1 is empty, column A in sheet 2 will show no value, it will however contain the reference. So technically it is not empty, functionally it is empty.
    In sheet 3 I want to test if column A of sheet 2 is empty.

    What formula could I use best?

  24. For my bookkeeping I count the expensives in a worksheet "wk 1", "wk 2", "wk 3", "wk 4" & "wk 5A" as an example for January. Week 5A is not a full week and in February I begin with "wk 5B". After week 5A I make a summary "JAN" of all the week totals and monthly fixed amounts like subscription fees, cable TV, internet, etc.
    The week totals are always at cell E12. To get that value I have a cell with ='wk 1'!$E$12 and it works, but I want to make it easier for my wife, so she can copy the sheets at to end tab herself. Hence I have a column with "wk 1" to "wk 5A" (cells A4 to A8) she has to fill in, but I can't get the formula using these values to work for cells B4 to B8. I need for cell B4 something like ="'"&A4&"'!"$E$12 but it doesn't work.

    1. I have solved the problem after more digging. The INDIRECT formula wasn't working properly as it didn't accept the E12 cell reference. With aid of ADDRESS (row 12, column 5) it worked. So, the formulas for B4 to B8 are:
      =INDIRECT(ADDRESS(12,5,1,1,A4))
      =INDIRECT(ADDRESS(12,5,1,1,A5))
      etc.

  25. Thank you,
    I have a question I will ask it as an example:
    I have two columns A and B (as if I want to plot them in a graph). I want to extract the maximum value of the column A so I used the formula =MAX(A1:A5000) {for example}. Now I want to extract the respective value in column B and I do not know how. For example if the maximum value is located in A2000 I want in another cell to have the value of B2000.
    Thank you in advance

    1. you should use a search formula for this. such as:
      =search(C1;A:A;B:B)

      Being C1=MAX(A1:A5000)

      or

      =Vlookup(C1;A1:B5000;2)

  26. Hi Svetlana,
    Can we use Cell name in formula instead of reference, like I have a cell name "Exchange_rate" and I want to link another cell with this one and in formula bar showing name "Exchange_rate" instead of Cell reference like A1 etc.

    1. You can put the cell name into the Names box. In the above example a cell range was used QTY and later a formula =SUM(QTY)

  27. Thank you!
    A little question: (...For example, the range A1:C2 includes 9 cells from A1 through C2...). Nine cells or six?

    1. Hi Vladimir,

      Surely, 6 cells. Thank you, fixed :)

  28. thanks a lot , I learn vary important information from this web. It helped me in my work.

  29. Good

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)