R
Ralph Howarth
Am I able to create formulas in a worksheet, and then have those formulas
read a cell on another worksheet for an offset value? In other words, I am
looking for a way to have fomulas reference a third worksheet and accept a
user chosen value to tell the formulas where the data rows start.
To visualize I will name three worhsheets:
Setup
Data
Results
In Results is a cell that specifies what row the data in Setup starts at.
I'll Name that cell "HeaderRow" since the Results worksheet also has a header
row.
If the Data worksheet has the header row in row 1 like a normal data table,
then both the Data and the Results will be starting off with data rows on row
2; but there are Data worksheets of external sources that have titles and
account information above the header row and so the header row gets pushed
down a few rows or so. So I wanted to make a Setup tab that is user friendly
where the departmental/entity who recieves the custom report data can simply
fill in the Setup worksheet what row their report data starts on as that is
one thing that is consistent- the header row is in the same place for that
end user group.
While building formulas in the Results worksheet, I am wondering if there is
a way to do cell reference math such that any given formula can be patterned:
= Data!$Col[Row+HeaderRow]
Can that be done somehow by using special quotation marks or delimiters of a
sort? I played around but did not get anything to work.
I do know that Excel is able to use Labels (upen checking the
Tools/Options/Calculation/Workbook default of Accept lables in fomulas check
box.); but that does not help me (I think) since the data rows do not have a
left most column of row names to enable me to reference an intersection. I
also see that there is an Offset function where a number can be accepted to
Offset where to reference a cell such that:
=Offset($ColRow,HeaderRow,0,1,1)
That seems to do the trick for me; but is it still possilbe to do cell
refence math withing an Reference argument?
read a cell on another worksheet for an offset value? In other words, I am
looking for a way to have fomulas reference a third worksheet and accept a
user chosen value to tell the formulas where the data rows start.
To visualize I will name three worhsheets:
Setup
Data
Results
In Results is a cell that specifies what row the data in Setup starts at.
I'll Name that cell "HeaderRow" since the Results worksheet also has a header
row.
If the Data worksheet has the header row in row 1 like a normal data table,
then both the Data and the Results will be starting off with data rows on row
2; but there are Data worksheets of external sources that have titles and
account information above the header row and so the header row gets pushed
down a few rows or so. So I wanted to make a Setup tab that is user friendly
where the departmental/entity who recieves the custom report data can simply
fill in the Setup worksheet what row their report data starts on as that is
one thing that is consistent- the header row is in the same place for that
end user group.
While building formulas in the Results worksheet, I am wondering if there is
a way to do cell reference math such that any given formula can be patterned:
= Data!$Col[Row+HeaderRow]
Can that be done somehow by using special quotation marks or delimiters of a
sort? I played around but did not get anything to work.
I do know that Excel is able to use Labels (upen checking the
Tools/Options/Calculation/Workbook default of Accept lables in fomulas check
box.); but that does not help me (I think) since the data rows do not have a
left most column of row names to enable me to reference an intersection. I
also see that there is an Offset function where a number can be accepted to
Offset where to reference a cell such that:
=Offset($ColRow,HeaderRow,0,1,1)
That seems to do the trick for me; but is it still possilbe to do cell
refence math withing an Reference argument?