How to use a function or variable when defining a separate cell
I am trying to create a dynamic pie chart with the following definition:
SERIES(,'2023'!$B$39:$B$46,'2023'!$AA$39:$AA$46,1)
Where $B$39:$B$46 is the legend range and $AA$39:$AA$46 is the data range.
My question is how to define $AA$39:$AA$46 using a function or value from another cell to make it dynamic depending on other inputs.
I have a function in cell A1, which converts the value in cell E33 to "AA." The function is:
=SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "")
I want to use the output from this function in the range used for the pie chart data. Rather than $AA$39:$AA$46, I would like to use either cell A1 or the actual equation =SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "") combined with the row range 39:46.
So, my question is whether it's possible to define a range as:
$A1$39:$A1$46, orSUBSTITUTE(ADDRESS(1, (E33), 4), "1", "")$39:SUBSTITUTE(ADDRESS(1, (E33), 4), "1", "")$46
I may not have explained my situation well, but I would appreciate any help.
You won't be able to put this formula directly into the source definition for the chart. You can put this formula (or any other formula that delivers the desired range) into a range name (or named formula), using the Formulas Ribbon > Name Manager > New. Specify a name and enter the formula. Make sure to use absolute referencing with $ signs if you always want to use the value in cell E33.
Given that your result refers to column AA, I assume the value in cell E33 is 27. Instead of the complicated substitute formula, you can use Index() like this:
=INDEX(Sheet2!$39:$39,Sheet2!$E$33):INDEX(Sheet2!$45:$45,Sheet2!$E$33)
Put that formula into a range name and plug the range name into the chart data source as shown in the screenshot.
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port