I have an excel program that does various calculations running in a macro.
As per the attached there is a data sheet (Sheet 1 on the left) and a calculations sheet (Sheet 2 on the right)
The example I've shown is a very simplified version of what I need to accomplish.
The formula is Cell B4, Sheet 2 is:
=INDEX(Sheet1!$B4:$G4,,RIGHT(B$1,FIND("_",B$1)-1))
Right now the cell range in the calculation is hard coded in the macro, but what I'm looking for is an Input Box (or something similar)
where my end users can enter a cell range from Sheet 1, which would then be inserted into the formula.
So in the example, users would enter:
Sheet1!$B4:$G5
in the Input Box.
I need to have this capability as the number of columns and rows in the data on Sheet 1 varies constantly.
I've been trying for days to get something to work but no success.
Anything to point me in the right direction would be most appreciated.
Thank you.
As per the attached there is a data sheet (Sheet 1 on the left) and a calculations sheet (Sheet 2 on the right)
The example I've shown is a very simplified version of what I need to accomplish.
The formula is Cell B4, Sheet 2 is:
=INDEX(Sheet1!$B4:$G4,,RIGHT(B$1,FIND("_",B$1)-1))
Right now the cell range in the calculation is hard coded in the macro, but what I'm looking for is an Input Box (or something similar)
where my end users can enter a cell range from Sheet 1, which would then be inserted into the formula.
So in the example, users would enter:
Sheet1!$B4:$G5
in the Input Box.
I need to have this capability as the number of columns and rows in the data on Sheet 1 varies constantly.
I've been trying for days to get something to work but no success.
Anything to point me in the right direction would be most appreciated.
Thank you.