Senior Citizen needs help with complex formula

[Derro]

Hi My problem is a bit complex and I have an old brain so I will try and
make it as clear as I can. I have set up a series of spreadsheets and
reports to
disseminate sales information which is down loaded from a cash register on a
daily basis. The business is a community shop where citizens come together to
sell home made products with a percentage of the profits going to the local
hospital. For those that know cash registers I have made each contributor a
department with the sales of there products, through linked PLU's going to
the department carrying there name. So at the end of the day those
departments which have sales in them are exported to an Excel worksheet from
the cash register with there name going in column "A" and the amount going in
column "B". If a department has no sales for the day no data is downloaded.
The next days data is again downloaded into the same cells as the day before,
overriding the previous days data.
My problem is that on each day the data (Cash amount only this time) for
that day needs to be Hyperlinked to my main spreadsheet which is in a
separate workbook and to go into the appropriate cell by cross reverence with
the days date in column "A" and the department names which are aligned in Row
"1". (Column "A" has a row for every working day of the year and there are 54
Departments in row "1").
I would expect the formula would go something like "IF" there is an amount
beside a department name in the first workbook that amount would be
hyperlinked to the corresponding cell in the second worksheet. I hope you can
understand what I need and I thank you for any advise you can offer.

 

[Amar]

Derro,

I can share a concept of the formula I have in mind:

Try the if function to check the date, then try the vlookp function to match
the departments.

= if(date on sheet2 matches with date on sheet 1, vlookup(sheet 1
department, sheet 2 department + amount range, 2, false),0).

However at the end of this exercise, you will need to copy the data in sheet
2 for that day and paste as values, or else it will be deleted the next day.

 

[Pete_UK]

You could do this using a SUMPRODUCT function in the main sheet, but
you would have to fix the values for each day's records before they
were over-written the next day. Give some examples of how your data is
laid out, the columns that are used, the sheet names and the file
names etc and then it will be possible to give you a sample formula.

Hope this helps.

Pete

 

[Derro]

Thankyou Amar it will take me a couple of days to figure it all out and try
getting it to work. I also am busy at work so only get some time at night to
do this. And as I'm down the bottom of the earth in OZ our day is your night.
I am going to try posting part of what I need to Pete so you may get a better
idea of what I am doing. Thanks again

 

[Derro]

Hi Pete Thankyou for replying. This is the second go at replying. The first
one got lost when I posted. I will try again to give you an example of what I
am trying to do. Thanks for the help as I am way out of my depth. My daughter
is living with a bloke from the UK Nice bloke. Still says it is to hot here
in Australia.
If you can sugest a better way please do
Geoff

First Workbooks (Data Downloaded from Cash Register)
1/9/08 2/9/08
Column A Column B Column A Column B
Department Name Amount Department Name Amount
Mary Allitt $68.50 Mary Allitt $98.50
Betty Anderson $46.90 Bronwyn Bathgat $56.90
Di Bayne $201.80 Di Bayn $26.90
Janette Borella $23.00 Janette Borella $67.00

Second Workbook Spreadsheet (Auto to from Workbooks Above)
Col A Col B Col C Col D Col E
Col F Col G
Row 1 Dept Mary Betty Bronwyn Di
Janette
Row 2 Name Allit Anderson Bathgate Bayne
Borella
Row 3 Date
Row 4 1/9/08 $68.50 $46.90 No Sales $201.80
$23.00
Row 5 2/9/08 $98.50 No Sale $56.90 $26.90
$67.00 Row 6 3/9/08 ect ect
ect ect ect
Row 7 4/9/08 " " "
" "