cell reference from value in another cell

[stef315]

I apologize if this has been answered. I tried using the Search here and on Google but may be using the wrong keywords.

I have two workbooks: one comes from someone and is updated all the time, the other is mine to gather the information out of the first that is relevant to me. I have decided that I need to either have an input row to tell my sheet which rows are mine; or, I need to look for my name in the first column and pull those rows.

I started using the input row format and here is the scenario:

--Outside sheet is in workbook named "MY" and sheet is named "MAIN".
--My workbook is named "MINE" and sheet is named "MINE".
--My name (NAME) appears periodically in column A on MAIN to indicate that row is my information.
--There are period rows in MAIN that do not have a value in column A. These rows contain dates.
--In MINE I want to pull the date then the value in that cell into two columns (DATE, JOB).

So the way I started was to add two rows to the top of MINE for input. My idea was in one of these rows I input which rows contain the dates. In the second I input which rows contain my information.

For example, the input rows in MINE are Row 1, Columns B contains the row numbers in MAIN that have the dates; Row 2, Columns B contains the row numbers in MAIN that have my Jobs. Let says the values are B1=4, C1=25, D1=46 and B2=9, C2=30, D2=51.

Now on MINE in cell A4 I want to pull the value in cell B4 from MAIN. The "4" though needs to come from cell B1 on MINE because the row positions in MAIN can change periodically. I want to use that reference because then in MINE!B5 I want the value in MAIN!C4; so the "4" is consistent for that group of 5 days. I'll then do the same with the Jobs associated with my name.

I'm not sure if this input row is best or checking for a string value is better. My problem came about because I wanted to do something like MINE!B5 = MAIN!C(MINE!B1) and I don't know how to put that so Excel likes it.

After I do this I am hoping to create a macro that will create an Outlook appointment for each of these jobs on those dates. I've got the basics of that macro down to create an appointment for the first job but will probably need additional help on that. I'll start a new thread for that if that's best though.

Thanks in advance and I hope I was clear enough.

 

[GlennUK]

If both workbooks will be open at the same time, then have a play around with the INDIRECT function, with which you can generate a reference to a cell indirectly.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[yogia]

Hi Stef315:

If I understand you correctly, then how about filtering the data of interest (your name in first column in the related sheet of the outside workbook) and then copying and pasting those rows at the appropriate location in the related sheet of your workbook. And yes this can be automated to be done through a macro.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[stef315]

I don't think either of those solutions work. I looked at Indirect and it just gives you the end result value. I need to use a cell just to determine the row number of a formula; the column will always remain the same for each section. The second would work in a round about way but the filters would have to be added each time and the macro could not exist in MAIN. Then the data would have to be copied using "visable cells only" and transposed pasted or something.

The sheet has the first column (A) having either a blank space or names. The names will repeat every 20 or so rows. The row that has a blank in column A is either a totally blank row or a row of dates (5 dates for 5 week days). The other rows that have names in column A will have a different job under each of the 5 date columns.

It's sort of a bad scheduling tool but something I have to live with. I'm trying to make it easier for myself and my coworkers to update our Outlook calendars.

 

[SkipVought]

Hi,

"I need to use a cell just to determine the row number of a formula; the column will always remain the same for each section."

Not knowing WHAT you need the row number for, I can't give specific advise. The OFFSET or INDEX function may work, but need more specific info.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[yogia]

Hi stef315:

Since you are intimately involved with this project, you are perhaps quite clear about what you are working with and what you are trying to accomplish. Now to enable others to have the same clear view, I suggest you post a few rows of your data, along with your expected result(s) without worrying about what technique needs to be used. You see locating the row number of a specific entry may or may not be the key to the solution you are seeking ... so let us have a look at your data and your expected resul(s) and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[ck1999]

Can you write a macro in "mine" workbook to loop from a1 until end of data. then do something like
Counter will be the row

dim info(100,6)
counter2 = 0
for counter = 1 to 1000 (or use a variable for last used row in workbook)

activeworkbook("main").activate
sheets("my").activate
if range("A" & counter).value = "" then
if range("B" & counter).value = "" then next counter
info(counter2,1) = range("B" & counter).value
info(counter2,3) = range("c" & counter).value
info(counter2,5) = range("d" & counter).value
counter2=counter2+1
end if
if range("A" & counter).value = "John Doe" then
' if just want row number make info(counter2,2)=counter
' to copy data to you other sheet use
info(counter2,2)= range("B" & counter).value
info(counter2,4)= range("c" & counter).value
info(counter2,6)= range("d" & counter).value
end if
next counter

' Loop to add info to "Mine" sheet
activeworkbook("mine").activate
sheets("mine").activate
range("a1").value = "Date"
range("b1").value = "Job"
range("c1").value = "Date"
range("d1").value = "Job"
range("e1").value = "Date"
range("f1").value = "Job"

for counter = 1 to counter2
range("A" & counter +1).value = info(counter,1)
range("b" & counter +1).value = info(counter,2)
range("c" & counter +1).value = info(counter,3)
range("d" & counter +1).value = info(counter,4)
range("e" & counter +1).value = info(counter,5)
range("f" & counter +1).value = info(counter,6)
next counter

This will move all the data into the mine sheet

may not be the most efficient coding
but from what I understand will do what you want.

ck1999