cell references

Hello, I'm stumped over something I'm sure is easy. I have 2 separate workbooks, and cell A1 in workbook A currently references the value in A5 in workbook B. Sometimes rows in workbook B get added or deleted, which changes the data. For example, if "apples" are in workbook B in A5, and a row...

What would be the simpliest way to copy the following table into another sheet, but all cells are absolute references to the original. e.g. ='Custom page 1'!B2 AB1Field 1Data2Page title3Page description4Field 15Field 26Field 37Field 48Field 59Field 610Field 711Field 812Field 913Field...

Hi all, I'm a new member so forgive me if this has been answered elsewhere but I have scoured the internet in search of an answer to my problem but I am yet to come up with a solution. Basically the problem is this. I have an IF formula that starts in AH4 that runs the length of Column AH from...

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...

=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&"...

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?

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...

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...

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...

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.

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...

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...

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...

<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," ")),"...

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...

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...

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...

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...

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)...

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...