Convert cell addresses in JSON format
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Convert cell addresses in JSON format
Hello Experts,
Please see the attachment for reference. Sheet 2: In Col C, I have names for "parameter",
in Range D2:P2, I have names of "files" and in Range D3:P40 I have cell addresses.
What I would like to achieve is in sheet 1 as an example.
Please help
Best regards
Vilas Desai
Please see the attachment for reference. Sheet 2: In Col C, I have names for "parameter",
in Range D2:P2, I have names of "files" and in Range D3:P40 I have cell addresses.
What I would like to achieve is in sheet 1 as an example.
Please help
Best regards
Vilas Desai
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert cell addresses in JSON format
I'm afraid I don't even begin to understand what you want.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
Ok, sorry about that, Hans
Let me try again please.
The important objective besides other, is to convert cell addresses in the "M14" format to "row":14,"column":13 format
There are 40 parameters arranged in col C3:C40 (Fluid Name, Fluid Phase,.......Pressure Loss @ Meter Min Flow)
There are workbook names in range D2:P2 (CVBLV, CVBFY,........Transmitters)
So this is like a 2D table. Parameter Vs File names.
in cell D2 of the worksheet 2, we have address P13 that corresponds to Parameter=Fluid Name in the file CVBLV.
Below is how I want to describe this situation.
in worksheet 1,
cell A2 = file name, in this case, it is CVBLV
Cell B2 = [{"parameter":"Fluid Name","row":13,"column":16}] meaning "parameter" by name "Fluid Name" has a value in cell P13 of workbook CVBLV
If I could explain this successfully then I will continue with the rest, which is o same lines.
Best regards
Vilas Desai
Let me try again please.
The important objective besides other, is to convert cell addresses in the "M14" format to "row":14,"column":13 format
There are 40 parameters arranged in col C3:C40 (Fluid Name, Fluid Phase,.......Pressure Loss @ Meter Min Flow)
There are workbook names in range D2:P2 (CVBLV, CVBFY,........Transmitters)
So this is like a 2D table. Parameter Vs File names.
in cell D2 of the worksheet 2, we have address P13 that corresponds to Parameter=Fluid Name in the file CVBLV.
Below is how I want to describe this situation.
in worksheet 1,
cell A2 = file name, in this case, it is CVBLV
Cell B2 = [{"parameter":"Fluid Name","row":13,"column":16}] meaning "parameter" by name "Fluid Name" has a value in cell P13 of workbook CVBLV
If I could explain this successfully then I will continue with the rest, which is o same lines.
Best regards
Vilas Desai
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert cell addresses in JSON format
One of the reasons I didn't understand is that Sheet2!C4 contains "Fluid Name" but on Sheet1, you use "Fluid Process". Etc.
I now assume that was misleading.
See Sheet3 in the attached version.
I now assume that was misleading.
See Sheet3 in the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
My Bad. Apologies.
FYI, when I do "Enable Editing" i get an error #Name? So the formula is getting corrupted. Anything wrong I am doing Hans. Thanks
FYI, when I do "Enable Editing" i get an error #Name? So the formula is getting corrupted. Anything wrong I am doing Hans. Thanks
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
Excel 2016.
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
Yes no Problem
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert cell addresses in JSON format
See the attached version. It is now a macro-enabled workbook (.xlsm)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
Thanks a lot, Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
Hello Hans,
I was trying to use the above file and stuck at two places.
1. n sheet 1, Cell B2, I dont see the end brackets ... Flower / Square both.
2. How do I run the macro after making changes in sheet 2?
Please advise'
Best regards
Vilas Desai
I was trying to use the above file and stuck at two places.
1. n sheet 1, Cell B2, I dont see the end brackets ... Flower / Square both.
2. How do I run the macro after making changes in sheet 2?
Please advise'
Best regards
Vilas Desai
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Convert cell addresses in JSON format
Please ignore point 1
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert cell addresses in JSON format
1. The end brackets should be present, but Excel may not display all characters unless you turn on Wrap Text.
2. Insert the following line below Function GetParams(Param As String) As String:
The results in column B on Sheet1 should now be updated automatically if you change something on Sheet2.
2. Insert the following line below Function GetParams(Param As String) As String:
Code: Select all
Application.Volatile
Best wishes,
Hans
Hans