cell references

  1. R

    cell references in body of email macro

    Hi, i'm pretty new to coding, so would appreciate any help - i've included the code for an email macro i've kinda put together. Would appreciate any suggestions on how to make this more efficient or better code. My issue I want to add a line in the body of this email. This line would be...
  2. J

    Can anyone tell me why this formula will not update automatically?

    =IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B132:K132"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4)),IF(D4=1,SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"...
  3. C

    Creating cell references between multiple worksheets

    How do you create worksheets that have cell references that reference each other and keep the same format and number scheme throughout all the sheets?
  4. I

    Extract cell references from a formula using VBA?

    First question, and I apologize if it has been asked before (I have searched the forums but did not find any posts that I recognized as a possible solution). If you're aware of another post that address this issue, please share the link! Context: I teach a college course, and my exams are...
  5. T

    Convert cell references to numbers within formula?

    Hi all Apologies if this has already been asked / answered but I could not find it anywhere. I want to convert cell references within formulae to actual figures e.g. using the table below: =SUM(B2:B8) arrives at 271.60 but I want to make the formula say...
  6. A

    VBA Change "Cell Reference In Formulas" To "Range Names In Excel" for a whole workbook

    Excel friends, Let's say wherever this vlookup(?,$B5:$C25,2,0) shows up in a Workbook. If I have defined a name for all ranges, and $B5:$C25 is Prices, then the VBA code should replace the vlookup to show: vlookup(?,Prices,2,0). I would like to find a VBA code to perform this kind of...
  7. J

    Refer to cell on different sheet plus 2 rows

    Hello I have this formula pointing to a cell on a different sheet; A1 ='Sheet2'!A1 In A2 I want the formula to point to 'Sheet2'!A2 so basically 'Sheet2'!A1+1 row. I thought I could use OFFSET with INDIRECT but was unable to make it work.
  8. D

    cell reference from a cell reference

    I can't figure out how to ask this question so google will give me an answer. I've often wanted to do this. It doesn't matter why. Please don't be a troll and tell me why I'm wrong to want to do this. Here's the formula I entered in A1: =if($B1>1,"yep","no") And then I pasted it into thirty...
  9. T

    Dynamic Index formula using cell references instead of cell numbers.

    Hey guys, Got a real brain teaser for the hardcore excel users. Recently I have been using an INDIRECT function: INDIRECT("'"&("WO"&$E$1)&"'!C8") As you can see I have already started to make my worksheet functions more dynamic by using cell references. In the above example rather than...
  10. M

    Reference specific cell from every spreadsheet on the workbook (except Spreadsheet X,Y and Z) and list results

    Hi all! I'm looking for a way to pull data from every spreadsheet in my workbook, except for spreadhsheet X, Y and Z and list the results on cell C11 in spreadsheet X. I'm sure this is very straightforward but I'm quite new to VBA and am picking as I go, learning from each iteration. Can...
  11. E

    Using Wildcard in Cell Reference

    <tbody> I'm building a worksheet that will concatenate cells based off of criteria in another column on the same row. After much searching I found Harlan Groves aconcat UDF and this is the formula I've got so far: =SUBSTITUTE(aconcat(IF(Labor!$A$8:$A$100=A2," "&Labor!$M$8:$M$100," ")),"...
  12. J

    Sorting a table used as a reference in another table

    I have two tables. The first table contains data which is referred to by a second table. I would like to sort the first table; however, when I do the references do not "follow" the data in the second table. Is there a way to do this, so that when I sort the first table the references in the...
  13. D

    VB Button required to "Save as" with multiple cell references as name, current date/time

    Morning Excel and VB wizards, I need some help, more like spoon feeding in fact. I am trying to create a workbook that will be used to record and store appraisals on a network drive and automatically update some cells in another tracker workbook. But before I get ahead of myself I’d like to...
  14. T

    Update formulas on multiple worksheets feeding from another

    I have a file that lists the months in columns and several vendors as rows. Each month I put in their sales figures, which then is used to calculate fees we charge them in a couple rows below. For each vendor, I have a separate worksheet that has an invoice template that pulls the fee over...
  15. H

    Dynamic Range - Cell references changing in OFFSET function

    Hi, I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from...
  16. I

    Can't get cell value to be sheet name for array value in HLOOKUP

    I have: =HLOOKUP(Table4[[#This Row],[parent_product_line]],UK_GBP!$B$11:$DK$65,4,FALSE) What I want to do is have "UK_GBP!" be a valye which comes from another cell so I tried: BE18 = UK_GBP =HLOOKUP(Table4[[#This Row],[parent_product_line]],CONCATENATE(BE18,"!")$B$11:$DK$65,4,FALSE)...
  17. N

    reference entire worksheet for pivot table

    Hi, I'm trying to work on a few pivot tables where the data is continually refreshed when it is pulled in from our database. Each pivot table uses a different worksheet- one of which is titled "DF_Matching" I want the pivot table to use the entire database, but because the number of records...
  18. A

    Playing with references

    Hi There, I've got a question on referencing formulas using the dollar sign. I have a set of data, for example: 1 2 3 4 5 6 7 8 9 I have to average 4 numbers in one cell. and in the next cell, the next four. i.e. one cell, =avg(1:4) then in the next cell, =avg(5:8). I have millions of...
  19. D

    Cell Reference changes

    I have a worksheet (Sheet1) with data in the first column....sixth column. In sheet2, each cell of the first column...sixth column points to the corresponding data in Sheet1. The formula in sheet2 looks like this: =IF(sheet1!A1="","",sheet1A1) Here's the problem: If I delete...
  20. B

    How to find the reference of a value in a table, when i know it is present in the table?

    Hi, I was trying to find the cell reference of a particular value, if found in a table. For eg. If i have a table having prices for difference currencies and for different quantities. Assuming the currencies name in Cells: D2:D20 and Quantities in Cells E1:M1. Corresponding data is in table...

Some videos you may like

This Week's Hot Topics

Top