All Questions
37
questions
1
vote
1
answer
127
views
Automatically change cell reference in formulas, based off of cell updating
I have a couple of formulas that are currently being manually updated every time someone wants to change the row that the formula is referencing. The current formula is:
="Call raised for a "...
- 13
0
votes
2
answers
37
views
Linking data in two Excel tables where row number does not align
Table1 data starts at row 11 onwards and Table1 is called eSpace
Table2 data starts at row 18 onwards and column B heading is called Level
Entering =eSpace[Level] in Table2 row 18 retrieves the data ...
0
votes
0
answers
40
views
Indirect function usage
I am trying to use the indirect function to refer to a cell in another workbook:
A1 = 'C:\Users\thekv\OneDrive\Documents\accessmydocs\[KVR 4.0.xlsm]SheetOne'!$e$32
B1 = indirect(a1)
I get #REF!
Is ...
-1
votes
1
answer
22
views
Prevent excel updating cell referene with new location
this should be super basic but I can't figure it out.
If I have a series of numbers in A1:A10. And in B1 i have [=A1], B2 [=A2] etc. Simple cell reference.
My problem is that when I select A1:A10 and ...
- 4,170
-1
votes
1
answer
69
views
Excel column references (instead of value)
I am searching for a way to obtain column-references in Excel.
I would like to search through a row (B12:G12) and look for the highest number. That could be done with the MAX-formula. But instead of ...
- 27
0
votes
1
answer
50
views
Formula reference holds for n cells in a row, then switches to new reference
I wonder if there's a quick solution to the following:
My goal is to divide a cell value by three, using the same value for three cells in a row, then switch to the next value in the series for ...
- 13
0
votes
2
answers
75
views
How to copy Excel formula across row so cell reference row increments and cell reference column stays fixed
When I copy this formula across a row of cells, cell reference row stays fixed while cell reference column increments. How do I do the inverse (column stays fixed while row increments)?
=HYPERLINK("#...
- 1,304
0
votes
1
answer
58
views
How to insert a formula inside an array?
I found this amazing formula:
=SUM(INDEX(data,N(IF(1,{1,3,5})))) by Jeff Weir.
But I need to reference the numbers inside an array with some formula. Here is an example:
=SUM(INDEX(data,N(IF(1,{...
- 13
0
votes
2
answers
193
views
Find the sum of a cell from difference worksheets without referencing sheet names
I have a workbook with multiple sheets. All sheet names follow this scheme "Sensor Status", "Sensor Status(1)" and so on. Is there a way to get the SUM of I4 from all these sheets without having to ...
0
votes
1
answer
246
views
Change cell reference when adding a new row in base sheet
I have 2 excel files: base sheet in the 1.xlsx with raw data and formula sheet for formulas in 2.xlsx
base file 1.xlsx:
1 - 1
2 - 2
3 - 3
4 - 4
5 - 5
formula sheet in 2.xlsx
1 - ='...
- 23
1
vote
1
answer
558
views
Excel Formula to find Next Sheet in workbook
I have a huge file with many sheets and in each sheet I need the same formula that references the next sheet in sequence, but only the sheet that follows it.
For example:
Let's say I have sheets ...
- 150
0
votes
1
answer
303
views
Finding a reference to a cell in an excel formula
Is there a formula or a macro that can find a reference to a cell in another cell?
For example:
A | B | C
------------------
1| 1 | 2 | =A1+B1
is there a formula that takes a C1 as input ...
- 27
0
votes
1
answer
5k
views
referencing a sheet which has a space in its name
I have a following formula which is refencing Sheet2:
((COUNTIF((INDIRECT("Sheet2!"&ADDRESS(ROW(Sheet2!J4);COLUMN(Sheet2!J4))&":J"&
(MIN(IF(Sheet2!A4:A107="";ROW(Sheet2!A4:A107))))));"&...
- 97
0
votes
1
answer
38
views
referencing a sheet which has a formula inside
I have a formula in Sheet2 which is following:
=((COUNTIF((INDIRECT(ADDRESS(ROW(J4);COLUMN(J4))&":J"&
(MIN(IF(A4:A107="";ROW(A4:A107))))));"
<>"&""))-1)/((COUNTIF((INDIRECT(...
- 97